使用C#打造通用的数据库连接类

众所周知vs.net提供了sqlserver、oledb、odbc等几种数据库连接驱动，现在比较常见的数据库连接类(如DBHelper等)只提供了其中一种连接方式，在我们开发的项目要更换数据库时或者在一个项目中要涉及多个不同类型的数据库时是非常麻烦的，通常要更改几乎所有的数据库连接相关代码。在经过几次痛苦之后，我决定打造一个通用的数据库连接类。

在查阅了相关资料之后我决定使用接口技术和.Net最新提供的通用数据库对象和配置文件来实现。关于使用接口技术，在查阅了MSDN文档之后我们得知.Net中各种数据库连接类( System.Data.SqlClient.SqlConnection、System.Data.OleDb.OleDbConnection 等)都是从System.Data.IDbConnection接口派生的，数据库连接类中的常用方法如打开连接Open()方法、得到Command对象的CreateCommand()方法、和数据库事务相关的BeginTransaction()方法、Commit()方法、Rollback()方法等在System.Data.IDbConnection接口中已经有定义。各种驱动类型的DataRader类也都是从System.Data.IDataReader接口派生的，DataRader的一些常用方法在接口中也都存在定义。

    考虑到开发的灵活性，我们将数据库连接信息写在配置文件中，内容格式如下：

  <connectionStrings>
    <add name="ConnectionString1" connectionString="Data Source=(local);Initial Catalog=db1;Persist Security Info=True;User ID=sa;Password=123" providerName="System.Data.SqlClient" />
    <add name="ConnectionString2" connectionString="Data Source=(local);Initial Catalog=db2;Persist Security Info=True;User ID=sa;Password=123" providerName="System.Data.SqlClient"/>
  </connectionStrings>

接下来再来考虑连接类的写法，考虑到一个项目中涉及到多个数据库情况，打开数据库连接的方法如下

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="connName">连接名</param>
        /// <returns></returns>
        static public System.Data.Common.DbConnection OpenConnect(string connName)
        {

            System.Data.Common.DbConnection Conn;
            //得到配置文件中的连接信息
            System.Configuration.ConnectionStringSettings s = System.Configuration.ConfigurationManager.ConnectionStrings[connName];
            //得到驱动类型¨ª
            System.Data.Common.DbProviderFactory f = System.Data.Common.DbProviderFactories.GetFactory(s.ProviderName);
            Conn = f.CreateConnection();
            //得到连接字符串
            Conn.ConnectionString = s.ConnectionString;
            Conn.Open();
            return Conn;
        }
//OpenConnect(string connName)

OpenConnect方法需要传递连接名参数，这个参数是的值要与配置文件中的值匹配。通过调用OpenConnect方法，我们在同一个项目中可以随时打开多个不同的连接
得到了连接之后，接下来要解决查询的问题，为了通用性我们将连接对象作为参数传递。
/// <summary>
        /// 执行查询返回DataReader
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">连接对象</param>
        /// <returns>成功时返回Reader对象，失败时返回null</returns>
        static public System.Data.IDataReader ExecuteQuery(string sql, System.Data.Common.DbConnection Conn)
        {
            System.Data.IDataReader reader = null;
            if (Conn == null)
            {
                return null;
            }
            try
            {
                if (Conn.State == System.Data.ConnectionState.Closed)
                {
                    Conn.Open();
                }
                System.Data.IDbCommand cmd = Conn.CreateCommand();
                cmd.CommandText = sql;
                reader = cmd.ExecuteReader();
                return reader;
            }
            catch (Exception ex)
            {
                ClsDB.ErrLog("ClsDB.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message);
                return null;
            }

        }

下面是增删改的方法：
/// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
    ///<param name="Conn">数据库连接对象</param>
        /// <returns>返回受影响行数</returns>
    static public int Execute(string sql, System.Data.Common.DbConnection Conn)
        {
            if (Conn == null)
            {
                ClsDB.ErrLog("ClsDB.Execute(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
                return 0;
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();

            cmd.CommandText = sql;
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ClsDB.ErrLog("ClsDB.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "/nsql=" + sql);
                return 0;
            }
}

为了方便向数据库添加二进制数据，再写一个带参数的增删改方法：
/// <summary>
        /// 执行Sql语句?
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">数据库连接对象</param>
        /// <param name="param">参数</param>
        /// <returns>返回受影响行数</returns>
        static public int Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param)
        {
            if (Conn == null)
            {
                ClsDB.ErrLog("ClsDB.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):连接对象为空!");
                return 0;
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();
            cmd.CommandText = sql;
            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.Add(param[i]);
            }
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ClsDB.ErrLog("ClsDB.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):" + ex.Message + "/nsql=" + sql);
                return 0;
            }
        }
到这里就差不多了，下面附上完整代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;

namespace Common
{
    /// <summary>
    /// 支持连接池的数据库访问类
    /// </summary>
    public class ClsDB
    {
        public ClsDB()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }

        /// <summary>
        /// 得到连接字符串
        /// </summary>
        /// <returns>连接字符串</returns>
        static private string getConnString(string key)
        {
            string connStr = System.Configuration.ConfigurationManager.ConnectionStrings[key].ToString();
            if (connStr == null || connStr == "")
            {
                ClsDB.ErrLog("ClsDB.getConnString(string key):["+key+"]所指定的连接类型为空");
            }
            return connStr;
        }
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <param name="connName">连接名</param>
        /// <returns></returns>
        static public System.Data.Common.DbConnection OpenConnect(string connName)
        {

            System.Data.Common.DbConnection Conn;
            //得到配置文件中的连接信息
            System.Configuration.ConnectionStringSettings s = System.Configuration.ConfigurationManager.ConnectionStrings[connName];
            //得到驱动类型
            System.Data.Common.DbProviderFactory f = System.Data.Common.DbProviderFactories.GetFactory(s.ProviderName);
            Conn = f.CreateConnection();
            //得到连接字符串
            Conn.ConnectionString = s.ConnectionString;
            Conn.Open();
            return Conn;
        }//OpenConnect(string connName)

        /// <summary>
        /// 执行查询返回DataTable
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <returns>成功返回DataTable,失败则返回 null</returns>
        static public System.Data.DataTable ExecuteQueryToDataTable(string sql, System.Data.Common.DbConnection Conn)
        {
            System.Data.DataTable dt = new System.Data.DataTable();
            System.Data.IDataReader reader = ExecuteQuery(sql, Conn);
            dt.Load(reader);
            return dt;
            
        }//ExecuteQueryToDataTable(string sql)

        /// <summary>
        /// 执行查询返回DataReader
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">连接对象</param>
        /// <returns>成功时返回Reader对象，失败时返回null</returns>
        static public System.Data.IDataReader ExecuteQuery(string sql, System.Data.Common.DbConnection Conn)
        {
            System.Data.IDataReader reader = null;
            if (Conn == null)
            {
                return null;
            }
            try
            {
                if (Conn.State == System.Data.ConnectionState.Closed)
                {
                    Conn.Open();
                }
                System.Data.IDbCommand cmd = Conn.CreateCommand();
                cmd.CommandText = sql;
                reader = cmd.ExecuteReader();
                return reader;
            }
            catch (Exception ex)
            {
                ClsDB.ErrLog("ClsDB.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message);
                return null;
            }

        }//ExecuteQuery(string sql)

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">数据库连接对象</param>
        /// <returns>返回受影响行数</returns>
        static public int Execute(string sql, System.Data.Common.DbConnection Conn)
        {
            if (Conn == null)
            {
                ClsDB.ErrLog("ClsDB.Execute(string sql, System.Data.Common.DbConnection Conn):连接对象为空!");
                return 0;
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();

            cmd.CommandText = sql;
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ClsDB.ErrLog("ClsDB.ExecuteQuery(string sql, System.Data.Common.DbConnection Conn):" + ex.Message + "/nsql=" + sql);
                return 0;
            }
        }//Execute(string sql)

        /// <summary>
        /// 执行Sql语句
        /// </summary>
        /// <param name="sql">Sql语句</param>
        /// <param name="Conn">数据库连接对象</param>
        /// <param name="param">参数</param>
        /// <returns>返回受影响行数</returns>
        static public int Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param)
        {
            if (Conn == null)
            {
                ClsDB.ErrLog("ClsDB.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):连接对象为空!");
                return 0;
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();
            cmd.CommandText = sql;
            for (int i = 0; i < param.Length; i++)
            {
                cmd.Parameters.Add(param[i]);
            }
            try
            {
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                ClsDB.ErrLog("ClsDB.Execute(string sql, System.Data.Common.DbConnection Conn, System.Data.Common.DbParameter[] param):" + ex.Message + "/nsql=" + sql);
                return 0;
            }
        }//Execute(string sql,System.Data.IDataParameter[] param)

        /// <summary>
        /// 执行一个事务
        /// </summary>
        /// <param name="sqls">Sql语句组</param>
        /// <returns>成功时返回true</returns>
        static public bool ExecuteTrans(string[] sqls, System.Data.Common.DbConnection Conn)
        {
            System.Data.IDbTransaction myTrans;
            if (Conn == null)
            {
                ClsDB.ErrLog("ClsDB.ExecuteTrans(string[] sqls):连接对象为空!");
                return false;
            }
            System.Data.IDbCommand cmd = Conn.CreateCommand();
            myTrans = Conn.BeginTransaction();
            cmd.Transaction = myTrans;
            try
            {
                foreach (string sql in sqls)
                {
                    if (sql != null)
                    {
                        cmd.CommandText = sql;
                        cmd.ExecuteNonQuery();
                    }
                }
                myTrans.Commit();
            }
            catch (Exception ex)
            {
                myTrans.Rollback();
                ClsDB.ErrLog("ClsDB.ExecuteTrans(string[] sqls):" + ex.Message);
                return false;
            }
            return true;
        }//Execute(string sql)

        /// <summary>
        /// 记录错误信息
        /// </summary>
        /// <param name="ErrInfo">错误信息</param>
        private static void ErrLog(string ErrInfo)
        {
            string fileName = System.Configuration.ConfigurationManager.AppSettings["ErrorLogFile"];
            string isWrite = System.Configuration.ConfigurationManager.AppSettings["ErrorLog"].Trim().ToLower();
            if (isWrite == "yes")
            {
                //将错误信息写入系统日志
                //System.Diagnostics.EventLog log = new System.Diagnostics.EventLog();
                //log.Source = "NewFrame";
                //log.WriteEntry(ErrInfo);
                //将错误信息写入记录文件
                StreamWriter sw = new StreamWriter(fileName, true);
                sw.WriteLine(System.DateTime.Now);
                sw.WriteLine(ErrInfo);
                sw.WriteLine();
                sw.Close();
            }
        }//ErrLog(string ErrInfo)

    }
}