asp.net中SQLHelper类实现数据库操作
作者：袖梨
2022-06-25
自己的DBHelper类
显示代码
| 代码如下 | 复制代码 |
| public class DBHelper { /// /// 从Web.config配置文件中读取数据库连接 /// private static string connectionStrings = ConfigurationManager.ConnectionStrings["MyConnectionString"].ToString(); public static string ConnectionStrings { get { return DBHelper.connectionStrings; } } /// /// 打开数据库连接 /// /// /// /// /// private static SqlCommand GetCommand(string strSql, CommandType cmdType, SqlParameter[] parameters) { SqlCommand cmd = new SqlCommand(); cmd.Connection = new SqlConnection(connectionStrings);//连接配置文件 try { cmd.CommandText = strSql;//传入SQL语句或存储过程 cmd.CommandType = cmdType;// if (parameters != null) { foreach (SqlParameter p in parameters) { cmd.Parameters.Add(p); } } } finally { cmd.Connection.Open();//打开连接 } return cmd; } /// /// 返回受影响的行数 /// /// /// /// /// public static int ExecuteQuery(string strSql, CommandType cmdType, params SqlParameter[] param) { SqlCommand cmd = GetCommand(strSql, cmdType, param); int result = 0; try { result = cmd.ExecuteNonQuery(); } finally { cmd.Connection.Close(); } return result; } /// /// 返回一行一列 /// /// /// /// /// public static int ExecuteScalar(string strSql, CommandType cmdType, params SqlParameter[] param) { SqlCommand cmd = GetCommand(strSql, cmdType, param); int result = 0; try { result = (int)cmd.ExecuteScalar(); } finally { cmd.Connection.Close(); } return result; } /// /// 返回集合(dataSet) /// /// /// /// /// public static DataSet ExecuteDataSet(string strSql, CommandType cmdType, params SqlParameter[] parameters) { DataSet ds = new DataSet(); SqlCommand cmd = GetCommand(strSql, cmdType, parameters); try { SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); } finally { cmd.Connection.Close();//关闭连接 } return ds; } /// /// 返回某个对象(dataTable) /// /// /// /// /// public static DataTable ExecuteDataTable(string strSql, CommandType cmdType, params SqlParameter[] parameters) { SqlCommand cmd = GetCommand(strSql, cmdType, parameters); DataSet ds = new DataSet(); try { SqlDataAdapter adapter = new SqlDataAdapter(cmd); adapter.Fill(ds); } finally { cmd.Connection.Close(); } return ds.Tables[0]; } } |
|
SQLHelper类代码
| 代码如下 | 复制代码 |
| public class SQLHelper { /// /// SqlServer数据访问帮助类 /// public sealed class SqlHelper { #region 私有构造函数和方法 private SqlHelper() { } /// /// 将SqlParameter参数数组(参数值)分配给SqlCommand命令. /// 这个方法将给任何一个参数分配DBNull.Value; /// 该操作将阻止默认值的使用. /// /// 命令名 /// SqlParameters数组 private static void AttachParameters(SqlCommand command, SqlParameter[] commandParameters) { if (command == null) throw new ArgumentNullException("command"); if (commandParameters != null) { foreach (SqlParameter p in commandParameters) { if (p != null) { // 检查未分配值的输出参数,将其分配以DBNull.Value. if ((p.Direction == ParameterDirection.InputOutput || p.Direction == ParameterDirection.Input) && (p.Value == null)) { p.Value = DBNull.Value; } command.Parameters.Add(p); } } } } /// /// 将DataRow类型的列值分配到SqlParameter参数数组. /// /// 要分配值的SqlParameter参数数组 /// 将要分配给存储过程参数的DataRow private static void AssignParameterValues(SqlParameter[] commandParameters, DataRow dataRow) { if ((commandParameters == null) || (dataRow == null)) { return; } int i = 0; // 设置参数值 foreach (SqlParameter commandParameter in commandParameters) { // 创建参数名称,如果不存在,只抛出一个异常. if (commandParameter.ParameterName == null || commandParameter.ParameterName.Length <= 1) throw new Exception( string.Format("请提供参数{0}一个有效的名称{1}.", i, commandParameter.ParameterName)); // 从dataRow的表中获取为参数数组中数组名称的列的索引. // 如果存在和参数名称相同的列,则将列值赋给当前名称的参数. if (dataRow.Table.Columns.IndexOf(commandParameter.ParameterName.Substring(1)) != -1) commandParameter.Value = dataRow[commandParameter.ParameterName.Substring(1)]; i++; } } /// /// 将一个对象数组分配给SqlParameter参数数组. /// /// 要分配值的SqlParameter参数数组 /// 将要分配给存储过程参数的对象数组 private static void AssignParameterValues(SqlParameter[] commandParameters, object[] parameterValues) { if ((commandParameters == null) || (parameterValues == null)) { return; } // 确保对象数组个数与参数个数匹配,如果不匹配,抛出一个异常. if (commandParameters.Length != parameterValues.Length) { throw new ArgumentException("参数值个数与参数不匹配."); } // 给参数赋值 for (int i = 0, j = commandParameters.Length; i < j; i++) { // If the current array value derives from IDbDataParameter, then assign its Value property if (parameterValues[i] is IDbDataParameter) { IDbDataParameter paramInstance = (IDbDataParameter)parameterValues[i]; if (paramInstance.Value == null) { commandParameters[i].Value = DBNull.Value; } else { commandParameters[i].Value = paramInstance.Value; } } else if (parameterValues[i] == null) { commandParameters[i].Value = DBNull.Value; } else { commandParameters[i].Value = parameterValues[i]; } } } /// /// 预处理用户提供的命令,数据库连接/事务/命令类型/参数 /// /// 要处理的SqlCommand /// 数据库连接 /// 一个有效的事务或者是null值 /// 命令类型 (存储过程,命令文本, 其它.) /// 存储过程名或都T-SQL命令文本 /// 和命令相关联的SqlParameter参数数组,如果没有参数为'null' /// private static void PrepareCommand(SqlCommand command, SqlConnection connection, SqlTransaction transaction, CommandType commandType, string commandText, SqlParameter[] commandParameters, out bool mustCloseConnection) { if (command == null) throw new ArgumentNullException("command"); if (commandText == null || commandText.Length == 0) throw new ArgumentNullException("commandText"); // If the provided connection is not open, we will open it if (connection.State != ConnectionState.Open) { mustCloseConnection = true; connection.Open(); } else { mustCloseConnection = false; } // 给命令分配一个数据库连接. command.Connection = connection; // 设置命令文本(存储过程名或SQL语句) command.CommandText = commandText; // 分配事务 if (transaction != null) { if (transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); command.Transaction = transaction; } // 设置命令类型. command.CommandType = commandType; // 分配命令参数 if (commandParameters != null) { AttachParameters(command, commandParameters); } return; } #endregion 私有构造函数和方法结束 #region 数据库连接 /// /// 一个有效的数据库连接字符串 /// /// public static string GetConnSting() { return ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString; } /// /// 一个有效的数据库连接对象 /// /// public static SqlConnection GetConnection() { SqlConnection Connection = new SqlConnection(SqlHelper.GetConnSting()); return Connection; } #endregion #region ExecuteNonQuery命令 /// /// 执行指定连接字符串,类型的SqlCommand. /// /// /// 示例: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders"); /// /// 一个有效的数据库连接字符串 /// 命令类型 (存储过程,命令文本, 其它.) /// 存储过程名称或SQL语句 /// public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText) { return ExecuteNonQuery(connectionString, commandType, commandText, (SqlParameter[])null); } /// /// 执行指定连接字符串,类型的SqlCommand.如果没有提供参数,不返回结果. /// /// /// 示例: /// int result = ExecuteNonQuery(connString, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// /// 一个有效的数据库连接字符串 /// 命令类型 (存储过程,命令文本, 其它.) /// 存储过程名称或SQL语句 /// SqlParameter参数数组 /// public static int ExecuteNonQuery(string connectionString, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); using (SqlConnection connection = new SqlConnection(connectionString)) { connection.Open(); return ExecuteNonQuery(connection, commandType, commandText, commandParameters); } } /// /// 执行指定连接字符串的存储过程,将对象数组的值赋给存储过程参数, /// 此方法需要在参数缓存方法中探索参数并生成参数. /// /// /// 这个方法没有提供访问输出参数和返回值. /// 示例: /// int result = ExecuteNonQuery(connString, "PublishOrders", 24, 36); /// /// 一个有效的数据库连接字符串/param> /// 存储过程名称 /// 分配到存储过程输入参数的对象数组 /// public static int ExecuteNonQuery(string connectionString, string spName, params object[] parameterValues) { if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("connectionString"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // 如果存在参数值 if ((parameterValues != null) && (parameterValues.Length > 0)) { // 从探索存储过程参数(加载到缓存)并分配给存储过程参数数组. SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connectionString, spName); // 给存储过程参数赋值 AssignParameterValues(commandParameters, parameterValues); return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName, commandParameters); } else { // 没有参数情况下 return ExecuteNonQuery(connectionString, CommandType.StoredProcedure, spName); } } /// /// 执行指定数据库连接对象的命令 /// /// /// 示例: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders"); /// /// 一个有效的数据库连接对象 /// 命令类型(存储过程,命令文本或其它.) /// 存储过程名称或T-SQL语句 /// public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText) { return ExecuteNonQuery(connection, commandType, commandText, (SqlParameter[])null); } /// /// 执行指定数据库连接对象的命令 /// /// /// 示例: /// int result = ExecuteNonQuery(conn, CommandType.StoredProcedure, "PublishOrders", new SqlParameter("@prodid", 24)); /// /// 一个有效的数据库连接对象 /// 命令类型(存储过程,命令文本或其它.) /// T存储过程名称或T-SQL语句 /// SqlParamter参数数组 /// public static int ExecuteNonQuery(SqlConnection connection, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (connection == null) throw new ArgumentNullException("connection"); // 创建SqlCommand命令,并进行预处理 SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, connection, (SqlTransaction)null, commandType, commandText, commandParameters, out mustCloseConnection); // Finally, execute the command int retval = cmd.ExecuteNonQuery(); // 清除参数,以便再次使用. cmd.Parameters.Clear(); if (mustCloseConnection) connection.Close(); return retval; } /// /// 执行指定数据库连接对象的命令,将对象数组的值赋给存储过程参数. /// /// /// 此方法不提供访问存储过程输出参数和返回值 /// 示例: /// int result = ExecuteNonQuery(conn, "PublishOrders", 24, 36); /// /// 一个有效的数据库连接对象 /// 存储过程名 /// 分配给存储过程输入参数的对象数组 /// public static int ExecuteNonQuery(SqlConnection connection, string spName, params object[] parameterValues) { if (connection == null) throw new ArgumentNullException("connection"); if (spName == null || spName.Length == 0) throw new ArgumentNullException("spName"); // 如果有参数值 if ((parameterValues != null) && (parameterValues.Length > 0)) { // 从缓存中加载存储过程参数 SqlParameter[] commandParameters = SqlHelperParameterCache.GetSpParameterSet(connection, spName); // 给存储过程分配参数值 AssignParameterValues(commandParameters, parameterValues); return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName, commandParameters); } else { return ExecuteNonQuery(connection, CommandType.StoredProcedure, spName); } } /// /// 执行带事务的SqlCommand. /// /// /// 示例.: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "PublishOrders"); /// /// 一个有效的数据库连接对象 /// 命令类型(存储过程,命令文本或其它.) /// 存储过程名称或T-SQL语句 /// public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText) { return ExecuteNonQuery(transaction, commandType, commandText, (SqlParameter[])null); } /// /// 执行带事务的SqlCommand(指定参数). /// /// /// 示例: /// int result = ExecuteNonQuery(trans, CommandType.StoredProcedure, "GetOrders", new SqlParameter("@prodid", 24)); /// /// 一个有效的数据库连接对象 /// 命令类型(存储过程,命令文本或其它.) /// 存储过程名称或T-SQL语句 /// SqlParamter参数数组 /// public static int ExecuteNonQuery(SqlTransaction transaction, CommandType commandType, string commandText, params SqlParameter[] commandParameters) { if (transaction == null) throw new ArgumentNullException("transaction"); if (transaction != null && transaction.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "transaction"); // 预处理 SqlCommand cmd = new SqlCommand(); bool mustCloseConnection = false; PrepareCommand(cmd, transaction.Connection, transaction, commandType, commandText, commandParameters, out mustCloseConnection); // 执行 int retval = cmd.ExecuteNonQuery(); // 清除参数集,以便再次使用. cmd.Parameters.Clear(); return retval; } /// /// 执行带事务的SqlCommand(指定参数值). /// /// /// 此方法不提供访问存储过程输出参数和返回值 /// 示例: /// int result = ExecuteNonQuery(conn, trans, "PublishOrders", 24, 36); 相关文章精彩推荐 | |
