/// <summary> /// return the value of the parameter which is sqldbDirection.outPut /// </summary> /// <param name="v_sStoreProcName"></param> /// <param name="v_oSqlParams"></param> /// <returns></returns> public string ExecuteProcAndGetOutPut(string v_sStoreProcName, SqlParameter[] v_oSqlParams) { string v_sReturnValue = ""; int v_iOutputIndex = 0; List <SqlParameter> parameters = new List <SqlParameter>(); SqlTransConn transConn = GetConnection(); foreach (SqlParameter pram in v_oSqlParams) { if (pram.Direction == ParameterDirection.Output || pram.Direction == ParameterDirection.ReturnValue) { v_iOutputIndex = parameters.Count; } parameters.Add(pram); } try { SqlHelper.ExecuteNonQuery(transConn, CommandType.StoredProcedure, v_sStoreProcName, parameters); v_sReturnValue = parameters[v_iOutputIndex].Value.ToString(); } catch (Exception ex) { throw new Exception("Failed to execute store proc:" + v_sStoreProcName, ex); } finally { transConn.Close(); } return(v_sReturnValue); }
/// <summary> /// Prepare a command for execution /// </summary> /// <param name="cmd">SqlCommand object</param> /// <param name="transConn">SqlTransaction/connection object</param> /// <param name="cmdType">Cmd type e.g. stored procedure or text</param> /// <param name="cmdText">Command text, e.g. Select * from Products</param> /// <param name="cmdParms">SqlParameters to use in the command</param> private static void PrepareCommand(SqlCommand cmd, SqlTransConn transConn, CommandType cmdType, string cmdText, List <SqlParameter> parameters) { SqlConnection conn = transConn.Conn; if (conn.State != ConnectionState.Open) { conn.Open(); } cmd.Connection = conn; cmd.CommandText = cmdText; if (transConn.Trans != null) { cmd.Transaction = transConn.Trans; } cmd.CommandType = cmdType; //设置超时时间 cmd.CommandTimeout = 120; if (parameters != null) { foreach (SqlParameter parm in parameters) { cmd.Parameters.Add(parm); } } }
// 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> _findAllFromStoreProc(string storeProcName, List <SqlParameter> parameters) { List <T> list = new List <T>(); SqlTransConn transConn = GetConnection(_connectionName); try { using (SqlDataReader reader = SqlHelper.ExecuteReader(transConn, CommandType.StoredProcedure, storeProcName, parameters)) { if (reader.HasRows) { while (reader.Read()) { T to = new T(); to.Initialize(reader); //LateBinding(ref to, query); list.Add(to); } } } } catch (Exception ex) { throw new Exception("Failed to execute store proc:" + storeProcName, ex); } finally { if (!transConn.IsInTransaction()) { transConn.Close(); } } return(list); }
/// <summary> /// return the value of the parameter which is sqldbDirection.outPut /// </summary> /// <param name="v_sStoreProcName"></param> /// <param name="v_oSqlParams"></param> /// <returns></returns> public int ExecuteProc(Query query) { List <SqlParameter> parameters = new List <SqlParameter>(); SqlTransConn transConn = GetConnection(); string storeProcName = query.Procedure; // generic, no mapping // rely on input parameter type foreach (string key in query.Parameters.Keys) { SqlParameter parameter = new SqlParameter(key, query.Parameters[key].Type); parameter.Value = query.Parameters[key].Value; parameter.Direction = query.Parameters[key].Direction; parameters.Add(parameter); } int result; try { result = SqlHelper.ExecuteNonQuery(transConn, CommandType.StoredProcedure, storeProcName, parameters); } catch (Exception ex) { throw new Exception("Failed to execute store proc:" + storeProcName, ex); } finally { transConn.Close(); } return(result); }
public void Rollback() { SqlTransConn transConn = transConnCollection["TransConn_" + connHashKey]; transConn.Rollback(); transConnCollection.Remove("TransConn_" + connHashKey); }
public static List <T> ExecuteReader <T>(SqlTransConn transConn, CommandType cmdType, string cmdText, List <SqlParameter> parameters) where T : BaseTO, new() { List <T> list = new List <T>(); try { using (SqlDataReader reader = SqlHelper.ExecuteReader(transConn, cmdType, cmdText, parameters)) { if (reader.HasRows) { while (reader.Read()) { T to = new T(); to.Initialize(reader); list.Add(to); } } } } catch (Exception ex) { throw new Exception("Failed to execute sql:" + cmdText, ex); } finally { transConn.Close(); } return(list); }
public int DeleteByPK(object PKValue) { StringBuilder sb = new StringBuilder(); sb.Append("Delete from ").Append(TableName); sb.Append(" where ").Append(PrimaryKey).Append("=@PKValue"); List <SqlParameter> parameters = new List <SqlParameter>(); parameters.Add(new SqlParameter("@PKValue", PKValue)); SqlTransConn transConn = GetConnection(); int result = 0; try { result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters); } catch (Exception ex) { throw new Exception(ex.Message + ":" + sb.ToString(), ex); } finally { transConn.Close(); } return(result); }
public bool ExecuteNonQuery(string v_sQueryString, SqlParameter[] v_oSqlParams, out int count) { bool bol = false; count = 0; SqlTransConn transConn = GetConnection(); try { count = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, v_sQueryString, v_oSqlParams != null ? v_oSqlParams.ToList() : null); if (count > 0) { bol = true; } } catch (Exception ex) { throw new Exception(v_sQueryString, ex); } finally { transConn.Close(); } return(bol); }
/// <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); }
/// <summary> /// Use this if transaction needs to be used ACROSS different DAOs with same DB /// Auto get connection if not already done /// </summary> public SqlTransConn StartSharedConnection() { SqlTransConn transConn = GetConnection(); transConnCollection.Add("TransConn_" + connHashKey, transConn); return(transConn); }
/// <summary> /// Use this if transaction needs to be used ACROSS different DAOs with same DB /// Auto get connection if not already done /// </summary> public static SqlTransConn StartSharedConnection(string connectionName) { SqlTransConn transConn = GetConnection(connectionName); DAGloble.TransactionPool.ThreadSafeWrite <string, SqlTransConn>(connectionName, transConn, "TransactionPool"); return(transConn); }
/// <summary> /// 根据主键数组和给定实体的值更新 /// </summary> /// <param name="to"></param> /// <param name="ids"></param> /// <param name="enablePKUpdate">是否允许更新主键,用于某些自定义主键需要更新的情况</param> public int UpdateFromIds(BaseTO to, long[] ids, bool enablePKUpdate) { Dictionary <string, object> dict; dict = to.GetSQLWriteValues(); //我把recordcount放在baseTo里面,用于列表分页时返回记录总数,这里新增修改时需要过滤掉 kevin dict.Remove("recordcount"); StringBuilder sb = new StringBuilder(); sb.Append("update ").Append(_tableName).Append(" set "); List <SqlParameter> parameters = new List <SqlParameter>(); int i = 0; string[] primaryKeys = to.GetPrimaryKey <T>(); foreach (string name in dict.Keys) { //如果不允许更新主键,不是主键才添加到update语句的更新字段中 if (enablePKUpdate ? true : !primaryKeys.Contains <string>(name)) { if (i > 0) { sb.Append(", "); } sb.Append(name).Append("=@").Append(name); parameters.Add(Query.BuilderParam(name, dict[name])); i++; } } if (null == primaryKeys || 0 == primaryKeys.Length) { throw new ExceptionNoPrimaryKey(); } string idList = string.Join(",", ids); sb.Append(" where " + primaryKeys[0] + " in ( " + idList + ")"); SqlTransConn transConn = GetConnection(_connectionName); int result = 0; try { result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters); } catch (Exception ex) { throw new Exception(ex.Message + ":" + sb.ToString(), ex); } finally { if (!transConn.IsInTransaction()) { transConn.Close(); } } return(result); }
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); }
public static object ExecuteScalar(SqlTransConn transConn, CommandType cmdType, string cmdText, List <SqlParameter> parameters) { SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transConn, cmdType, cmdText, parameters); object val = cmd.ExecuteScalar(); cmd.Parameters.Clear(); return(val); }
/// <summary> /// Close and abort any transaction /// </summary> public void CloseSharedConnection() { SqlTransConn transConn = transConnCollection["TransConn_" + connHashKey]; if (transConn != null) { transConn.Close(); transConnCollection.Remove("TransConn_" + connHashKey); } }
public void Commit() { SqlTransConn transConn = transConnCollection["TransConn_" + connHashKey]; transConn.Commit(); if (!transConn.IsInTransaction()) { transConnCollection.Remove("TransConn_" + connHashKey); } }
/// <summary> /// Close and abort any transaction /// </summary> public static void CloseSharedConnection(string connectionName) { SqlTransConn transConn = DAGloble.TransactionPool.ThreadSafeRead <string, SqlTransConn>(connectionName, "TransactionPool"); if (transConn != null) { transConn.Close(); DAGloble.TransactionPool.ThreadSafeRemove <string, SqlTransConn>(connectionName, "TransactionPool"); } }
public List <T> ExecuteProcAndGetOutput(Query query, out int outParamValue) { int v_iOutputIndex = -1; List <SqlParameter> parameters = new List <SqlParameter>(); SqlTransConn transConn = GetConnection(); string storeProcName = query.Procedure; int i = 0; foreach (string key in query.Parameters.Keys) { SqlParameter parameter = new SqlParameter(key, query.Parameters[key].Type); parameter.Value = query.Parameters[key].Value; parameter.Direction = query.Parameters[key].Direction; parameters.Add(parameter); if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.ReturnValue) { v_iOutputIndex = i; } i++; } //List<T> result; List <T> list = new List <T>(); try { using (SqlDataReader reader = SqlHelper.ExecuteReader(transConn, CommandType.StoredProcedure, storeProcName, parameters, false)) { if (reader.HasRows) { while (reader.Read()) { T to = new T(); to.Initialize(reader); LateBinding(ref to, query); list.Add(to); } } } //reader.Close();// Should close DataReader first so that can get parameters value. outParamValue = int.Parse(parameters[v_iOutputIndex].Value.ToString()); //outParamValue = 1; } catch (Exception ex) { throw new Exception("Failed to execute store proc:" + storeProcName, 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); }
/// <summary> /// return the value of the parameter which is sqldbDirection.outPut /// </summary> /// <param name="v_sStoreProcName"></param> /// <param name="v_oSqlParams"></param> /// <returns></returns> public Q ExecuteProcAndGetOutput <Q>(Query query) { Q v_sReturnValue; int v_iOutputIndex = -1; List <SqlParameter> parameters = new List <SqlParameter>(); SqlTransConn transConn = GetConnection(); string storeProcName = query.Procedure; // generic, no mapping // rely on input parameter type int i = 0; foreach (string key in query.Parameters.Keys) { SqlParameter parameter = new SqlParameter(key, query.Parameters[key].Type); parameter.Value = query.Parameters[key].Value; parameter.Direction = query.Parameters[key].Direction; parameters.Add(parameter); if (parameter.Direction == ParameterDirection.Output || parameter.Direction == ParameterDirection.ReturnValue) { v_iOutputIndex = i; } i++; } try { SqlHelper.ExecuteNonQuery(transConn, CommandType.StoredProcedure, storeProcName, parameters); if (v_iOutputIndex > -1) { v_sReturnValue = (Q)parameters[v_iOutputIndex].Value; } else { v_sReturnValue = default(Q); } } catch (Exception ex) { throw new Exception("Failed to execute store proc:" + storeProcName, ex); } finally { transConn.Close(); } return(v_sReturnValue); }
private void _updateOrAddOrDeleteDBFromStoreProc(Query query) { List <SqlParameter> parameters = new List <SqlParameter>(); string storeProcName; if (StoreProcMap != null && StoreProcMap.ContainsKey(query.Procedure)) { Query.StoreProcType storeproc = this.StoreProcMap[query.Procedure]; storeProcName = storeproc.Name; foreach (string key in storeproc.Parameters.Keys) { // use parameter type from storeproc definition SqlParameter parameter = new SqlParameter(key, storeproc.Parameters[key]); parameter.Value = query.Parameters[key].Value; parameters.Add(parameter); } } else { storeProcName = query.Procedure; // generic, no mapping // rely on input parameter type foreach (string key in query.Parameters.Keys) { SqlParameter parameter = new SqlParameter(key, query.Parameters[key].Type); parameter.Value = query.Parameters[key].Value; parameters.Add(parameter); } } SqlTransConn transConn = GetConnection(); try { SqlHelper.ExecuteNonQuery(transConn, CommandType.StoredProcedure, storeProcName, parameters); } catch (Exception ex) { throw new Exception("Failed to execute store proc:" + storeProcName, ex); } finally { transConn.Close(); } }
public int DeleteByPKArray(long[] idArrar) { StringBuilder sb = new StringBuilder(); sb.Append("Delete from ").Append(_tableName); string[] primaryKeys = BaseTO.GetSQLPrimaryKey(BaseTO.GetTableName <T>()); if (null == primaryKeys || 0 == primaryKeys.Length) { throw new ExceptionNoPrimaryKey(); } if (1 < primaryKeys.Length) { //此方法只用于实体类只有一个主键的情况 throw new ExceptionErrorPrimaryKeyNumber(); } string idList = string.Join(",", idArrar); sb.Append(" where ") .Append(primaryKeys[0]) .Append(" in (") .Append(idList) .Append(")"); List <SqlParameter> parameters = new List <SqlParameter>(); SqlTransConn transConn = GetConnection(_connectionName); int result = 0; try { result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), null); } catch (Exception ex) { throw new Exception(ex.Message + ":" + sb.ToString(), ex); } finally { if (!transConn.IsInTransaction()) { transConn.Close(); } } return(result); }
/// <summary> /// create a new connection (to avoid connection sharing issue) if transaction was not started /// return a shared connection (with transaction on) if transaction was started /// </summary> protected static SqlTransConn GetConnection(string connectionName) { SqlTransConn transConn = DAGloble.TransactionPool.ContainsKey(connectionName) ? DAGloble.TransactionPool.ThreadSafeRead <string, SqlTransConn>(connectionName, "TransactionPool") : null; if (transConn == null) { transConn = new SqlTransConn(SqlServerSet.GetConnectionString(connectionName)); } else { if (transConn.Conn.State != ConnectionState.Open)//Transcation is running, open a new connection { transConn = new SqlTransConn(SqlServerSet.GetConnectionString(connectionName)); } } return(transConn); }
public int DeleteByPK(long id) { StringBuilder sb = new StringBuilder(); sb.Append("Delete from ").Append(_tableName); string[] primaryKeys = BaseTO.GetSQLPrimaryKey(BaseTO.GetTableName <T>()); if (null == primaryKeys || 0 == primaryKeys.Length) { throw new ExceptionNoPrimaryKey(); } if (1 < primaryKeys.Length) { //此方法只用于实体类只能有一个主键的情况 throw new ExceptionErrorPrimaryKeyNumber(); } sb.Append(" where ").Append(primaryKeys[0]).Append("=@PKValue"); List <SqlParameter> parameters = new List <SqlParameter>(); SqlParameter parameter = new SqlParameter("@PKValue", SqlDbType.Int, 0); parameter.Value = id; parameters.Add(parameter); SqlTransConn transConn = GetConnection(_connectionName); int result = 0; try { result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters); } catch (Exception ex) { throw new Exception(ex.Message + ":" + sb.ToString(), ex); } finally { if (!transConn.IsInTransaction()) { transConn.Close(); } } return(result); }
public int UpdateFromTO(BaseTO to, object PKValue) { Dictionary <string, object> dict = to.GetSQLWriteValues(); StringBuilder sb = new StringBuilder(); sb.Append("update ").Append(TableName).Append(" set "); List <SqlParameter> parameters = new List <SqlParameter>(); int i = 0; foreach (string name in dict.Keys) { if (i > 0) { sb.Append(", "); } sb.Append(name).Append("=@").Append(name); parameters.Add(Query.BuilderParam(name, dict[name])); i++; } sb.Append(" where ").Append(PrimaryKey).Append("=@PKValue"); parameters.Add(new SqlParameter("@PKValue", PKValue)); SqlTransConn transConn = GetConnection(); int result = 0; try { result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters); } catch (Exception ex) { throw new Exception(ex.Message + ":" + sb.ToString(), ex); } finally { transConn.Close(); } return(result); }
public int InsertFromTO(BaseTO to) { Dictionary <string, object> dict = to.GetSQLWriteValues(); StringBuilder sb = new StringBuilder(); sb.Append("Insert into ").Append(TableName).Append(" ("); sb.Append(String.Join(", ", dict.Keys.ToArray())); sb.Append(") values ("); List <SqlParameter> parameters = new List <SqlParameter>(); int i = 0; foreach (string name in dict.Keys) { if (i > 0) { sb.Append(", "); } sb.Append("@").Append(name); parameters.Add(Query.BuilderParam(name, dict[name])); i++; } sb.Append(")"); SqlTransConn transConn = GetConnection(); int result = 0; try { result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters); } catch (Exception ex) { throw new Exception(ex.Message + ":" + sb.ToString(), ex); } finally { transConn.Close(); } return(result); }
public static SqlDataReader ExecuteReader(SqlTransConn transConn, CommandType cmdType, string cmdText, List <SqlParameter> parameters) { SqlCommand cmd = new SqlCommand(); // we use a try/catch here because if the method throws an exception we want to // close the connection throw code, because no datareader will exist, hence the // commandBehaviour.CloseConnection will not work try { PrepareCommand(cmd, transConn, cmdType, cmdText, parameters); SqlDataReader rdr = cmd.ExecuteReader(!transConn.IsInTransaction() ? CommandBehavior.CloseConnection : CommandBehavior.Default); cmd.Parameters.Clear(); return(rdr); } catch (Exception ex) { throw ex; } }
public int DeleteByPK(T t) { StringBuilder sb = new StringBuilder(); sb.Append("Delete from ").Append(_tableName); string[] primaryKeys = t.GetPrimaryKey <T>(); if (null == primaryKeys || 0 == primaryKeys.Length) { throw new ExceptionNoPrimaryKey(); } sb.Append(" where 1=1"); List <SqlParameter> parameters = new List <SqlParameter>(); foreach (string pk in primaryKeys) { sb.Append(" and ").Append(pk).Append("=@" + pk); SqlParameter parameter = new SqlParameter("@" + pk, SqlDbType.NVarChar); parameter.Value = t.GetPropertyValue(pk); parameters.Add(parameter); } SqlTransConn transConn = GetConnection(_connectionName); int result = 0; try { result = SqlHelper.ExecuteNonQuery(transConn, CommandType.Text, sb.ToString(), parameters); } catch (Exception ex) { throw new Exception(ex.Message + ":" + sb.ToString(), ex); } finally { if (!transConn.IsInTransaction()) { transConn.Close(); } } return(result); }