三层结构视频中的DBHelper.cs

using System; using System.Collections.Generic; using System.Text; using System.Configuration; using System.Data.Common; using System.Data; using System.Data.SqlClient;   namespace LyqDAL {     /// <summary>     /// 自定义访问通用类     /// </summary>     public class DBHelper     {         private static readonly string connectionString = ConfigurationManager.ConnectionStrings["lyqConnectionString"].ConnectionString.ToString();         private static readonly string providerName = "System.Data.SqlClient";         private static SqlConnection connection = new SqlConnection(connectionString);         /// <summary>         /// GetConnection 用于获取连接数据库的 connection 对象         /// </summary>         /// <returns></returns>         private static DbConnection GetConnection()         {             DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);             DbConnection connection = _factory.CreateConnection();             connection.ConnectionString = connectionString;             return connection;         }         /// <summary>         /// GetCommand 获取命令参数 command 对象         /// </summary>         /// <param name="commandText"></param>         /// <param name="commandType"></param>         /// <param name="connection"></param>         /// <returns></returns>         private static DbCommand GetCommand(string commandText, CommandType commandType, DbConnection connection)         {             DbCommand command = connection.CreateCommand();             command.CommandText = commandText;             command.CommandType = commandType;             return command;           }         /// <summary>         /// 读取数据         /// </summary>         /// <param name="sql">sql语句</param>         /// <param name="param">参数列表</param>         /// <returns>DataTable</returns>         public static DataTable GetDataTable(string sql, params SqlParameter[] param)         {             DataTable dt = new DataTable();             using (SqlConnection conn = new SqlConnection(connectionString))             {                 SqlDataAdapter da = new SqlDataAdapter(sql, conn);                 if (param != null)                     da.SelectCommand.Parameters.AddRange(param);                 da.Fill(dt);             }             return dt;         }         /// <summary>         /// GetCommand 方法重载         /// </summary>         /// <param name="commandText">sql语句</param>         /// <param name="connection"></param>         /// <returns></returns>         private static DbCommand GetCommand(string commandText, DbConnection connection)         {             DbCommand command = connection.CreateCommand();             command.CommandText = commandText;             command.CommandType = CommandType.Text;             return command;         }         public DbCommand GetSqlStringCommond(string sqlQuery)         {             DbCommand dbCommand = connection.CreateCommand();             dbCommand.CommandText = sqlQuery;             dbCommand.CommandType = CommandType.Text;             return dbCommand;         }         /// <summary>         /// GetParameter 用于为命令设置参数         /// </summary>         /// <param name="paramName"></param>         /// <param name="paramValue"></param>         /// <param name="command"></param>         /// <returns></returns>         private static DbParameter GetParameter(string paramName, object paramValue, DbCommand command)         {             DbParameter parameter = command.CreateParameter();             parameter.ParameterName = paramName;             parameter.Value = paramValue;             return parameter;         }         /// <summary>         /// 执行无参的存储过程         /// </summary>         /// <param name="sqlCommand">存储过程名</param>         /// <returns></returns>         public static int ExecuteNonQueryProc(string sqlCommand)         {             int result = 0;             using (DbConnection connection = GetConnection())             {                 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);                 connection.Open();                 result = command.ExecuteNonQuery();                 command.Parameters.Clear();             }             return result;         }         /// <summary>         /// 执行无返回值有参数的存储过程         /// </summary>         /// <param name="sqlCommand">存储过程名</param>         /// <param name="parameters">参数</param>         /// <returns></returns>         public static int ExecuteNonQueryProc(string sqlCommand, Dictionary<string, object> parameters)         {             int result = 0;             using (DbConnection connection = GetConnection())             {                 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);                 foreach (KeyValuePair<string, object> p in parameters)                 {                     command.Parameters.Add(GetParameter(p.Key, p.Value, command));                 }                 connection.Open();                 result = command.ExecuteNonQuery();                 command.Parameters.Clear();             }             return result;         }          /// <summary>         /// 执行无返回值的sql语句         /// </summary>         /// <param name="sqlCommand"></param>         /// <param name="parameters"></param>         public static int ExecuteNonQuery(string sqlCommand)         {             int result = 0;             using (DbConnection connection = GetConnection())             {                 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);                 connection.Open();                 result = command.ExecuteNonQuery();                 command.Parameters.Clear();                 return result;             }         }         /// <summary>         /// 执行有参数的sql语句         /// </summary>         /// <param name="sqlCommand"></param>         /// <param name="para"></param>         /// <returns></returns>         public static int ExecuteNonQuery(string sqlCommand, Dictionary<string, object> para)         {             int result = 0;             using (DbConnection connection = GetConnection())             {                 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);                 foreach (KeyValuePair<string, object> p in para)                 {                     command.Parameters.Add(GetParameter(p.Key, p.Value, command));                 }                 connection.Open();                 result = command.ExecuteNonQuery();                 command.Parameters.Clear();                 return result;             }         }         /// <summary>         /// 执行有返回值无参数的存储过程         /// </summary>         /// <param name="cmdText"></param>         /// <returns></returns>         public static object ExecuteScalarProc(string cmdText)         {             using (DbConnection connection = GetConnection())             {                 DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);                 connection.Open();                 object val = command.ExecuteScalar();                 command.Parameters.Clear();                 return val;             }         }         /// <summary>         /// 执行有返回值的有参数的存储过程         /// </summary>         /// <param name="cmdText">存储过程名</param>         /// <param name="para">参数</param>         /// <returns></returns>         public static object ExecuteScalarProc(string cmdText, Dictionary<string, object> para)         {             using (DbConnection connection = GetConnection())             {                 DbCommand command = GetCommand(cmdText, CommandType.StoredProcedure, connection);                 foreach (KeyValuePair<string, object> p in para)                 {                     command.Parameters.Add(GetParameter(p.Key, p.Value, command));                 }                 connection.Open();                 object val = command.ExecuteScalar();                 command.Parameters.Clear();                 return val;             }         }         /// <summary>         /// 执行有返回值的sql语句         /// </summary>         /// <param name="cmdText"></param>         /// <returns></returns>         public static object ExecuteScalar(string cmdText)         {             using (DbConnection connection = GetConnection())             {                 DbCommand command = GetCommand(cmdText, CommandType.Text, connection);                 connection.Open();                 object val = command.ExecuteScalar();                 command.Parameters.Clear();                 return val;             }         }         /// <summary>         /// 执行有返回值有参数的sql语句         /// </summary>         /// <param name="cmdText"></param>         /// <param name="para"></param>         /// <returns></returns>         public static object ExecuteScalar(string cmdText, Dictionary<string, object> para)         {             using (DbConnection connection = GetConnection())             {                 DbCommand command = GetCommand(cmdText, CommandType.Text, connection);                 foreach (KeyValuePair<string, object> p in para)                 {                     command.Parameters.Add(GetParameter(p.Key, p.Value, command));                 }                 connection.Open();                 object val = command.ExecuteScalar();                 command.Parameters.Clear();                 return val;             }         }         /// <summary>         /// 执行无参数的存储过程,返回DbDataReader对象         /// </summary>         /// <param name="sqlCommand">存储过程名</param>         /// <returns></returns>         public static DbDataReader GetReaderProc(string sqlCommand)         {             try             {                 DbConnection connection = GetConnection();                 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);                 connection.Open();                 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);                 return reader;             }             catch (Exception ex)             {                 Console.Write("" + ex.Message);                 return null;             }         }         /// <summary>         /// 执行有参数的存储过程,返回DbDataReader对象         /// </summary>         /// <param name="sqlCommand"></param>         /// <param name="parameters"></param>         /// <returns></returns>         public static DbDataReader GetReaderProc(string sqlCommand, Dictionary<string, object> parameters)         {             try             {                 DbConnection connection = GetConnection();                 DbCommand command = GetCommand(sqlCommand, CommandType.StoredProcedure, connection);                 foreach (KeyValuePair<string, object> p in parameters)                 {                     command.Parameters.Add(GetParameter(p.Key, p.Value, command));                 }                 connection.Open();                 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);                 command.Parameters.Clear();                 return reader;             }             catch             {                 return null;             }         }         #region         /// <summary>         /// 执行无参数的sql语句,返回DbDataReader对象         /// </summary>         /// <param name="sqlCommand"></param>         /// <returns></returns>         public static DbDataReader GetReader(string sqlCommand)         {             try             {                 DbConnection connection = GetConnection();                 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);                 connection.Open();                 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);                 command.Parameters.Clear();                 return reader;             }             catch (Exception ex)             {                 Console.Write("" + ex.Message);                 return null;             }         }         #endregion         /// <summary>         /// 执行有参数的sql语句,返回DbDataReader对象         /// </summary>         /// <param name="sqlCommand"></param>         /// <param name="parameters"></param>         /// <returns></returns>         public static DbDataReader GetReader(string sqlCommand, Dictionary<string, object> parameters)         {             try             {                 DbConnection connection = GetConnection();                 DbCommand command = GetCommand(sqlCommand, CommandType.Text, connection);                 foreach (KeyValuePair<string, object> p in parameters)                 {                     command.Parameters.Add(GetParameter(p.Key, p.Value, command));                 }                 connection.Open();                 DbDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);                 command.Parameters.Clear();                 return reader;             }             catch (Exception ex)             {                 Console.Write("" + ex.Message);                 return null;             }         }                  /// <summary>         /// 返回DataTable对象         /// </summary>         /// <param name="safeSql"></param>         /// <returns></returns>         public static DataTable GetDataSet(string safeSql)         {             /*DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);             DbConnection connection = GetConnection();             connection.Open();             DbDataAdapter da = _factory.CreateDataAdapter();             da.SelectCommand = connection.CreateCommand();             da.SelectCommand.CommandText = safeSql;             DataTable dt = new DataTable();             da.Fill(dt);             return dt;*/             using (DbConnection connection = GetConnection())             {                 DbProviderFactory _factory = DbProviderFactories.GetFactory(providerName);                 DbCommand command = GetCommand(safeSql, CommandType.Text, connection);                 connection.Open();                 DbDataAdapter da = _factory.CreateDataAdapter();                 da.SelectCommand = command;                 DataTable datatable = new DataTable();                 da.Fill(datatable);                 return datatable;             }         }     } }