前言

最近在开发一个人力资源的管理系统，老板说这个产品需要支持多数据库，因为到时不同用户公司可能会用不同的数据库，所以这边就把涉及到数据库连接的操作封装了一个基础连接库。通过C#提供的DataConnection相关接口 实现多数据库链接。

C#公用数据库连接代码如下：

using System;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.Common;
 
/// <summary>
/// 堆代码 duidaima.com
/// 数据库链接类型
/// </summary>
public enum DataBaseType
{
    /// <summary>
    /// 链接字符串：User ID=IFSAPP;Password=IFSAPP;Data Source=RACE;
    /// (DESCRIPTION = (ADDRESS_LIST= (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.226)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = BKYYCS)))
    /// </summary>
    Oracle,
    /// <summary>
    /// SqlServer 数据库库
    /// server=505-03;database=ttt;user=sa;pwd=123
    /// </summary>
    Sql,
    /// <summary>
    /// MySql 数据库
    /// </summary>
    MySql,
    /// <summary>
    /// Access 数据库
    /// </summary>
    Access
}
 
/* 使用C#已提供的DataConnection相关接口 实现多数据库链接
    采用单实例模式 防止数据库链接冗余 */
 
/// <summary>
/// 数据库确认类 不对外公开
/// </summary>
class ParentDbConn
{
    /// <summary>
    /// 获取 数据库平台接口对象
    /// </summary>
    /// <param name="dataBaseType">数据库类型</param>
    /// <param name="connString">链接字符串</param>
    /// <returns>数据库平台接口对象</returns>
    public static DbConnection GetConnection(DataBaseType dataBaseType, string connString)
    {
        DbConnection iConnection = null;
 
        try
        {
            switch (dataBaseType)
            {
                case DataBaseType.Sql:
                    iConnection = new SqlConnection(connString);
                    break;
                case DataBaseType.Oracle:
                    iConnection = new OracleConnection(connString);
                    break;
            }
 
            if (iConnection == null) { throw new Exception("[GetConnection]创建链接对象为NULL"); }
        }
        catch (Exception e)
        {
            //此处可以改成返回NULL对象 在调用时在对异常进行处理 只是看不到抛出的异常错误信息
            throw new Exception("[GetConnection]创建链接对象异常：" + e.Message.ToString());
        }
 
        return iConnection;
    }
}
 
using System;
using System.Data;
using System.Data.Common;
 
/* 数据库相关操作接口实现 -- 无实例生成限制 */
/// <summary>
/// 数据库平台对象
/// </summary>
public class PlatFormConn
{
    private DbConnection iConnection = null;
    private DbTransaction iTransaction = null;
    private DbCommand dbCommand = null;
    private IDataReader iReader = null;
    private DbDataAdapter dbDataAdapter = null;
 
    /// <summary>
    /// 禁用 无参构造方法
    /// </summary>
    private PlatFormConn(){}
 
    /// <summary>
    /// 初始链接的数据库实例
    /// </summary>
    /// <param name="dataBaseType">数据库类型</param>
    /// <param name="connString">链接字符串</param>
    public PlatFormConn(DataBaseType dataBaseType, string connString)
    {
        try
        {
            iConnection = ParentDbConn.GetConnection(dataBaseType, connString);
        }
        catch (Exception e)
        {
            throw new Exception(e.Message.ToString());
        }
    }
 
    /// <summary>
    /// 打开 数据库链接
    /// </summary>
    public void Open()
    {
        if (iConnection != null)
        {
            if (iConnection.State == ConnectionState.Closed)
            {
                try{iConnection.Open();}
                catch(Exception e){throw new Exception(e.Message.ToString());}
            }
        }
    }
    /// <summary>
    /// 关闭 数据库链接
    /// </summary>
    public void Close()
    {
        if (iConnection != null)
        {
            if (iConnection.State == ConnectionState.Open)
            {
                try{iConnection.Close();}
                catch(Exception e){throw new Exception(e.Message.ToString());}
            }
        }
    }
    /// <summary>
    /// 释放数据库链接资源
    /// </summary>
    public void Destroy()
    {
        try{iConnection.Dispose();}
        catch(Exception e){throw new Exception(e.Message.ToString());}
    }
 
    /// <summary>
    /// 开始事物
    /// </summary>
    public void BeginTrans()
    {
        if (iConnection != null && iConnection.State == ConnectionState.Open)
        {
            try{iTransaction = iConnection.BeginTransaction();}
            catch(Exception e){throw new Exception(e.Message.ToString());}
        }
    }
    /// <summary>
    /// 提交事物
    /// </summary>
    public void Commit()
    {
        if (iTransaction != null)
        {
            try{iTransaction.Commit();}
            catch(Exception e){throw new Exception(e.Message.ToString());}
        }
    }
    /// <summary>
    /// 回滚事物
    /// </summary>
    public void RollBack()
    {
        if (iTransaction != null)
        {
            try{iTransaction.Rollback();}
            catch(Exception e){throw new Exception(e.Message.ToString());}
        }
    }
 
    /// <summary>
    /// 执行 SQL查询语句 并返回DataTableL类型的对象
    /// </summary>
    /// <param name="commadString">SQL执行语句</param>
    /// <returns></returns>
    public DataTable ExcuteQuery(string commadString)
    {
        DataTable dt = new DataTable();
 
        try
        {
            dbCommand = iConnection.CreateCommand();
            dbCommand.CommandText = commadString;
            iReader = dbCommand.ExecuteReader();
 
            if (dbCommand == null)
            {
                throw new Exception("[ExcuteQuery]执行命令失败");
            }
             
            dt.Load(iReader);
        }
        catch(Exception e){throw new Exception(e.Message.ToString());}
        return dt;
    }
 
    /// <summary>
    /// 刷新DataSet的数据
    /// </summary>
    /// <param name="dataSet">DataSet对象</param>
    /// <param name="commadString">SQL语句</param>
    public void FillDataSet(DataSet dataSet, string commadString)
    {
        if (dataSet != null)
        {
            // 命令实例对象 无 从新生成
            if (dbCommand == null) { dbCommand = iConnection.CreateCommand(); }
 
            dbDataAdapter.SelectCommand = dbCommand;
        }
    }
}
 
/* 数据库相关操作接口实现 -- 单实例模式 */
/// <summary>
/// 单数据链接类
/// 限制 仅生成一次数据库链接 防止冗余
/// </summary>
public class SingleDbConn
{
    private static SingleDbConn singleDbConn = null;
    private PlatFormConn platFormConn = null;
 
    /// <summary>
    /// 单实例模式
    /// 禁用构造方法
    /// </summary>
    private SingleDbConn(){}
     
    /// <summary>
    /// 提供静态获取单数据连接实例的方法
    /// </summary>
    /// <param name="dataBaseType"></param>
    /// <param name="connString"></param>
    /// <returns></returns>
    public static SingleDbConn GetSingleDbConn(DataBaseType dataBaseType, string connString)
    {
        if (singleDbConn == null)
        {
            try
            {
                singleDbConn = new SingleDbConn();
                singleDbConn.platFormConn = new PlatFormConn(dataBaseType, connString);
            }
            catch(Exception e){throw new Exception(e.Message.ToString());}
        }
 
        return singleDbConn;
    }
 
    /// <summary>
    /// 打开 数据库链接
    /// </summary>
    public void Open()
    {
        platFormConn.Open();
    }
    /// <summary>
    /// 关闭 数据库链接
    /// </summary>
    public void Close()
    {
        platFormConn.Close();
    }
    /// <summary>
    /// 释放数据库链接资源
    /// </summary>
    public void Destroy()
    {
        platFormConn.Destroy();
    }
 
    /// <summary>
    /// 开始事物
    /// </summary>
    public void BeginTrans()
    {
        platFormConn.BeginTrans();
    }
    /// <summary>
    /// 提交事物
    /// </summary>
    public void Commit()
    {
        platFormConn.Commit();
    }
    /// <summary>
    /// 回滚事物
    /// </summary>
    public void RollBack()
    {
        platFormConn.RollBack();
    }
 
    /// <summary>
    /// 执行 SQL查询语句 并返回DataTableL类型的对象
    /// </summary>
    /// <param name="commadString">SQL执行语句</param>
    /// <returns></returns>
    public DataTable ExcuteQuery(string commadString)
    {
        return platFormConn.ExcuteQuery(commadString);
    }
}

数据库连接操作是开发管理信息系统最基本也是最重要的操作，把数据库的常用操作抽象成一个对象可以极大的提高代码的简洁性和可复用性。