/// <summary> /// add by peter /// date 2010-11-18 /// </summary> /// <param name="v_sQueryString"></param> /// <param name="v_oSqlParams"></param> public void ExecuteQueryString(string v_sQueryString, SqlParameter[] v_oSqlParams) { SqlTransConn transConn = GetConnection(); try { using (SqlCommand command = transConn.CreateCommand()) { command.CommandText = v_sQueryString; command.CommandType = CommandType.Text; foreach (SqlParameter parm in v_oSqlParams) { command.Parameters.Add(parm); } command.ExecuteNonQuery(); command.Parameters.Clear(); } } catch (Exception ex) { throw new Exception(v_sQueryString, ex); } finally { transConn.Close(); } }
public bool IsReadOnly() { string sql = @"SELECT is_read_only FROM sys.databases WHERE name = @name"; List <SqlParameter> parameters = new List <SqlParameter>(); SqlTransConn transConn = GetConnection(_connectionName); int result = 0; try { using (SqlCommand command = transConn.CreateCommand()) { command.CommandText = sql; command.CommandType = CommandType.Text; command.Parameters.Add(new SqlParameter("@name", transConn.Conn.Database)); result = System.Convert.ToInt32(command.ExecuteScalar()); } } catch (Exception ex) { throw new Exception("Failed to execute sql:" + sql, ex); } finally { if (!transConn.IsInTransaction()) { transConn.Close(); } } return(result > 0); }
// does not work // public int Delete(Query query) // { // SqlBuilder builder = InitSqlBuilder(query); // String sql = builder.BuildDelete(); // int result; //#if DEBUG // m_log.Error(sql); //#endif // SqlTransConn transConn = GetConnection(); // try // { // using (SqlCommand command = transConn.CreateCommand()) // { // command.CommandText = sql; // command.CommandType = CommandType.Text; // List<SqlParameter> parameters = builder.GetSqlParams(); // foreach (SqlParameter parm in parameters) // command.Parameters.Add(parm); // result = command.ExecuteNonQuery(); // command.Parameters.Clear(); // } // } // catch (Exception ex) // { // throw new Exception(sql, ex); // } // finally // { // transConn.Close(); // } // return result; // } private void _updateOrAddOrDeleteDBFromBuilder(Query query) { SqlBuilder builder = InitSqlBuilder(query); String sql = builder.Build(); SqlTransConn transConn = GetConnection(); try { using (SqlCommand command = transConn.CreateCommand()) { command.CommandText = sql; command.CommandType = CommandType.Text; List <SqlParameter> parameters = builder.GetSqlParams(); foreach (SqlParameter parm in parameters) { command.Parameters.Add(parm); } command.ExecuteNonQuery(); command.Parameters.Clear(); } } catch (Exception ex) { throw new Exception(sql, ex); } finally { transConn.Close(); } }
private List <T> _findAllFromBuilder(Query query) { List <T> list = new List <T>(); SqlBuilder builder; String sql; try { builder = InitSqlBuilder(query); sql = builder.Build(); } catch (ExceptionEmptyQuery) { return(new List <T>()); } SqlTransConn transConn = GetConnection(); try { using (SqlCommand command = transConn.CreateCommand()) { command.CommandText = sql; command.CommandType = CommandType.Text; List <SqlParameter> parameters = builder.GetSqlParams(); foreach (SqlParameter parm in parameters) { command.Parameters.Add(parm); } using (SqlDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { T to = new T(); to.Initialize(reader); LateBinding(ref to, query); list.Add(to); } } } command.Parameters.Clear(); } } catch (Exception ex) { throw new Exception(sql, ex); } finally { transConn.Close(); } return(list); }
private List <T> _findAllFromSQLText(string sql, List <SqlParameter> parameters) { List <T> list = new List <T>(); SqlTransConn transConn = GetConnection(_connectionName); try { using (SqlCommand command = transConn.CreateCommand()) { command.CommandText = sql; command.CommandType = CommandType.Text; command.CommandTimeout = 120; if (null != parameters) { foreach (SqlParameter parm in parameters) { command.Parameters.Add(parm); } } using (SqlDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { T to = new T(); to.Initialize(reader); //LateBinding(ref to, query); list.Add(to); } } } command.Parameters.Clear(); } } catch (Exception ex) { throw new Exception(sql + ",message:" + ex.Message, ex); } finally { if (!transConn.IsInTransaction()) { transConn.Close(); } } return(list); }
public List <T> ExecuteQueryStringAndReturn(string v_sQueryString, SqlParameter[] v_oSqlParams) { List <T> list = new List <T>(); SqlTransConn transConn = GetConnection(); try { using (SqlCommand command = transConn.CreateCommand()) { command.CommandText = v_sQueryString; command.CommandType = CommandType.Text; foreach (SqlParameter parm in v_oSqlParams) { command.Parameters.Add(parm); } using (SqlDataReader reader = command.ExecuteReader()) { if (reader.HasRows) { while (reader.Read()) { T to = new T(); to.Initialize(reader); list.Add(to); } } } command.Parameters.Clear(); } } catch (Exception ex) { throw new Exception(v_sQueryString, ex); } finally { transConn.Close(); } return(list); }
public int ExecuteQueryStringAndReturnId(string v_sQueryString, SqlParameter[] v_oSqlParams) { int nowid = 0; DataSet ds = new DataSet(); SqlDataAdapter dap = new SqlDataAdapter(); SqlTransConn transConn = GetConnection(); try { SqlCommand command = transConn.CreateCommand(); command.CommandText = v_sQueryString; command.CommandType = CommandType.Text; foreach (SqlParameter parm in v_oSqlParams) { command.Parameters.Add(parm); } dap.SelectCommand = command; dap.Fill(ds); if (ds.Tables[0].Rows[0][1].ToString() != "0") { return(0); } else { nowid = Convert.ToInt32(ds.Tables[0].Rows[0][0].ToString()); } return(nowid); } catch (Exception ex) { throw new Exception(v_sQueryString, ex); } finally { transConn.Close(); } }
public void ExecuteQueryString(string v_sQueryString, SqlParameter[] v_oSqlParams, string key) { SqlTransConn transConn = GetConnection(); if (!string.IsNullOrEmpty(key)) { if (this.UpdateMap.ContainsKey(key)) { v_sQueryString = this.UpdateMap[key]; } } try { using (SqlCommand command = transConn.CreateCommand()) { command.CommandText = v_sQueryString; command.CommandType = CommandType.Text; foreach (SqlParameter parm in v_oSqlParams) { command.Parameters.Add(parm); } command.ExecuteNonQuery(); command.Parameters.Clear(); } } catch (Exception ex) { throw new Exception(v_sQueryString, ex); } finally { transConn.Close(); } }
/// <summary> /// Get the count(*) of the SQL built from the filter, without the limit/paging /// </summary> /// <param name="filter"></param> /// <returns></returns> public virtual int GetTotal(Query query) { SetInternalBinding(true); int total = 0; SqlBuilder builder; if (query != null) { try { builder = InitSqlBuilder(query); } catch (ExceptionEmptyQuery) { return(0); } catch (ExceptionBadFilterSequence) { // return empty list return(0); } } else { throw new Exception("Not supported"); } string sql = builder.BuildCountSql(); SqlTransConn transConn = GetConnection(_connectionName); try { using (SqlCommand command = transConn.CreateCommand()) { command.CommandText = sql; command.CommandType = CommandType.Text; List <SqlParameter> parameters = builder.GetSqlParams(); foreach (SqlParameter param in parameters) { command.Parameters.Add(param); } total = (int)command.ExecuteScalar(); command.Parameters.Clear(); } } catch (Exception ex) { throw new Exception(sql, ex); } finally { if (!transConn.IsInTransaction()) { transConn.Close(); } } // set this so that the value can be used to determine IsValidRange of limit/offset/page query.Total = total; return(total); }