一聚教程网:一个值得你收藏的教程网站

热门教程

asp.net中SQLHelper类实现数据库操作

时间:2022-06-25 06:02: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'
             /// true 如果连接是打开的,则为true,其它情况下为false.
             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语句
             /// 返回影响的行数/returns>
             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);

相关文章