一个通用的数据库访问组件(一)_.NET_编程开发_程序员俱乐部

   虽然现在有很多ORM ，但有时候我们可能只是需要简单的数据操作,于是写了一个数据库通用访问组件。其实这个最开始连接口都没有,到最后逐步完善.虽然是普通的数据访问,但是我们还是要来实现一些可插拔特性和工厂模式.

首先来定义接口

class="brush:csharp;collapse:true;;gutter:true;">using System;
using System.Collections.Generic;
using System.Data;
using System.Collections;
using System.Data.Common;
namespace FYJ.Data
{
    public enum DbHelperType : int
    {
        Other = 0,
        SqlServer = 1,
        Oracle = 2,
        OleDb = 3,
        MySql = 4,
        SqlLite = 5,
        Odbc = 6
    }

    /// <summary>
    /// 功能:数据接口
    /// 作者:fangyj
    /// 创建日期:2012-07-15
    /// 修改日期:2013-08-11
    /// </summary>
    public interface IDbHelper
    {
        #region 执行
        /// <summary>
        /// 执行一条sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        int ExecuteSql(string sql, params IDataParameter[] parms);

        /// <summary>
        /// 执行一条sql语句
        /// </summary>
        /// <param name="parms"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        int ExecuteSql(IEnumerable<IDataParameter> parms, string sql);

        /// <summary>
        /// 根据IDataParameter 自动生成sql语句并执行
        /// </summary>
        /// <param name="tableName">表明</param>
        /// <param name="pkName">主键名</param>
        /// <param name="iaAdd">是否插入语句，否则为修改</param>
        /// <param name="list"></param>
        /// <returns></returns>
        int ExecuteSql(string tableName, string pkName, bool iaAdd, IEnumerable<IDataParameter> parms);

        /// <summary>
        /// 执行存储过程 返回DataSet
        /// </summary>
        /// <param name="parms">存储过程参数</param>
        /// <param name="storedProcName">存储过程名</param>
        /// <returns></returns>
        DataSet RunProcedure(IEnumerable<IDataParameter> parms, string storedProcName);

        /// <summary>
        /// 执行存储过程 返回DataSet
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="parms">存储过程参数</param>
        /// <returns></returns>
        DataSet RunProcedure(string storedProcName, params IDataParameter[] parms);

        /// <summary>
        /// 执行存储过程  
        /// </summary>
        /// <param name="storedProcName">存储过程名</param>
        /// <param name="dic">以存储过程参数名为key 参数值为value</param>
        /// <returns></returns>
        DataSet RunProcedure(string storedProcName, Dictionary<String, object> dic);

        /// <summary>
        /// 执行存储过程 返回整数值 @RETURN_VALUE 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="dic"></param>
        /// <returns></returns>
        int ExecuteProcedure(string storedProcName, Dictionary<String, object> dic);

        /// <summary>
        /// 执行存储过程 返回整数值 @RETURN_VALUE 
        /// </summary>
        /// <param name="parms"></param>
        /// <param name="storedProcName"></param>
        /// <returns></returns>
        int ExecuteProcedure(IEnumerable<IDataParameter> parms, string storedProcName);

        /// <summary>
        /// 执行存储过程 返回整数值 @RETURN_VALUE 
        /// </summary>
        /// <param name="storedProcName"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        int ExecuteProcedure(string storedProcName, params IDataParameter[] parms);
        #endregion

        #region 查询
        /// <summary>
        /// 获取DataSet
        /// </summary>
        /// <param name="parms"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        DataSet GetDataSet(IEnumerable<IDataParameter> parms, string sql);

        /// <summary>
        /// 获取DataSet
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        DataSet GetDataSet(string sql, params IDataParameter[] parms);

        /// <summary>
        /// 获取查询的数据列表  IDataParameter参数前缀@ ：或者不加都会自动修正  currentPage设置为0表示不返回数据   currentPage为null则返回所有数据
        /// count传递null表示不返回总数   
        /// </summary>
        /// <param name="count">是否返回总记录条数 </param>
        /// <param name="tableName">表或视图名 </param>
        /// <param name="accessProperty">要访问的列</param>
        /// <param name="where">查询条件</param>
        /// <param name="orderProperty">排序</param>
        /// <param name="currentPage">当前页</param>
        /// <param name="pageSize">每页显示条数</param>
        /// <returns></returns>
        DataTable GetDataTable(ref long? count, string tableName, string order, string[] accessProperty = null, string where = null, int currentPage = 1, int pageSize = 20);

        /// <summary>
        /// 获取DataTable 该值为DataSet的第一张表
        /// </summary>
        /// <param name="parms"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        DataTable GetDataTable(IEnumerable<IDataParameter> parms, string sql);

        /// <summary>
        /// 获取DataTable 该值为DataSet的第一张表
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parms"></param>
        /// <returns></returns>
        DataTable GetDataTable(string sql, params IDataParameter[] parms);

        /// <summary>
        /// 获取前max行数据  如果max小于1 则返回所有行
        /// </summary>
        /// <param name="tableName">表名</param>
        /// <param name="max"></param>
        /// <returns></returns>
        DataTable GetDataTable(string tableName, long max);

        /// <summary>
        /// 获取第一行第一列object值
        /// </summary>
        /// <param name="parms"></param>
        /// <param name="sql"></param>
        /// <returns></returns>
        object GetObject(IEnumerable<IDataParameter> parms, string sql);
        object GetObject(string sql, params IDataParameter[] parms);
        string GetString(IEnumerable<IDataParameter> parms, string sql);
        string GetString(string sql, params IDataParameter[] parms);
        long GetLong(IEnumerable<IDataParameter> parms, string sql);
        long GetLong(string sql, params IDataParameter[] parms);
        double GetDouble(IEnumerable<IDataParameter> parms, string sql);
        double GetDouble(string sql, params IDataParameter[] parms);
        int GetInt(IEnumerable<IDataParameter> parms, string sql);
        int GetInt(string sql, params IDataParameter[] parms);
        bool GetBoolean(IEnumerable<IDataParameter> parms, string sql);
        bool GetBoolean(string sql, params IDataParameter[] parms);
        bool Exists(IEnumerable<IDataParameter> parms, string sql);
        bool Exists(string sql, params IDataParameter[] parms);
        /// <summary>
        /// 表是否存在
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        bool ExistsTable(string tableName);

        /// <summary>
        /// 获取当前数据库所有表
        /// </summary>
        /// <returns></returns>
        List<string> GetTables();
        #endregion

        /// <summary>
        /// 创建DbParameter对象
        /// </summary>
        /// <param name="parameterName"></param>
        /// <param name="parameterValue"></param>
        /// <param name="direction"></param>
        /// <returns></returns>
        DbParameter CreateParameter(string parameterName = null, object parameterValue = null, ParameterDirection? direction = null);

        #region  属性
        /// <summary>
        /// 获取DbProviderFactory对象
        /// </summary>
        /// <returns></returns>
        DbProviderFactory DbProviderFactoryInstance { get; set; }

        /// <summary>
        /// 获取连接对象
        /// </summary>
        /// <returns></returns>
        DbConnection DbConnectionInstance { get; set; }

        /// <summary>
        /// 获取数据类型枚举
        /// </summary>
        /// <returns></returns>
        DbHelperType DbHelperTypeEnum { get; }

        DbTransaction Tran { get; }
        #endregion
        /// <summary>
        /// 开始事务
        /// </summary>
        void BeginTran();

        /// <summary>
        /// 提交事务
        /// </summary>
        void Commit();

        /// <summary>
        /// 回滚
        /// </summary>
        void Rollback();

        /// <summary>
        /// 打开连接
        /// </summary>
        void OpenConnection();

        /// <summary>
        /// 关闭连接
        /// </summary>
        void CloseConnection();

        /// <summary>
        /// 测试数据库是否可以打开
        /// </summary>
        /// <returns></returns>
        bool TestCanConnectionOpen();
    }
}

 这个接口是不是有点多。。。

我们没有使用config文件里面的appSettings和connectionStrings来配置,因为需要扩展更多的属性.于是自己写section吧，当然也可以自己写xml，感觉写xml还简单些

以下是配置文件

  <configSections>
    <section name="DbHelperSettings" type="FYJ.Data.Config.DbHelperSection, FYJ.Data" />
  </configSections>
  <DbHelperSettings>
    <DbHelperSetting name="hotel" type="FYJ.Data.DbHelper,FYJ.Data" providerName="System.Data.SqlClient" connectionString="server=.;database=db_hotel;uid=xx;pwd=xx" isEncrypt="false" encryptType="FYJ.Data.DbEncrypt,FYJ.Data">
    </DbHelperSetting>
  </DbHelperSettings>
  <appSettings>

 可以看到有很多属性,name是唯一的id不能重复,type是FYJ.Data.IDbHelper接口的实现,isEncrypt表示是否加密连接字符串,encryptType是实现了FYJ.Data.IDbEncrypt的解密类，该接口就只有一个方法 string Decrypt(string text);返回解密后的字符串

下面我们来看工厂类

using System;
using System.Collections.Generic;
using System.Text;
using System.Reflection;
using System.Data.Common;
using System.Configuration;
using System.Text.RegularExpressions;
using System.IO;

namespace FYJ.Data
{
    /// <summary>
    /// 工厂类，无法继承该类
    /// </summary>
    public sealed class DbFactory
    {
        private static Dictionary<string, IDbHelper> dbs = new Dictionary<string, IDbHelper>();
        /// <summary>
        /// 根据配置节点主键名构造IDbHelper对象
        /// </summary>
        /// <param name="configName"></param>
        /// <returns></returns>
        public static IDbHelper CreateIDbHelper(string configName)
        {
            var items = (ConfigurationManager.GetSection("DbHelperSettings") as FYJ.Data.Config.DbHelperSection).Items;

            foreach (FYJ.Data.Config.DbHelperElement item in items)
            {
                if (item.Name == configName)
                {
                    Type t = Type.GetType(item.Type);
                    IDbHelper db = (IDbHelper)Activator.CreateInstance(t);

                    DbProviderFactory factory = null;
                    string connectionString = item.ConnectionString;
                    if (item.IsEncrypt == true)
                    {
                        IDbEncrypt encrypt = (IDbEncrypt)Activator.CreateInstance(Type.GetType(item.EncryptType));
                        connectionString = encrypt.Decrypt(connectionString);
                    }
                    if (item.ProviderName.StartsWith("MySql.Data", StringComparison.CurrentCultureIgnoreCase))
                    {
                        object obj = Assembly.Load("MySql.Data").CreateInstance("MySql.Data.MySqlClient.MySqlClientFactory");
                        factory = (DbProviderFactory)obj;
                    }
                    else if (item.ProviderName.StartsWith("System.Data.SQLite", StringComparison.CurrentCultureIgnoreCase))
                    {
                        object obj = Assembly.Load("System.Data.SQLite").CreateInstance("System.Data.SQLite.SQLiteFactory");
                        factory = (DbProviderFactory)obj;
                        String dbFileName = Regex.Match(connectionString, "data\\s*source\\s*=\\s*(.*)").Groups[1].Value;
                        if (!File.Exists(dbFileName))
                        {
                            connectionString = "data source=" + Path.Combine(AppDomain.CurrentDomain.BaseDirectory, dbFileName);
                        }
                    }
                    else
                    {
                        factory = DbProviderFactories.GetFactory(item.ProviderName);
                    }

                    db.DbProviderFactoryInstance = factory;
                    db.DbConnectionInstance = factory.CreateConnection();
                    db.DbConnectionInstance.ConnectionString = connectionString;
                    return db;
                }
            }

            throw new Exception("名为" + configName + "的数据配置不存在");
        }

        /// <summary>
        /// 根据配置节点主键名构造IDbHelper对象
        /// </summary>
        /// <param name="configName"></param>
        /// <returns></returns>
        public static IDbHelper GetIDbHelper(string configName)
        {
            if (!dbs.ContainsKey(configName))
            {
                dbs.Add(configName, CreateIDbHelper(configName));
            }

            return dbs[configName];
        }
    }
}

 CreateIDbHelper 方法是创建一个实现FYJ.Data.IDbHelper接口的实例，相应GetIDbHelper方法是得到一个静态的全局实例，并且会自动修正SQLite的连接字符串

由于代码贴得有点多,所以留在下面一篇

本文为个人原创,欢迎转载但请以超链接的形式注明出处哦。