.NetCore中Dapper读写分离封装

1.先建一个DbConnectionDescriptorProvider.cs 提供连接

 /// <summary>
    /// Db连接提供
    /// </summary>
    public class DbConnectionDescriptorProvider
    {
        //读写库连接字符串(主库）
        static string readWriteConnection = "server=172.16.2.84;userid=root;password=123456;database=test_db;";
        /// <summary>
        /// 从库连接字符串
        /// </summary>
        static string readOnlyConnection = "server=172.16.2.84;userid=root;password=123456;database=test_db;";
        /// <summary>
        /// 获取对DB进行操作的类
        /// </summary>
        public static DbConnectionDescriptor DbConnectionDescriptor { get; } = getDbConnectionDescriptor();

        /// <summary>
        /// 从配置中获取数据库链接
        /// </summary>
        private static DbConnectionDescriptor getDbConnectionDescriptor()
        {
            return new DbConnectionDescriptor(readWriteConnection);
        }
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        /// <param name="dbType"></param>
        /// <returns></returns>
        public static DbConnectionDescriptor DbConnection(DbReadWriteEnum dbType= DbReadWriteEnum.Master)
        {
            switch(dbType)
            {
                case DbReadWriteEnum.Master:
                    return new DbConnectionDescriptor(readWriteConnection);
                case DbReadWriteEnum.Slave:
                    return new DbConnectionDescriptor(readOnlyConnection);
                default:
                    return new DbConnectionDescriptor(readWriteConnection);
            }
        }
    }
    public enum DbReadWriteEnum
    {
        /// <summary>
        /// 主
        /// </summary>
        Master = 1,
        /// <summary>
        /// 从
        /// </summary>
        Slave = 2
    }

2.再建一个DbConnectionDescriptor.cs保存当前连接的具体库

 /// <summary>
    /// 连接字符串连接对像
    /// </summary>
    public class DbConnectionDescriptor
    {
        /// <summary>
        /// 初始化类的新实例
        /// </summary>
        /// <param name="databaseType"></param>
        /// <param name="connectionString"></param>
        public DbConnectionDescriptor(string connectionString)
        {
            this.ConnectionString = connectionString;
        }
        /// <summary>
        /// 获取连接字符串
        /// </summary>
        public string ConnectionString { get; }

        /// <summary>
        /// 获取连接字符串
        /// </summary>
        /// <returns></returns>
        public IDbConnection GetConnection()
        {
            //Mysql
            var conn = new MySql.Data.MySqlClient.MySqlConnection(this.ConnectionString);
            //pgsql
            // var conn = new Npgsql.NpgsqlConnection(this.ConnectionString);
            //sqlserver
            // var conn = new Microsoft.Data.SqlClient.SqlConnection(this.ConnectionString);
            return conn;
        }
    }

3.再建一个DbConnectionDescriptorDapperExtensions.cs 扩展具体数据库操作封装

 public static class DbConnectionDescriptorDapperExtensions
    {
        public static IDbConnection GetConn(this DbConnectionDescriptor connectionDescriptor)
        {
            return connectionDescriptor.GetConnection();
        }

        /// <summary>
        /// 获取事务
        /// </summary>
        /// <returns></returns>
        public static IDbTransaction CreateTranscation(IDbConnection conn)
        {
            if (conn.State == ConnectionState.Closed)
            {
                conn.Open();
            }
            return conn.BeginTransaction();
        }



        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static bool ExecuteNonQuerySql(this DbConnectionDescriptor connectionDescriptor, string sql, object param = null, IDbTransaction transaction = null, IDbConnection conn = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            Func<DbConnectionDescriptor, bool> execFunc = (connDescriptor) =>
            {
                using (var conn = connDescriptor.GetConnection())
                {
                    return conn.Execute(sql, param, transaction, commandTimeout, commandType) >= 0;
                }
            };

            return ExecSql(connectionDescriptor, execFunc, sql, param);
        }

        /// <summary>
        /// 执行返回首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static T ExecuteScalarSql<T>(this DbConnectionDescriptor connectionDescriptor, string sql, object param = null, IDbTransaction transaction = null, IDbConnection conn = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            Func<DbConnectionDescriptor, T> execFunc = (connDescriptor) =>
            {
                using (var conn = connDescriptor.GetConnection())
                {
                    return conn.ExecuteScalar<T>(sql, param, transaction, commandTimeout, commandType);
                }
            };

            return ExecSql(connectionDescriptor, execFunc, sql, param, transaction);
        }


        /// <summary>
        /// 查询单条
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static T QueryModelSql<T>(this DbConnectionDescriptor connectionDescriptor, string sql, object param = null, IDbTransaction transaction = null, IDbConnection conn = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            Func<DbConnectionDescriptor, T> execFunc = (connDescriptor) =>
            {
                using (var conn = connDescriptor.GetConnection())
                {
                    return conn.Query<T>(sql, param, transaction, true, commandTimeout, commandType).FirstOrDefault<T>();
                }
            };

            return ExecSql(connectionDescriptor, execFunc, sql, param);
        }

        /// <summary>
        /// 查询列表
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <param name="transaction"></param>
        /// <param name="commandTimeout"></param>
        /// <param name="commandType"></param>
        /// <returns></returns>
        public static IEnumerable<T> QueryListSql<T>(this DbConnectionDescriptor connectionDescriptor, string sql, object param = null, IDbTransaction transaction = null, IDbConnection conn = null, int? commandTimeout = default(int?), CommandType? commandType = default(CommandType?))
        {
            Func<DbConnectionDescriptor, IEnumerable<T>> execFunc = (connDescriptor) =>
            {
                using (var conn = connDescriptor.GetConnection())
                {
                    return conn.Query<T>(sql, param, transaction, true, commandTimeout, commandType);
                }
            };

            return ExecSql(connectionDescriptor, execFunc, sql, param, transaction);
        }

        /// <summary>
        /// 执行统一操作
        /// </summary>
        /// <typeparam name="TResult"></typeparam>
        /// <param name="func"></param>
        /// <param name="sql"></param>
        /// <param name="param"></param>
        /// <returns></returns>
        private static TResult ExecSql<TResult>(DbConnectionDescriptor connectionDescriptor, Func<DbConnectionDescriptor, TResult> func, string sql, object param = null, IDbTransaction transaction = null)
        {
            TResult result = default(TResult);
            try
            {
                System.Diagnostics.Stopwatch stopwatch = new System.Diagnostics.Stopwatch();
                stopwatch.Start();

                result = func.Invoke(connectionDescriptor);

                stopwatch.Stop();
                if (stopwatch.ElapsedMilliseconds > 1000)
                {
                    //执行耗时超过1秒->记录日志
                    //todo
                }
            }
            catch (Exception ex)
            {
                //错误日记记录 todo
                //事务回滚
                if (transaction != null)
                {
                    transaction.Rollback();
                }
                throw ex;
            }
            finally
            {

            }

            return result;
        }


    }

4.调用示例

 public class DapperTest
    {
        /// <summary>
        /// 增删改示例
        /// </summary>
        public void Add()
        {
            User user = new User()
            {
                user_name = "张三",
                mobile = "13600000000",
                email = "zhangsan@qq.com",
                create_time = DateTime.Now
            };
            string sqlstr = @"INSERT INTO user
(user_name, mobile, email, create_time)
VALUES(@user_name, @mobile, @email, @create_time); ";

            var response = DbConnectionDescriptorProvider.DbConnection().ExecuteNonQuerySql(sqlstr, user);
            Console.WriteLine($"插入结果:{response}");
        }

        /// <summary>
        /// 查询单条
        /// </summary>
        public void Query()
        {
            string sqlstr = "select * from user where id=@id";
            var param = new
            {
                id = 1
            };
            var result = DbConnectionDescriptorProvider.DbConnection(DbReadWriteEnum.Slave).QueryModelSql<User>(sqlstr, param);
        }
        /// <summary>
        /// 查询多条
        /// </summary>
        public void QueryList()
        {
            string sqlstr = "select * from user where user_name=@user_name";
            var param = new
            {
                user_name = "张三"
            };
            var result = DbConnectionDescriptorProvider.DbConnection().QueryListSql<User>(sqlstr, param);
        }
        /// <summary>
        /// 事务执行
        /// </summary>
        public void Transcation()
        {
            //创建事务
            var conn = DbConnectionDescriptorProvider.DbConnection().GetConn();

            var transtion = DbConnectionDescriptorDapperExtensions.CreateTranscation(conn);
            //订单
            Order order = new Order
            {
                order_no = DateTime.Now.ToString("yyyyMMddHHmmssfff"),
                order_status = "待支付",
                create_time = DateTime.Now,
                user_id = 1
            };
            //插入返回Id
            string sqlSaveOrder = @"INSERT INTO `order` (order_no, order_status, create_time, user_id) VALUES(@order_no, @order_status, @create_time, @user_id);SELECT LAST_INSERT_ID();";
            //保存订单
            int orderId = DbConnectionDescriptorProvider.DbConnection().ExecuteScalarSql<int>(sqlSaveOrder, order, transtion,conn);

            //订单明细
            List<OrderDetail> orderDetails = new List<OrderDetail>
            {
                new OrderDetail
                {
                    order_id = orderId,
                    goods_name="冰箱22款",
                    goods_no=DateTime.Now.ToString("yyyyMMddHHmmssfff"),
                    goods_price=1688,
                    create_time=DateTime.Now
                },
                new OrderDetail
                {
                    order_id=orderId,
                     goods_name="热水器c-102",
                    goods_no=DateTime.Now.ToString("yyyyMMddHHmmssfff"),
                    goods_price=1200,
                    create_time=DateTime.Now
                }
            };

            string sqlSaveOrderDetail = @"INSERT INTO order_detail
(goods_no, goods_name, goods_price, create_time, order_id)
VALUES(@goods_no, @goods_name, @goods_price, @create_time, @order_id);";
            //保存订单明细
            DbConnectionDescriptorProvider.DbConnection().ExecuteNonQuerySql(sqlSaveOrderDetail, orderDetails, transtion,conn);

            //事务提交
            transtion.Commit();
        }
    }