SQLServer封装【包含事务】
使用方式
SqlHelper SqlHelp = new SqlHelper(); SqlHelp.Open(); //开启事务 SqlHelp.TranBegin(); //执行各种SQL语句 //提交事务 SqlHelp.TranCommit(); //回滚事务 SqlHelp.TranRollBack(); SqlHelp.Close();
public class SqlHelper { /// <summary> /// 数据库连接串 /// </summary> private string connStr = CommonConfiger.DBConStr; /// <summary> /// 获取或设置数据库连接字符串(默认使用的是接口数据库) /// </summary> public string ConnStr { get { return connStr; } set { connStr = value; } } /// <summary> /// 声明sqlConn的连接 /// </summary> public SqlConnection sqlConn = null; /// <summary> /// 声明sqlTrans事务 /// </summary> public SqlTransaction sqlTrans = null; /// <summary> /// 声明sqlCmd命令 /// </summary> private SqlCommand sqlCmd = null; /// <summary> /// 打开连接 /// </summary> public void Open() { //SqlConnectionStringBuilder sqlConnString = new SqlConnectionStringBuilder(); if (object.Equals(sqlConn, null)) { sqlConn = new SqlConnection(connStr); } if (sqlConn.State != ConnectionState.Open) { sqlConn.Open(); } } /// <summary> /// 关闭连接 /// </summary> public void Close() { if (!object.Equals(sqlConn, null)) { if (sqlCmd != null) { sqlCmd.Dispose(); } sqlConn.Close(); GC.Collect(); } } /// <summary> /// 关闭连接 /// </summary> /// <param name="IsClearPool">是否清理连接池,True则清理</param> public void Close(bool IsClearPool) { if (!object.Equals(sqlConn, null)) { if (sqlCmd != null) { sqlCmd.Dispose(); } sqlConn.Close(); if (IsClearPool) { SqlConnection.ClearPool(sqlConn);//回收连接池中的睡眠连接 sqlConn.Dispose(); sqlConn = null; GC.Collect();//垃圾回收 } } } /// <summary> /// 开始事务 /// </summary> public void TranBegin() { sqlCmd = new SqlCommand(); if (sqlConn == null) { sqlConn = new SqlConnection(connStr); } sqlCmd.Connection = sqlConn; if (sqlConn.State != ConnectionState.Open) { sqlConn.Open(); } sqlTrans = sqlConn.BeginTransaction(); sqlCmd.CommandTimeout = 1200; sqlCmd.Transaction = sqlTrans; //Util.CommonLog.WriteLogInfo("SqlHelper", new Exception("事务开启")); } /// <summary> /// 不开启事务处理 /// </summary> public void NotTranBegin() { { sqlCmd = new SqlCommand(); sqlCmd.Connection = sqlConn; //sqlTrans = sqlConn.BeginTransaction(); sqlCmd.CommandTimeout = 1200; //sqlCmd.Transaction = sqlTrans; } } /// <summary> /// 提交事务 /// </summary> public void TranCommit() { if (!object.Equals(null, sqlTrans)) { sqlTrans.Commit(); sqlTrans = null; if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); } //Util.CommonLog.WriteLogInfo("SqlHelper", new Exception("事务提交")); } } /// <summary> /// 回滚事务 /// </summary> public void TranRollBack() { if (!object.Equals(null, sqlTrans)) { if (!object.Equals(null, sqlTrans.Connection)) { sqlTrans.Rollback(); sqlTrans = null; if (sqlConn.State == ConnectionState.Open) { sqlConn.Close(); } } } } /// <summary> /// 设置是否执行的是存储过程 /// <param name="cmmandType">CommandType 类型</param> /// </summary> public void SetCommandType(CommandType cmmandType) { if (!object.Equals(null, sqlCmd)) { sqlCmd.CommandType = CommandType.StoredProcedure; } } #region private utility methods & constructors /// <summary> /// This method is used to attach array of SqlParameters to a SqlCommand. /// This method will assign a value of DbNull to any parameter with a direction of /// InputOutput and a value of null. /// This behavior will prevent default values from being used, but /// this will be the less common case than an intended pure output parameter (derived as InputOutput) /// where the user provided no input value. /// </summary> /// <param name="command">The command to which the parameters will be added</param> /// <param name="commandParameters">an array of SqlParameters tho be added to command</param> private void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) { foreach (SqlParameter p in commandParameters) { //check for derived output value with no value assigned if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } /// <summary> /// This method assigns an array of values to an array of SqlParameters. /// </summary> /// <param name="commandParameters">array of SqlParameters to be assigned values</param> /// <param name="parameterValues">array of Components holding the values to be assigned</param> private void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues) { if ((commandParameters == null) || (parameterValues == null)) { //do nothing if we get no data return; } // we must have the same number of values as we pave parameters to put them in if (commandParameters.Length != parameterValues.Length) { throw new ArgumentException("Parameter count does not match Parameter Value count."); } //iterate through the SqlParameters, assigning the values from the corresponding position in the //value array for (int i = 0, j = commandParameters.Length; i < j; i++) { commandParameters[i].Value = parameterValues[i]; } } /// <summary> /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command. /// </summary> /// <param name="command">the SqlCommand to be prepared</param> /// <param name="connection">a valid SqlConnection, on which to execute this command</param> /// <param name="transaction">a valid SqlTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param> private void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters) { //if the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { connection.Open(); } //associate the connection with the command command.Connection = connection; command.CommandTimeout = 300; //set the command text (stored procedure name or SQL statement) command.CommandText = commandText; //if we were provided a transaction, assign it. if (transaction != null) { command.Transaction = transaction; } //set the command type command.CommandType = commandType; //attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(command, commandParameters); } return; } /// <summary> /// ExecuteNonQuery方法专用 /// 可以把方法都放到一个事务中 /// This method opens (if necessary) and assigns a connection, transaction, command type and parameters /// to the provided command. /// </summary> /// <param name="command">the SqlCommand to be prepared</param> /// <param name="connection">a valid SqlConnection, on which to execute this command</param> /// <param name="transaction">a valid SqlTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param> private void PrepareCommand(CommandType commandType, string commandText, SqlParameter[] commandParameters) { //if the provided connection is not open, we will open it if (sqlConn.State != ConnectionState.Open) { sqlConn.Open(); } //set the command text (stored procedure name or SQL statement) sqlCmd.CommandText = commandText; //set the command type sqlCmd.CommandType = commandType; //attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(sqlCmd, commandParameters); } return; } #endregion private utility methods & constructors #region DataHelpers public string CheckNull(object obj) { return (string)obj; } public string CheckNull(DBNull obj) { return null; } #endregion #region AddParameters public object CheckForNullString(string text) { if (text == null || text.Trim().Length == 0) { return DBNull.Value; } else { return text; } } public SqlParameter MakeInParam(string ParamName, object Value) { return new SqlParameter(ParamName, Value); } /// <summary> /// Make input param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <param name="Value">Param value.</param> /// <returns>New parameter.</returns> public SqlParameter MakeInParam(string ParamName, SqlDbType DbType, Int32 Size, object Value) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Input, Value); } /// <summary> /// Make input param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <returns>New parameter.</returns> public SqlParameter MakeOutParam(string ParamName, SqlDbType DbType, int Size) { return MakeParam(ParamName, DbType, Size, ParameterDirection.Output, null); } /// <summary> /// Make stored procedure param. /// </summary> /// <param name="ParamName">Name of param.</param> /// <param name="DbType">Param type.</param> /// <param name="Size">Param size.</param> /// <param name="Direction">Parm direction.</param> /// <param name="Value">Param value.</param> /// <returns>New parameter.</returns> public SqlParameter MakeParam(string ParamName, SqlDbType DbType, int Size, ParameterDirection Direction, object Value) { SqlParameter param; if (Size > 0) param = new SqlParameter(ParamName, DbType, Size); else param = new SqlParameter(ParamName, DbType); param.Direction = Direction; if (!(Direction == ParameterDirection.Output && Value == null)) param.Value = Value; return param; } /// <summary> /// 把Hashtable转化为SqlParameter参数组 /// </summary> /// <param name="ParamList">Hashtable</param> /// <returns></returns> public SqlParameter[] MakeAllParam(Hashtable ParamList) { SqlParameter[] p1 = new SqlParameter[ParamList.Count]; int i = 0; System.Collections.IDictionaryEnumerator CacheEnum = ParamList.GetEnumerator(); while (CacheEnum.MoveNext()) { p1[i] = MakeInParam(CacheEnum.Key.ToString(), CacheEnum.Value); i++; } return p1; } #endregion #region ExecuteNonQuery /// <summary> /// 增、删、改的方法[ExecuteNonQuery] 返回所影响的行数,执行其他 /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms"></param> /// <returns></returns> public int ExecuteNonQuery(string sql, CommandType commandType, params SqlParameter[] commandParameters) { //create a command and prepare it for execution if (sqlConn.State != ConnectionState.Open) { sqlConn.Open(); } if (sqlCmd == null) { sqlCmd = sqlConn.CreateCommand(); } //set the command text (stored procedure name or SQL statement) sqlCmd.CommandText = sql; //set the command type sqlCmd.CommandType = commandType; //attach the command parameters if they are provided if (commandParameters != null) { AttachParameters(sqlCmd, commandParameters); } //finally, execute the command. int retval = sqlCmd.ExecuteNonQuery(); // detach the SqlParameters from the command object, so they can be used again. sqlCmd.Parameters.Clear(); return retval; } /// <summary> /// 执行查询、查询多少条数据;返回第一行,第一列方法[ExecuteScalar] 返回-1执行失败 /// </summary> /// <param name="sql">Sql命令</param> /// <param name="cmdtype">SQL语句(CommandType.Text)或者存储过程(CommandType.StoredProcedure)</param> /// <param name="pms"></param> /// <returns></returns> public object ExecuteScalar(string sql, CommandType cmdtype, params SqlParameter[] pms) { try { SqlConnection con = new SqlConnection(connStr); using (SqlCommand cmd = new SqlCommand(sql, con)) { //存储过程或者Sql语句 cmd.CommandType = cmdtype; if (pms != null) { cmd.Parameters.AddRange(pms); } if (con.State != ConnectionState.Open) { con.Open(); } return cmd.ExecuteScalar(); } } catch (Exception) { throw; } } /// <summary> /// 增、删、改的方法[ExecuteNonQuery] 返回所影响的行数,执行其他 /// </summary> /// <param name="commandText"></param> /// <returns></returns> public int ExecuteNonQuery(string commandText) { return ExecuteNonQuery(commandText, CommandType.Text, (SqlParameter[])null); } /// <summary> /// 执行一个简单的Sqlcommand 没有返回结果 /// </summary> /// 例如: /// int result = ExecuteNonQuery("delete from test where tt =@tt", new SqlParameter("@tt", 24)); /// <param name="commandText">只能是sql语句</param> /// <param name="commandParameters">参数</param> /// <returns>受影响的行数</returns> public int ExecuteNonQuery(string commandText, params SqlParameter[] commandParameters) { return ExecuteNonQuery(commandText, CommandType.Text, commandParameters); } /// <summary> /// 执行一个简单的Sqlcommand 没有返回结果 /// 参数是Hashtable /// </summary> /// <param name="commandText">只能是sql语句</param> /// <param name="commandParameters">Hashtable参数</param> /// <returns>受影响的行数</returns> public int ExecuteNonQuery(string commandText, Hashtable commandParameters) { return ExecuteNonQuery(commandText, CommandType.Text, MakeAllParam(commandParameters)); } #endregion ExecuteNonQuery #region SqlDataAdapter public SqlDataAdapter ExecuteSqlDataAdapter(string commandText, params SqlParameter[] commandParameters) { SqlConnection cn = new SqlConnection(); cn.ConnectionString = connStr; cn.Open(); SqlDataAdapter myda = new SqlDataAdapter(commandText, cn); foreach (SqlParameter p in commandParameters) { //check for derived output value with no value assigned if ((p.Direction == ParameterDirection.InputOutput) && (p.Value == null)) { p.Value = DBNull.Value; } myda.SelectCommand.Parameters.Add(p); } cn.Close(); return myda; } /// <summary> /// 构建DataAdapter的方法, /// </summary> /// <param name="commandText"></param> /// <param name="paramsTable"></param> /// <returns></returns> public SqlDataAdapter ExecuteSqlDataAdapter(string commandText, Hashtable paramsTable) { return ExecuteSqlDataAdapter(commandText, MakeAllParam(paramsTable)); } /// <summary> /// 构建DataAdapter的方法, /// </summary> /// <param name="commandText">简单sql语句</param> /// <returns>返回值</returns> public SqlDataAdapter ExecuteSqlDataAdapter(string commandText) { SqlConnection cn = new SqlConnection(); cn.ConnectionString = connStr; cn.Open(); SqlDataAdapter myda = new SqlDataAdapter(commandText, cn); cn.Close(); return myda; } #endregion #region ExecuteDataSet /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public DataSet ExecuteDataset(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteDataset(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public DataSet ExecuteDataset(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //SqlNotice st = new SqlNotice(); //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //create the DataAdapter & DataSet SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //st.End("ExecuteDataset_1", commandText); //return the dataset return ds; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataSet ds = ExecuteDataset(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a dataset containing the resultset generated by the command</returns> public DataSet ExecuteDataset(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataSet SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); //fill the DataSet using default values for DataTable names, etc. da.Fill(ds); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //st.End("ExecuteDataset_2", commandText); //return the dataset return ds; } /// <summary> /// 返回datataset 只需要传入查询语句 /// </summary> /// <param name="commandText"></param> /// <returns></returns> public DataSet ExecuteDataset(string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(connStr, CommandType.Text, commandText, (SqlParameter[])null); } /// <summary> /// 带参数查询 /// </summary> /// <param name="commandText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public DataSet ExecuteDataset(string commandText, params SqlParameter[] commandParameters) { using (SqlConnection cn = new SqlConnection(connStr)) { cn.Open(); return ExecuteDataset(cn, CommandType.Text, commandText, commandParameters); } } /// <summary> /// 返回DataSet /// </summary> /// <param name="commandText">sql语句</param> /// <param name="commandParameters">Hashtable参数</param> /// <returns>返回结果</returns> public DataSet ExecuteDataset(string commandText, Hashtable commandParameters) { using (SqlConnection cn = new SqlConnection(connStr)) { cn.Open(); return ExecuteDataset(cn, CommandType.Text, commandText, MakeAllParam(commandParameters)); } } /// <summary> /// 执行存储过程 返回相应的dataset /// </summary> /// <param name="commandText">存储过程名字</param> /// <param name="commandType"></param> /// <param name="commandParameters"></param> /// <returns></returns> public DataSet ExecuteDataset(string commandText, CommandType commandType, params SqlParameter[] commandParameters) { using (SqlConnection cn = new SqlConnection(connStr)) { cn.Open(); return ExecuteDataset(cn, commandType, commandText, commandParameters); } } /// <summary> /// 执行sql语句时 返回相应的dataset /// </summary> /// <param name="commandText">sql语句</param> /// <param name="commandType">参数类型</param> /// <param name="commandParameters">Hashtable参数</param> /// <returns>返回值</returns> public DataSet ExecuteDataset(string commandText, CommandType commandType, Hashtable commandParameters) { using (SqlConnection cn = new SqlConnection(connStr)) { cn.Open(); return ExecuteDataset(cn, commandType, commandText, MakeAllParam(commandParameters)); } } /// <summary> /// 事务中执行返回dataset /// </summary> /// <param name="transaction"></param> /// <param name="commandText"></param> /// <returns></returns> public DataSet ExecuteDataset(SqlTransaction transaction, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataset(transaction, CommandType.Text, commandText, (SqlParameter[])null); } /// <summary> /// 事务中执行返回dataset 可带Hashtable参数 /// </summary> /// <param name="transaction">事务</param> /// <param name="commandText">sql语句</param> /// <param name="commandParameters">Hashtable参数</param> /// <returns></returns> public DataSet ExecuteDataset(SqlTransaction transaction, string commandText, Hashtable commandParameters) { return ExecuteDataset(transaction, commandText, MakeAllParam(commandParameters)); } /// <summary> /// 事务中返回dataset 可带参数 /// </summary> /// <param name="transaction"></param> /// <param name="commandText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public DataSet ExecuteDataset(SqlTransaction transaction, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, CommandType.Text, commandText, commandParameters); //create the DataAdapter & DataSet SqlDataAdapter da = new SqlDataAdapter(cmd); DataSet ds = new DataSet(); da.Fill(ds); cmd.Parameters.Clear(); return ds; } #endregion ExecuteDataSet #region ExecuteDataTable /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public DataTable ExecuteDataTable(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. //if (object.Equals(sqlConn, null)) //{ using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteDataTable(cn, commandType, commandText, commandParameters); } // using (sqlConn cn = new SqlConnection(connectionString)) // { // sqlConn.Open(); // //call the overload that takes a connection in place of the connection string // return ExecuteDataTable(sqlConn, commandType, commandText, commandParameters); // } //} //else //{ // if (sqlConn.State != ConnectionState.Open) // { // sqlConn.Open(); // } // return ExecuteDataTable(sqlConn, commandType, commandText, commandParameters); //} } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public DataTable ExecuteDataTable(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //create the DataAdapter & DataTable SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); //fill the DataTable using default values for DataTable names, etc. da.Fill(dt); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //da.Dispose(); return dt; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// DataTable dt = ExecuteDataTable(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a DataTable containing the resultset generated by the command</returns> public DataTable ExecuteDataTable(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataTable SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); //fill the DataTable using default values for DataTable names, etc. da.Fill(dt); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //return the DataTable return dt; } /// <summary> /// 返回DataTable /// </summary> /// <param name="commandText">简单sql语句</param> /// <returns></returns> public DataTable ExecuteDataTable(string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connStr, CommandType.Text, commandText, (SqlParameter[])null); } /// <summary> /// 返回DataTable /// </summary> /// <param name="connectionString">连接串</param> /// <param name="commandText">简单sql语句</param> /// <returns></returns> public DataTable ExecuteDataTable(string connectionString, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connectionString, CommandType.Text, commandText, (SqlParameter[])null); } /// <summary> /// 返回DataTable /// </summary> /// <param name="commandText">简单sql语句</param> /// <param name="commandParameters">可以连接到的参数</param> /// <returns></returns> public DataTable ExecuteDataTable(string commandText, params SqlParameter[] commandParameters) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connStr, CommandType.Text, commandText, commandParameters); } /// <summary> /// 返回DataTable 可带Hashtable参数 /// </summary> /// <param name="commandText">sql语句</param> /// <param name="commandParameters">Hashtable参数</param> /// <returns></returns> public DataTable ExecuteDataTable(string commandText, Hashtable commandParameters) { //pass through the call providing null for the set of SqlParameters return ExecuteDataTable(connStr, CommandType.Text, commandText, MakeAllParam(commandParameters)); } #endregion ExecuteDataTable #region ExecuteReader /// <summary> /// this enum is used to indicate whether the connection was provided by the caller, or created by SqlHelper, so that /// we can set the appropriate CommandBehavior when calling ExecuteReader() /// </summary> private enum SqlConnectionOwnership { /// <summary>Connection is owned and managed by SqlHelper</summary> Internal, /// <summary>Connection is owned and managed by the caller</summary> External } /// <summary> /// Create and prepare a SqlCommand, and call ExecuteReader with the appropriate CommandBehavior. /// </summary> /// <remarks> /// If we created and opened the connection, we want the connection to be closed when the DataReader is closed. /// /// If the caller provided the connection, we want to leave it to them to manage. /// </remarks> /// <param name="connection">a valid SqlConnection, on which to execute this command</param> /// <param name="transaction">a valid SqlTransaction, or 'null'</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParameters to be associated with the command or 'null' if no parameters are required</param> /// <param name="connectionOwnership">indicates whether the connection parameter was provided by the caller, or created by SqlHelper</param> /// <returns>SqlDataReader containing the results of the command</returns> private SqlDataReader ExecuteReader(SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, SqlConnectionOwnership connectionOwnership) { //SqlNotice st = new SqlNotice(); //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, transaction, commandType, commandText, commandParameters); //create a reader SqlDataReader dr; // call ExecuteReader with the appropriate CommandBehavior if (connectionOwnership == SqlConnectionOwnership.External) { dr = cmd.ExecuteReader(); } else { dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); } // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); // st.End("ExecuteReader_1", commandText); return dr; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteReader(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(connString, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public SqlDataReader ExecuteReader(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection SqlConnection cn = new SqlConnection(connectionString); cn.Open(); try { //call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(cn, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal); } catch { //if we fail to return the SqlDatReader, we need to close the connection ourselves cn.Close(); throw; } } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteReader(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public SqlDataReader ExecuteReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //pass through the call to the private overload using a null transaction value and an externally owned connection return ExecuteReader(connection, (SqlTransaction)null, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteReader(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// SqlDataReader dr = ExecuteReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>a SqlDataReader containing the resultset generated by the command</returns> public SqlDataReader ExecuteReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //pass through to private overload, indicating that the connection is owned by the caller return ExecuteReader(transaction.Connection, transaction, commandType, commandText, commandParameters, SqlConnectionOwnership.External); } /// <summary> /// 返回SqlDataReader 只是传入一条sql语句 /// </summary> /// <param name="commandText">sql语句</param> /// <returns></returns> public SqlDataReader ExecuteReader(string commandText) { return ExecuteReader(connStr, CommandType.Text, commandText, (SqlParameter[])null); } /// <summary> /// 返回SqlDataReader 只是传入一条sql语句和相应的参数 /// </summary> /// <param name="commandText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public SqlDataReader ExecuteReader(string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection SqlConnection cn = new SqlConnection(connStr); cn.Open(); try { //call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(cn, null, CommandType.Text, commandText, commandParameters, SqlConnectionOwnership.Internal); } catch { //if we fail to return the SqlDatReader, we need to close the connection ourselves cn.Close(); throw; } } /// <summary> /// 返回SqlDataReader 只是传入一条sql语句和相应的Hashtable参数 /// </summary> /// <param name="commandText">sql语句</param> /// <param name="commandParameters">Hashtable参数</param> /// <returns>返回值</returns> public SqlDataReader ExecuteReader(string commandText, Hashtable commandParameters) { //create & open a SqlConnection SqlConnection cn = new SqlConnection(connStr); cn.Open(); try { //call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(cn, null, CommandType.Text, commandText, MakeAllParam(commandParameters), SqlConnectionOwnership.Internal); } catch { //if we fail to return the SqlDatReader, we need to close the connection ourselves cn.Close(); throw; } } /// <summary> /// 返回SqlDataReader 只是传入一条sql语句和相应的参数 /// </summary> /// <param name="commandText"></param> /// <param name="commandParameters"></param> /// <returns></returns> public SqlDataReader ExecuteReader(string commandText, CommandType commandType, params SqlParameter[] commandParameters) { //create & open a SqlConnection SqlConnection cn = new SqlConnection(connStr); cn.Open(); try { //call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(cn, null, commandType, commandText, commandParameters, SqlConnectionOwnership.Internal); } catch { //if we fail to return the SqlDatReader, we need to close the connection ourselves cn.Close(); throw; } } /// <summary> /// 返回SqlDataReader 只是传入一条sql语句和相应的Hashtable参数 /// </summary> /// <param name="commandText">sql语句</param> /// <param name="commandType">参数类型</param> /// <param name="commandParameters">Hashtable参数</param> /// <returns>返回值</returns> public SqlDataReader ExecuteReader(string commandText, CommandType commandType, Hashtable commandParameters) { //create & open a SqlConnection SqlConnection cn = new SqlConnection(connStr); cn.Open(); try { //call the private overload that takes an internally owned connection in place of the connection string return ExecuteReader(cn, null, commandType, commandText, MakeAllParam(commandParameters), SqlConnectionOwnership.Internal); } catch { //if we fail to return the SqlDatReader, we need to close the connection ourselves cn.Close(); throw; } } #endregion ExecuteReader #region ExecuteScalar /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the database specified in /// the connection string. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public object ExecuteScalar(string connectionString, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteScalar(connectionString, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the database specified in the connection string /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(connString, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connectionString">a valid connection string for a SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public object ExecuteScalar(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create & open a SqlConnection, and dispose of it after we are done. using (SqlConnection cn = new SqlConnection(connectionString)) { cn.Open(); //call the overload that takes a connection in place of the connection string return ExecuteScalar(cn, commandType, commandText, commandParameters); } } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteScalar(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(conn, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public object ExecuteScalar(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //SqlNotice st = new SqlNotice(); //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //execute the command & return the results object retval = cmd.ExecuteScalar(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //st.End("ExecuteScalar_1", commandText); return retval; } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteScalar(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a 1x1 resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// int orderCount = (int)ExecuteScalar(trans, CommandType.StoredProcedure, "GetOrderCount", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an object containing the value in the 1x1 resultset generated by the command</returns> public object ExecuteScalar(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //SqlNotice st = new SqlNotice(); //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //execute the command & return the results object retval = cmd.ExecuteScalar(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); //st.End("ExecuteScalar_2", commandText); return retval; } /// <summary> /// 返回ExecuteScalar 只是传入一条sql语句 /// </summary> /// <param name="commandText">sql语句</param> /// <returns></returns> public object ExecuteScalar(string commandText) { return ExecuteScalar(connStr, CommandType.Text, commandText, (SqlParameter[])null); } /// <summary> /// 返回ExecuteScalar 可传入参数 /// </summary> /// <param name="commandText">sql语句</param> /// <param name="commandParameters">参数</param> /// <returns></returns> public object ExecuteScalar(string commandText, params SqlParameter[] commandParameters) { return ExecuteScalar(connStr, CommandType.Text, commandText, commandParameters); } /// <summary> /// 返回ExecuteScalar 可传入Hashtable参数 /// </summary> /// <param name="commandText">sql语句</param> /// <param name="commandParameters">Hashtable参数</param> /// <returns></returns> public object ExecuteScalar(string commandText, Hashtable commandParameters) { return ExecuteScalar(connStr, CommandType.Text, commandText, MakeAllParam(commandParameters)); } /// <summary> /// 返回ExecuteScalar 可传入事务、Hashtable参数 /// </summary> /// <param name="transaction">事务</param> /// <param name="commandText">sql语句</param> /// <param name="commandParameters">Hashtable参数</param> /// <returns></returns> public object ExecuteScalar(SqlTransaction transaction, string commandText, Hashtable commandParameters) { return ExecuteScalar(transaction, CommandType.Text, commandText, MakeAllParam(commandParameters)); } /// <summary> /// 返回ExecuteScalar 可传入事务、参数 /// </summary> /// <param name="transaction">事务</param> /// <param name="commandText">sql语句</param> /// <param name="commandParameters">参数</param> /// <returns></returns> public object ExecuteScalar(SqlTransaction transaction, string commandText, params SqlParameter[] commandParameters) { return ExecuteScalar(transaction, CommandType.Text, commandText, commandParameters); } /// <summary> /// 执行一个简单的Sqlcommand 没有返回结果 /// </summary> /// 例如: /// int result = ExecuteScalar(myTran,"delete from test where 1>2 "); 返回 result =0 /// <param name="transaction">事务名称</param> /// <param name="commandText">只能是sql语句</param> /// <returns>受影响的行数</returns> public object ExecuteScalar(SqlTransaction transaction, string commandText) { return ExecuteScalar(transaction, CommandType.Text, commandText, (SqlParameter[])null); } #endregion ExecuteScalar #region ExecuteXmlReader /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlConnection. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteXmlReader(connection, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlConnection /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(conn, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="connection">a valid SqlConnection</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public XmlReader ExecuteXmlReader(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters); //create the DataAdapter & DataSet XmlReader retval = cmd.ExecuteXmlReader(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } /// <summary> /// Execute a SqlCommand (that returns a resultset and takes no parameters) against the provided SqlTransaction. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders"); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText) { //pass through the call providing null for the set of SqlParameters return ExecuteXmlReader(transaction, commandType, commandText, (SqlParameter[])null); } /// <summary> /// Execute a SqlCommand (that returns a resultset) against the specified SqlTransaction /// using the provided parameters. /// </summary> /// <remarks> /// e.g.: /// XmlReader r = ExecuteXmlReader(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// </remarks> /// <param name="transaction">a valid SqlTransaction</param> /// <param name="commandType">the CommandType (stored procedure, text, etc.)</param> /// <param name="commandText">the stored procedure name or T-SQL command using "FOR XML AUTO"</param> /// <param name="commandParameters">an array of SqlParamters used to execute the command</param> /// <returns>an XmlReader containing the resultset generated by the command</returns> public XmlReader ExecuteXmlReader(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { //create a command and prepare it for execution SqlCommand cmd = new SqlCommand(); PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters); //create the DataAdapter & DataSet XmlReader retval = cmd.ExecuteXmlReader(); // detach the SqlParameters from the command object, so they can be used again. cmd.Parameters.Clear(); return retval; } #endregion ExecuteXmlReader /// <summary> /// 批量修改表数据 /// </summary> /// <param name="updateTable"></param> /// <returns></returns> public bool UpdateTable(DataTable updateTable,string tableName) { StringBuilder sql = new StringBuilder(); if (updateTable.Rows.Count > 0) { updateTable.TableName = tableName; updateTable.AcceptChanges(); foreach (System.Data.DataRow dr in updateTable.Rows) dr.SetModified(); // 初期化SqlCommand SqlCommand loCmd = new SqlCommand($"select * from {tableName} where 1=0", sqlConn, sqlTrans); SqlDataAdapter da = new SqlDataAdapter(loCmd); SqlCommandBuilder sqlCmdBuilder = new SqlCommandBuilder(da); da.AcceptChangesDuringUpdate = false; SqlCommand updatecmd = new SqlCommand(" UPDATE OrderSN with(rowlock,updlock) SET ordersn_modifytime=GETDATE(),Flag='1' where OrderSN_ID= @OrderSN_ID ", sqlConn, sqlTrans); updatecmd.UpdatedRowSource = UpdateRowSource.None; da.UpdateCommand = updatecmd; da.UpdateCommand.Parameters.Add("@OrderSN_ID", SqlDbType.NVarChar, 64, "OrderSN_ID"); da.UpdateBatchSize = updateTable.Rows.Count; da.Update(updateTable); updateTable.AcceptChanges(); } return true; } }