通用的数据库操作助手类

你还在为访问各种数据库而写SqlHelper、OracleHelper……这些数据库操作助手类吗，还在为写一个通用的访问数据库操作类，而定义一堆接和实现吗？

从DotNet2.0以后微软已经为我们写好了这些我直接封装一下就可以成为一个通用的数据库操作助手类。

那就是DbProviderFactory类。实例化它 DbProviderFactory m_factory = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings["Db"].ProviderName);
然后就用m_factory它去创建对象。

下面附上我从SqlHelper改装的一个通用的DbHelper类。

例举了该类部分方法，完整的类后面用下载。

using System;
using System.Data;
using System.Data.Common;
using System.Collections;
using System.Configuration;

namespace OA.Data
{
    /// <summary>
    /// 数据库访问类
    /// </summary>
    public class DbHelper
    {

    #region 私有变量
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        protected static string m_connectionstring = null ;

        /// <summary>
        /// DbProviderFactory实例
        /// </summary>
        private static DbProviderFactory m_factory = null;
 
        /// <summary>
        /// Parameters缓存哈希表
        /// </summary>
        private static Hashtable m_paramcache = Hashtable.Synchronized(new Hashtable());

        private static object lockHelper = new object();

    #endregion    
      
    #region 属性

        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string ConnectionString
        {
            get
            {
                if (m_connectionstring == null)
                {
                    m_connectionstring =ConfigurationManager.ConnectionStrings["LocalDb"].ConnectionString;
                }
                return m_connectionstring;
            }
            set 
            {
                m_connectionstring = value;
            }
        }

        /// <summary>
        /// DbFactory实例
        /// </summary>
        public static DbProviderFactory Factory
        {
            get
            {
                if (m_factory == null)
                {
                    m_factory = DbProviderFactories.GetFactory(ConfigurationManager.ConnectionStrings["LocalDb"].ProviderName);
                }
                return m_factory;
            }
        }


    #endregion

    #region 私有方法
 
        /// <summary>
        /// 预处理用户提供的命令,数据库连接/事务/命令类型/参数
        /// </summary>
        /// <param name="cmd">要处理的DbCommand</param>
        /// <param name="connection">数据库连接</param>
        /// <param name="trans">一个有效的事务或者是null值</param>
        /// <param name="cmdType">命令类型 (存储过程,命令文本, 其它.)</param>
        /// <param name="cmdText">存储过程名或都SQL命令文本</param>
        /// <param name="dbParms">和命令相关联的DbParameter参数数组,如果没有参数为'null'</param>
        /// <param name="mustCloseConnection"><c>true</c> 如果连接是打开的,则为true,其它情况下为false.</param>
        private static void PrepareCommand(DbCommand cmd, DbConnection connection, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] dbParms, out bool mustCloseConnection)
        {
            if (cmd == null) throw new ArgumentNullException("DbCommand");
            if (cmdText == null || cmdText.Length == 0) throw new ArgumentNullException("CommandType");

            // If the provided connection is not open, we will open it
            if (connection.State != ConnectionState.Open)
            {
                mustCloseConnection = true;
                connection.Open();
            }
            else
            {
                mustCloseConnection = false;
            }

            // 给命令分配一个数据库连接.
            cmd.Connection = connection;

            // 设置命令文本(存储过程名或SQL语句)
            cmd.CommandText = cmdText;

            // 分配事务
            if (trans != null)
            {
                if (trans.Connection == null) throw new ArgumentException("The transaction was rollbacked or commited, please provide an open transaction.", "trans");
                cmd.Transaction = trans;
            }

            // 设置命令类型.
            cmd.CommandType = cmdType;

            // 分配命令参数
            if (dbParms != null)
            {
                AttachParameters(cmd, dbParms);
            }
            return;
        }

 

    #endregion 私有方法结束

    /// <summary>
        /// 获取连接对象
        /// </summary>
        /// <param name="key"></param>
        /// <returns></returns>
    public static DbConnection GetConn(string key)
        {
            DbConnection conn = Factory.CreateConnection();
            conn.ConnectionString = ConfigurationManager.ConnectionStrings[key].ConnectionString;
            conn.Open();
            return conn;
        }

    #region ExecuteNonQuery方法
        /// <summary>
        /// 执行SQL语句
        /// </summary>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">SQL语句</param>
        /// <param name="dbParms">参数</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(CommandType cmdType, string cmdText, params DbParameter[] dbParms)
        {
            if (ConnectionString == null || ConnectionString.Length == 0) throw new ArgumentNullException("ConnectionString");

            using (DbConnection connection =  Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();

                return ExecuteNonQuery(connection, cmdType, cmdText, dbParms);
            }
        }


 

        /// <summary>
        /// 执行指定数据库连接对象的命令
        /// </summary>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="cmdType">命令类型(存储过程,命令文本或其它.)</param>
        /// <param name="cmdText">T存储过程名称或SQL语句</param>
        /// <param name="dbParms">DbParamter参数数组</param>
        /// <returns>返回影响的行数</returns>
        public static int ExecuteNonQuery(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] dbParms)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            DbCommand cmd = Factory.CreateCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (DbTransaction)null, cmdType, cmdText, dbParms, out mustCloseConnection);

            int retval = cmd.ExecuteNonQuery();

            // 清除参数,以便再次使用.
            cmd.Parameters.Clear();
            if (mustCloseConnection)
                connection.Close();
            return retval;
        }

  
        /// <summary>
        /// 带事务执行一组命令
        /// </summary>
        /// <param name="cmds">命令组</param>
        /// <returns></returns>
        public static int ExecuteNonQuery(DbCommand[] cmds)
        {
            DbTransaction trans = null;
            int i = 0;
            try
            {
                using (DbConnection connection = Factory.CreateConnection())
                {
                    connection.ConnectionString = ConnectionString;
                    connection.Open();

                    trans = connection.BeginTransaction();

                    foreach (DbCommand cmd in cmds)
                    {
                        cmd.Transaction = trans;
                        i = cmd.ExecuteNonQuery();
                    }
                    trans.Commit();
                    return i;
                }
            }
            catch (DbException err)
            {
                trans.Rollback();
                throw err;
            }
        }

    #endregion ExecuteNonQuery方法结束

    #region GetTable方法
    

        /// <summary>
        /// 获取DataTable.
        /// </summary>
        /// <param name="cmdType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名称或SQL语句</param>
        /// <param name="dbParms">DbParamter参数数组</param>
        /// <returns>返回一个包含结果集的DataTable</returns>
        public static DataTable GetTable(CommandType cmdType, string cmdText, params DbParameter[] dbParms)
        {
            if (ConnectionString == null || ConnectionString.Length == 0) throw new ArgumentNullException("ConnectionString");

            using (DbConnection connection = Factory.CreateConnection())
            {
                connection.ConnectionString = ConnectionString;
                connection.Open();

                // 调用指定数据库连接字符串重载方法.
                return GetTable(connection, cmdType, cmdText, dbParms);
            }
        }

 
        /// <summary>
        /// 指定数据库连接对象获取DataTable.
        /// </summary>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="cmdType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名或SQL语句</param>
        /// <param name="dbParms">DbParamter参数数组</param>
        /// <returns>返回一个包含结果集的DataTable</returns>
        public static DataTable GetTable(DbConnection connection, CommandType cmdType, string cmdText, params DbParameter[] dbParms)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            // 预处理
            DbCommand cmd = Factory.CreateCommand();
            bool mustCloseConnection = false;
            PrepareCommand(cmd, connection, (DbTransaction)null, cmdType, cmdText, dbParms, out mustCloseConnection);

            // 创建DbDataAdapter和DataTable.
            using (DbDataAdapter da = Factory.CreateDataAdapter())
            {
                da.SelectCommand = cmd;
                DataTable dt = new DataTable();

                // 填充DataTable.
                da.Fill(dt); 

                cmd.Parameters.Clear(); 

                if (mustCloseConnection)
                    connection.Close();

                return dt;
            }
        }

  

        #endregion GetTable


    #region ExecuteReader 数据阅读器

        /// <summary>
        /// 枚举,标识数据库连接是由BaseDbHelper提供还是由调用者提供
        /// </summary>
        private enum DbConnectionOwnership
        {
            /// <summary>由BaseDbHelper提供连接</summary>
            Internal,
            /// <summary>由调用者提供连接</summary>
            External
        }


        /// <summary>
        /// 获取DataReader.
        /// </summary>
        /// <param name="cmdType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名称或SQL语句</param>
        /// <param name="dbParms">DbParamter参数数组</param>
        /// <returns>返回一个包含结果集的DataReader</returns>
        public static DbDataReader ExecuteReader(CommandType cmdType, string cmdText, params DbParameter[] dbParms)
        {
            if (ConnectionString == null || ConnectionString.Length == 0) throw new ArgumentNullException("ConnectionString");
            DbConnection connection = null;
            try
            {
                connection = Factory.CreateConnection();
                connection.ConnectionString = ConnectionString;
                connection.Open();

                return ExecuteReader(connection, null, cmdType, cmdText, dbParms, DbConnectionOwnership.Internal);
            }
            catch (DbException err)
            {
                if (connection != null) connection.Close();
                throw err;
            }

        }
 
        /// <summary>
        /// 指定连接字符串获取DataTable
        /// </summary>
        /// <param name="connString">连接字符串</param>
        /// <param name="cmdType">命令类型</param>
        /// <param name="cmdText">存储过程名称或SQL语句</param>
        /// <param name="dbParms">DbParamter参数数组</param>
        /// <returns></returns>
        public static DbDataReader ExecuteReader(string connectionString,CommandType cmdType, string cmdText, params DbParameter[] dbParms)
        {
            if (connectionString == null || connectionString.Length == 0) throw new ArgumentNullException("ConnectionString");
            DbConnection connection = null;
            try
            {
                connection = Factory.CreateConnection();
                connection.ConnectionString = connectionString;
                connection.Open();

                return ExecuteReader(connection, null, cmdType, cmdText, dbParms, DbConnectionOwnership.Internal);
            }
            catch (DbException err)
            {
                // If we fail to return the SqlDatReader, we need to close the connection ourselves
                if (connection != null) connection.Close();
                throw err;
            }

        } 
 
 
 
 
 

        /// <summary>
        /// 执行指定数据库连接对象的数据DataReader
        /// </summary>
        /// <param name="connection">一个有效的数据库连接对象</param>
        /// <param name="trans">一个有效的事务,或者为 'null'</param>
        /// <param name="cmdType">命令类型 (存储过程,命令文本或其它)</param>
        /// <param name="cmdText">存储过程名或SQL语句</param>
        /// <param name="dbParms">DbParameters参数数组,如果没有参数则为'null'</param>
        /// <param name="connectionOwnership">标识数据库连接对象是由调用者提供还是由BaseDbHelper提供</param>
        /// <returns>返回包含结果集的DbDataReader</returns>
        private static DbDataReader ExecuteReader(DbConnection connection, DbTransaction trans, CommandType cmdType, string cmdText, DbParameter[] dbParms, DbConnectionOwnership connectionOwnership)
        {
            if (connection == null) throw new ArgumentNullException("connection");

            bool mustCloseConnection = false;
            // 创建命令
            DbCommand cmd = Factory.CreateCommand();
            try
            {
                PrepareCommand(cmd, connection, trans, cmdType, cmdText, dbParms, out mustCloseConnection);

                // 创建数据阅读器
                DbDataReader dataReader;

                if (connectionOwnership == DbConnectionOwnership.External)
                {
                    dataReader = cmd.ExecuteReader();
                }
                else
                {
                    dataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
 
                bool canClear = true;
                foreach (DbParameter dbParm in cmd.Parameters)
                {
                    if (dbParm.Direction != ParameterDirection.Input)
                        canClear = false;
                }

                if (canClear)
                { 
                    cmd.Parameters.Clear();
                }


                return dataReader;
            }
            catch
            {
                if (mustCloseConnection)
                    connection.Close();
                throw;
            }
        }
        /// <summary>
        /// 准备输入参数.
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="dbType">参数数据类型</param>
        /// <param name="size">参数大小</param>
        /// <param name="value">参数值</param>
        /// <returns>输入参数</returns>
        public static DbParameter MakeInParam(string paramName, DbType dbType, int size, object inValue)
        {
            return MakeParam(paramName, dbType, size, ParameterDirection.Input, inValue);
        }
        /// <summary>
        /// 准备输入参数.
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="dbType">参数数据类型</param>
        /// <param name="value">参数值</param>
        /// <returns>输入参数</returns>
        public static DbParameter MakeInParam(string paramName, DbType dbType, object inValue)
        {
            return MakeParam(paramName, dbType, 0, ParameterDirection.Input, inValue);
        }

        /// <summary>
        /// 准备输出参数.
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="dbType">参数数据类型</param>
        /// <param name="size">参数大小</param>        
        /// <returns>输出参数</returns>
        public static DbParameter MakeOutParam(string paramName, DbType dbType, int size)
        {
            return MakeParam(paramName, dbType, size, ParameterDirection.Output, null);
        }


        /// <summary>
        /// 准备存储过程的参数.
        /// </summary>
        /// <param name="paramName">参数名称</param>
        /// <param name="dbType">参数数据类型</param>
        /// <param name="size">参数大小</param>        
        /// <param name="direction">参数方向</param>
        /// <param name="inValue">参数值</param>
        /// <returns>输入或输出参数</returns>
        public static DbParameter MakeParam(string paramName, DbType dbType, int size, ParameterDirection direction, object inValue)
        {
            DbParameter param = Factory.CreateParameter();
             param.ParameterName = paramName;

             if (size > 0)
             {
                 param.DbType = dbType;
                 param.Size = size;
             }
             else
                 param.DbType = dbType;

            param.Direction = direction;


            if (!(direction == ParameterDirection.Output))
            {
                if (inValue == null)
                    param.Value = DBNull.Value;
                else
                    param.Value = inValue;
            }

            return param;
        }
    #endregion 生成参数结束

    }
 
}

细详信息请点击下载