使用Dapper.Contrib 开发.net core程序，兼容多种数据库

关于Dapper的介绍，我想很多人都对它有一定的了解，这个类似一个轻型的ORM框架是目前应用非常火的一个东西，据说各方面的性能都不错，而且可以支持多种数据库，在开始介绍这个文章之前，我花了不少功夫来学习了Dapper 的相关使用。Dapper.Contrib是对Dapper的进一步封装，使对象的基本增删改查等操作进一步简化，我做了一个案例使用Dapper.Contrib 开发.net core程序，测试它对多种数据库的处理。

1、Dapper.Contrib的使用

前面介绍过，Dapper.Contrib是对Dapper的进一步封装，使对象的基本增删改查等操作进一步简化。

它主要是通过特性映射的方式实现自定义类和数据库之间的关系处理，如下是实体类的定义信息。

    [Table("T_Customer")]
    public class CustomerInfo
    {
        [ExplicitKey]//非自增长的用此标识
        public virtual string ID { get; set; }

        public virtual string Name { get; set; }

        public virtual int Age { get; set; }

        public virtual string Creator { get; set; }

        public virtual DateTime CreateTime { get; set; }

    }

Dapper.Contrib的所有实体配置选项

• Table：指定实体对应地数据库表名，如果类名和数据库表名不同，需要设置（如案例所示）
• Key：指定此列为自动增长主键
• ExplicitKey：指定此列为非自动增长主键（例如guid，字符串列）
  
• Computed：计算属性，此列不作为更新
• Write：指定列是否可写

通过定义好实体类和数据库表的映射关系，就可以通过强类型处理相关的接口了，如下所示。

T Get<T>(id);
IEnumerable<T> GetAll<T>();
int Insert<T>(T obj);
int Insert<T>(Enumerable<T> list);
bool Update<T>(T obj);
bool Update<T>(Enumerable<T> list);
bool Delete<T>(T obj);
bool Delete<T>(Enumerable<T> list);
bool DeleteAll<T>();

这样通过映射指定表名或者字段信息后，就可以知道类和表之间的关系，可以封装对应的强类型处理接口了。

2、Dapper.Contrib 开发.net core程序

我们创建一个空白的.net core程序框架后，就在它的基础上做一些Dapper的数据库测试。

首先为了考虑多数据库的处理，我们需要创建一个配置文件，并可以动态配置不同的数据库，配置文件appSettings.json如下所示。

上面我配置了多种数据库的连接字符串，并且通过动态指定节点名称和数据库类型，来实现对项目指向不同数据库的访问。

例如我们准备需要让Dapper支持我们常见的数据库类型，如下定义数据库类型。

    /// <summary>
    /// 数据库类型定义
    /// </summary>
    public enum DatabaseType
    {
        SqlServer,  //SQLServer数据库
        MySql,      //Mysql数据库
        Npgsql,     //PostgreSQL数据库
        Oracle,     //Oracle数据库
        Sqlite,     //SQLite数据库
        DB2         //IBM DB2数据库
    }

对于不同的数据库信息，我们需要根据不同的配置连接字符串，并创建对应的数据库连接对象供Dapper使用，如对于SQLServer的数据库，那么创建的是SqlConnection对象，对于Mysql，创建的是MySqlConnection连接对象，对于PostgreSQL对应的是NpgsqlConnection，以此类推。而Dapper则通过对连接对象的扩展实现了多种数据请求。

对于多数据库的支持，我们需要统一解析配置内容appSetting.json的内容，并返回不同数据库的连接对象，如下是连接工厂的统一处理方式，通过 CreateConnection() 返回配置的连接对象。

    /// <summary>
    /// 数据库连接辅助类
    /// </summary>
    public class ConnectionFactory
    {
        /// <summary>
        /// 转换数据库类型
        /// </summary>
        /// <param name="databaseType">数据库类型</param>
        /// <returns></returns>
        private static DatabaseType GetDataBaseType(string databaseType)
        {
            DatabaseType returnValue = DatabaseType.SqlServer;
            foreach (DatabaseType dbType in Enum.GetValues(typeof(DatabaseType)))
            {
                if (dbType.ToString().Equals(databaseType, StringComparison.OrdinalIgnoreCase))
                {
                    returnValue = dbType;
                    break;
                }
            }
            return returnValue;
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        public static IDbConnection CreateConnection()
        {
            IDbConnection connection = null;

            //获取配置进行转换
            var type = AppConfig.GetConfig("ComponentDbType");
            var dbType = GetDataBaseType(type);

            //DefaultDatabase 根据这个配置项获取对应连接字符串
            var database = AppConfig.GetConfig("DefaultDatabase");
            if (string.IsNullOrEmpty(database))
            {
                database = "sqlserver";//默认配置
            }
            var strConn = AppConfig.Configuration.GetConnectionString(database);

            switch (dbType)
            {
                case DatabaseType.SqlServer:
                    connection = new System.Data.SqlClient.SqlConnection(strConn);
                    break;
                case DatabaseType.MySql:
                    connection = new MySql.Data.MySqlClient.MySqlConnection(strConn);
                    break;
                case DatabaseType.Npgsql:
                    connection = new Npgsql.NpgsqlConnection(strConn);
                    break;
                case DatabaseType.Sqlite:
                    connection = new SQLiteConnection(strConn);
                    break;
                case DatabaseType.Oracle:
                    connection = new Oracle.ManagedDataAccess.Client.OracleConnection(strConn);
                    //connection = new System.Data.OracleClient.OracleConnection(strConn);
                    break;
                case DatabaseType.DB2:
                    //connection = new System.Data.OleDb.OleDbConnection(strConn);
                    break;
            }

            return connection;
        }
    }

有了数据库对象工厂，我们的配置就可以动态化了。

下面我们来看看，获得这些连接对象后，如何通过Dapper.Contrib来获取对应的对象了，下面的类是常规的对数据库信息的处理，包括常规的增删改查等基础接口。

    /// <summary>
    /// 常规的数据访问层
    /// </summary>
    public class Customer
    {
        public IDbConnection Connection
        {
            get
            {
                var connection = ConnectionFactory.CreateConnection();
                connection.Open();
                return connection;
            }
        }

        public IEnumerable<CustomerInfo> GetAll()
        {
            using (IDbConnection dbConnection = Connection)
            {
                return dbConnection.GetAll<CustomerInfo>();
                //return dbConnection.Query<CustomerInfo>("SELECT * FROM T_Customer");
            }
        }


        public CustomerInfo FindByID(string id)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return dbConnection.Get<CustomerInfo>(id);
                //string query = "SELECT * FROM T_Customer WHERE ID = @Id";
                //return dbConnection.Query<CustomerInfo>(query, new { Id = id }).FirstOrDefault();
            }
        }

        public bool Insert(CustomerInfo info)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.Insert(info) > 0;
                result = true;
                //string query = "INSERT INTO T_Customer (ID, Name, Age, Creator, CreateTime)"
                //                + " VALUES(@ID, @Name, @Age, @Creator, @CreateTime)";
                //result = dbConnection.Execute(query, info) > 0;
            }
            return result;
        }
        
        public bool Update(CustomerInfo prod)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.Update(prod);
                //string query = "UPDATE T_Customer SET Name = @Name,"
                //               + " Age = @Age, Creator= @Creator, CreateTime=@CreateTime"
                //               + " WHERE ID = @ID";
                //result = dbConnection.Execute(query, prod) > 0;
            }
            return result;
        }
        public bool Delete(string id)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.Delete(new CustomerInfo { ID = id });
                //string query = "DELETE FROM T_Customer WHERE ID = @Id";
                //result = dbConnection.Execute(query, new { ID = id }) > 0;
            }
            return result;
        }
        public bool DeleteAll()
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.DeleteAll<CustomerInfo>();
                //string query = "DELETE FROM T_Customer WHERE ID = @Id";
                //result = dbConnection.Execute(query, new { ID = id }) > 0;
            }
            return result;
        }
    }

其中的备注部分的代码是等同于上面的执行代码的，是Dapper 的SQL版本的一种处理方式。

我们看到，对于Customer表来说，使用对象的接口处理，我们已经隔离了很多硬编码的SQL处理，不过我们还可以对它进行进一步的优化处理。

我们定义一个通用的BaseDAL来剥离常规的增删改查处理，并且把同步和异步的操作分来两个文件来管理，同步处理的基类如下代码所示。

    /// <summary>
    /// 数据库访问基类
    /// </summary>
    /// <typeparam name="T">实体类类型</typeparam>
    public partial class BaseDAL<T> where T : class
    {
        /// <summary>
        /// 对象的表名
        /// </summary>
        public string TableName { get; set; }

        /// <summary>
        /// 主键属性对象
        /// </summary>
        public PropertyInfo PrimaryKey { get; set; }

        public BaseDAL()
        {
            this.TableName = EntityHelper.GetTableName(typeof(T));
            this.PrimaryKey = EntityHelper.GetSingleKey<T>();
        }

        /// <summary>
        /// 数据库连接
        /// </summary>
        protected IDbConnection Connection
        {
            get
            {
                var connection = ConnectionFactory.CreateConnection();
                connection.Open();
                return connection;
            }
        }

        /// <summary>
        /// 返回数据库所有的对象集合
        /// </summary>
        /// <returns></returns>
        public IEnumerable<T> GetAll()
        {
            using (IDbConnection dbConnection = Connection)
            {
                return dbConnection.GetAll<T>();
            }
        }

        /// <summary>
        /// 查询数据库,返回指定ID的对象
        /// </summary>
        /// <param name="id">主键的值</param>
        /// <returns></returns>
        public T FindByID(object id)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return dbConnection.Get<T>(id);
            }
        }

        /// <summary>
        /// 插入指定对象到数据库中
        /// </summary>
        /// <param name="info">指定的对象</param>
        /// <returns></returns>
        public bool Insert(T info)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                dbConnection.Insert(info);
                result = true;
            }
            return result;
        }
        /// <summary>
        /// 插入指定对象集合到数据库中
        /// </summary>
        /// <param name="list">指定的对象集合</param>
        /// <returns></returns>
        public bool Insert(IEnumerable<T> list)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.Insert(list) > 0;
            }
            return result;
        }

        /// <summary>
        /// 更新对象属性到数据库中
        /// </summary>
        /// <param name="info">指定的对象</param>
        /// <returns></returns>
        public bool Update(T info)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.Update(info);
            }
            return result;
        }
        /// <summary>
        /// 更新指定对象集合到数据库中
        /// </summary>
        /// <param name="list">指定的对象集合</param>
        /// <returns></returns>
        public bool Update(IEnumerable<T> list)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.Update(list);
            }
            return result;
        }
        /// <summary>
        /// 从数据库中删除指定对象
        /// </summary>
        /// <param name="info">指定的对象</param>
        /// <returns></returns>
        public bool Delete(T info)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.Delete(info);
            }
            return result;
        }
        /// <summary>
        /// 从数据库中删除指定对象集合
        /// </summary>
        /// <param name="list">指定的对象集合</param>
        /// <returns></returns>
        public bool Delete(IEnumerable<T> list)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.Delete(list);
            }
            return result;
        }
        /// <summary>
        /// 根据指定对象的ID,从数据库中删除指定对象
        /// </summary>
        /// <param name="id">对象的ID</param>
        /// <returns></returns>
        public bool Delete(object id)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                string query = string.Format("DELETE FROM {0} WHERE {1} = @id", TableName, PrimaryKey.Name);
                var parameters = new DynamicParameters();
                parameters.Add("@id", id);

                result = dbConnection.Execute(query, parameters) > 0;
            }
            return result;
        }
        /// <summary>
        /// 从数据库中删除所有对象
        /// </summary>
        /// <returns></returns>
        public bool DeleteAll()
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                result = dbConnection.DeleteAll<T>();
            }
            return result;
        }

    }

异步类的代码如下所示。

    /// <summary>
    /// 数据库访问基类
    /// </summary>
    /// <typeparam name="T">实体类类型</typeparam>
    public partial class BaseDAL<T> where T : class
    {
        /// <summary>
        /// 返回数据库所有的对象集合
        /// </summary>
        /// <returns></returns>
        public virtual async Task<IEnumerable<T>> GetAllAsync()
        {
            using (IDbConnection dbConnection = Connection)
            {
                return await dbConnection.GetAllAsync<T>();
            }
        }

        /// <summary>
        /// 查询数据库,返回指定ID的对象
        /// </summary>
        /// <param name="id">主键的值</param>
        /// <returns></returns>
        public virtual async Task<T> FindByIDAsync(object id)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return await dbConnection.GetAsync<T>(id);
            }
        }
        /// <summary>
        /// 插入指定对象到数据库中
        /// </summary>
        /// <param name="info">指定的对象</param>
        /// <returns></returns>
        public virtual async Task<bool> InsertAsync(T info)
        {
            bool result = false;
            using (IDbConnection dbConnection = Connection)
            {
                await dbConnection.InsertAsync(info);
                result = true;
            }
            return await Task<bool>.FromResult(result);
        }

        /// <summary>
        /// 插入指定对象集合到数据库中
        /// </summary>
        /// <param name="list">指定的对象集合</param>
        /// <returns></returns>
        public virtual async Task<bool> InsertAsync(IEnumerable<T> list)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return await dbConnection.InsertAsync(list) > 0;
            }
        }
        /// <summary>
        /// 更新对象属性到数据库中
        /// </summary>
        /// <param name="info">指定的对象</param>
        /// <returns></returns>
        public virtual async Task<bool> UpdateAsync(T info)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return await dbConnection.UpdateAsync(info);
            }
        }

        /// <summary>
        /// 更新指定对象集合到数据库中
        /// </summary>
        /// <param name="list">指定的对象集合</param>
        /// <returns></returns>
        public virtual async Task<bool> UpdateAsync(IEnumerable<T> list)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return await dbConnection.UpdateAsync(list);
            }
        }

        /// <summary>
        /// 从数据库中删除指定对象
        /// </summary>
        /// <param name="info">指定的对象</param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(T info)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return await dbConnection.DeleteAsync(info);
            }
        }

        /// <summary>
        /// 从数据库中删除指定对象集合
        /// </summary>
        /// <param name="list">指定的对象集合</param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(IEnumerable<T> list)
        {
            using (IDbConnection dbConnection = Connection)
            {
                return await dbConnection.DeleteAsync(list);
            }
        }

        /// <summary>
        /// 根据指定对象的ID,从数据库中删除指定对象
        /// </summary>
        /// <param name="id">对象的ID</param>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAsync(object id)
        {
            using (IDbConnection dbConnection = Connection)
            {
                string query = string.Format("DELETE FROM {0} WHERE {1} = @id", TableName, PrimaryKey.Name);
                var parameters = new DynamicParameters();
                parameters.Add("@id", id);

                return await dbConnection.ExecuteAsync(query, parameters) > 0;
            }
        }

        /// <summary>
        /// 从数据库中删除所有对象
        /// </summary>
        /// <returns></returns>
        public virtual async Task<bool> DeleteAllAsync()
        {
            using (IDbConnection dbConnection = Connection)
            {
                return await dbConnection.DeleteAllAsync<T>();
            }
        }
    }

这样，我们如果需要增加一个如客户信息表的管理类，就很简单的继承基类就可以了，代码很少，但是增删改查接口一个也少不了。

    /// <summary>
    /// 继承基类对象管理
    /// </summary>
    public class CustomerDAL :BaseDAL<CustomerInfo>
    {
    }

为了测试一下数据访问层的处理接口，我创建了一个.net core的控制台程序进行测试，如下项目视图所示。

主要目的是确认数据处理的效果。

我们在Program.cs类里面增加相关的测试代码，为了简便和处理效果没有用UnitTest处理。

            //创建管理对象，并测试接口
            var customer = new CustomerDAL();
            var list = customer.GetAll();
            foreach (var item in list)
            {
                Console.WriteLine(item.ToJson());
                var info = customer.FindByID(item.ID);
                Console.WriteLine(info.ToJson());
                Console.WriteLine();
            }

            //插入记录
            var insertInfo = new CustomerInfo() { Name = "test", Age = 30, Creator = "test" };
            var insertList = new List<CustomerInfo>() { insertInfo };
            var flag = customer.Insert(insertList);
            Console.WriteLine("插入操作" + (flag ? "成功" : "失败"));

            Console.WriteLine("插入的新内容");
            insertInfo = customer.FindByID(insertInfo.ID);
            Console.WriteLine(insertInfo.ToJson());

            Console.WriteLine("更新内容");
            insertInfo.Name = "Test" + DateTime.Now.ToShortDateString();
            flag = customer.Update(insertInfo);
            Console.WriteLine("更新操作" + (flag ? "成功" : "失败"));

            Console.WriteLine("更新的新内容");
            insertInfo = customer.FindByID(insertInfo.ID);
            Console.WriteLine(insertInfo.ToJson());

            Console.WriteLine("删除内容");
            flag = customer.Delete(insertInfo.ID);
            Console.WriteLine("删除操作" + (flag ? "成功" : "失败"));


            Console.WriteLine("所有内容");
            list = customer.GetAll();
            foreach (var item in list)
            {
                Console.WriteLine(item.ToJson());
                Console.WriteLine();
            }

            Console.ReadLine();

测试Mysql、SQLite数据库同样没有问题

Mysql配置信息如下

处理的Mysql记录信息如下。 

SQLite配置信息如下

处理SQLite数据信息如下

 

 而在处理PostgreSQL的信息（配置节点npgsql里面）的时候，查询的主键好像和大小写有关系，导致插入记录出错。

而Oracle我采用的是Oracle.ManagedDataAccess.Core进行访问，由于我本地Oracle数据库侦听处理有点问题，因此没有测试成功，暂不予置评。

而对于数据库的支持问题，导致我重新审核一下是否采用Dapper.Contrib还是其他Dapper方式来构建数据库访问基类的问题，我需要兼容多种数据库的信息，并且能够尽可能的封装常规的增删改查等操作，其中目前的基类还没有加入更加复杂的查询操作，分页操作等功能，在解决这些困惑问题，才会继续考虑把底层支持的接口全部完善。