三层结构视频中的DBHelper.cs

 lyqConnectionString  要换

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; 
            } 
        } 
    } 
}