c#公用数据库链接类

csharp

浏览数:457

2019-1-8

AD:资源代下载服务


公用数据库链接类

using System;
using System.Data.SqlClient;
using System.Data.OracleClient;
using System.Data.Common;

/// <summary>
/// 数据库链接类型
/// </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);
    }
}