让Dapper读写分离

DotNetCore实战

共 11366字,需浏览 23分钟

 ·

2021-05-26 01:18


在上一篇说了封闭Dapper扩展方法为一个接口来支持Mock,接下来看看如何实现读写分离。

其实定义两个接口,一个用来实现读,一个用来实现写。在读的接口里只有Query的方法,在写的接口里实现Query和Execute全量(通读写的库也是支持读的,有的场景还是必须在写库里读,因为写库往读库中同步时是有时差的)。

IDapperPlusRead.cs

    /// <summary>    /// DapperPlus读接口    /// </summary>    public interface IDapperPlusRead    {        /// <summary>        /// 连接        /// </summary>        public IDbConnection Connection        {            get;        }        /// <summary>        /// Executes a single-row query, returning the data typed as type.        /// </summary>        /// <param name="type">The type to return.</param>        /// <param name="sql">The SQL to execute for the query.</param>        /// <param name="param">The parameters to pass, if any.</param>        /// <param name="transaction">The transaction to use, if any.</param>        /// <param name="buffered">Whether to buffer results in memory.</param>        /// <param name="commandTimeout">The command timeout (in seconds).</param>        /// <param name="commandType">The type of command to execute.</param>        /// <returns>A sequence of data of the supplied type; if a basic type(int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is created per row, and a direct column-name===member-name mapping is assumed(case insensitive).        /// 异常:T:System.ArgumentNullException:type is null.        /// </returns>        IEnumerable<object> Query(Type type, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null);        //这时省略n个方法     }

IDapperPlusWrite.cs

    /// <summary>    /// 写Dapper接口    /// </summary>    public interface IDapperPlusWrite    {        /// <summary>        /// 连接        /// </summary>        public IDbConnection Connection        {            get;        }        /// <summary>        /// Execute parameterized SQL.        /// </summary>        /// <param name="sql">The SQL to execute for this query.</param>        /// <param name="param">The parameters to use for this query.</param>        /// <param name="transaction">The transaction to use for this query.</param>        /// <param name="commandTimeout">Number of seconds before command execution timeout.</param>        /// <param name="commandType">Is it a stored proc or a batch?</param>        /// <returns>The number of rows affected.</returns>        int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null);
/// <summary> /// Executes a single-row query, returning the data typed as type. /// </summary> /// <param name="type">The type to return.</param> /// <param name="sql">The SQL to execute for the query.</param> /// <param name="param">The parameters to pass, if any.</param> /// <param name="transaction">The transaction to use, if any.</param> /// <param name="buffered">Whether to buffer results in memory.</param> /// <param name="commandTimeout">The command timeout (in seconds).</param> /// <param name="commandType">The type of command to execute.</param> /// <returns>A sequence of data of the supplied type; if a basic type(int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is created per row, and a direct column-name===member-name mapping is assumed(case insensitive). /// 异常:T:System.ArgumentNullException:type is null. /// </returns> IEnumerable<object> Query(Type type, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null); //这时省略n个方法 }


按照读写接口实现各自的类,这里就要在两个类的构造里取出各自己的连接字符串(读写库的实例或库名有所区别),这里用了个约定,就是在配置里,连接字符串的名称有read或write字样,通过这个约定来分别取读写连接字符串。

DapperPlusRead.cs

   /// <summary>    /// DapperPlus读实现类    /// </summary>    public class DapperPlusRead : IDapperPlusRead    {        protected IDbConnection _connection;        /// <summary>        /// 构造        /// </summary>        /// <param name="connection">连接</param>        /// <param name="configuration">配置</param>        public DapperPlusRead(IDbConnection connection, IConfiguration configuration)        {                        var connectionStrings = configuration.GetSection("ConnectionStrings").Get<Dictionary<string, string>>();            _connection = connection;            _connection.ConnectionString = connectionStrings.Where(s => s.Key.ToLower().Contains("read")).FirstOrDefault().Value;          }        /// <summary>        /// 连接        /// </summary>        public IDbConnection Connection        {            get            {                return _connection;            }        }
/// <summary> /// Executes a single-row query, returning the data typed as type. /// </summary> /// <param name="type">The type to return.</param> /// <param name="sql">The SQL to execute for the query.</param> /// <param name="param">The parameters to pass, if any.</param> /// <param name="transaction">The transaction to use, if any.</param> /// <param name="buffered">Whether to buffer results in memory.</param> /// <param name="commandTimeout">The command timeout (in seconds).</param> /// <param name="commandType">The type of command to execute.</param> /// <returns>A sequence of data of the supplied type; if a basic type(int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is created per row, and a direct column-name===member-name mapping is assumed(case insensitive). /// 异常:T:System.ArgumentNullException:type is null. /// </returns> public IEnumerable<object> Query(Type type, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null) { return _connection.Query(type, sql, param, transaction, buffered, commandTimeout, commandType); } //这时省略n个方法 }

DapperPlusWrite.cs

    /// <summary>    /// 写Dapper类    /// </summary>    public class DapperPlusWrite : IDapperPlusWrite    {        private readonly IDbConnection _connection;        /// <summary>        /// 构造        /// </summary>        /// <param name="connection">连接</param>        /// <param name="configuration">配置</param>        public DapperPlusWrite(IDbConnection connection, IConfiguration configuration)        {            var connectionStrings = configuration.GetSection("ConnectionStrings").Get<Dictionary<string, string>>();            _connection = connection;            _connection.ConnectionString = connectionStrings.Where(s => s.Key.ToLower().Contains("write")).FirstOrDefault().Value;        }        /// <summary>        /// 连接        /// </summary>        public IDbConnection Connection        {            get            {                return _connection;            }        }        /// <summary>        /// Execute parameterized SQL.        /// </summary>        /// <param name="sql">The SQL to execute for this query.</param>        /// <param name="param">The parameters to use for this query.</param>        /// <param name="transaction">The transaction to use for this query.</param>        /// <param name="commandTimeout">Number of seconds before command execution timeout.</param>        /// <param name="commandType">Is it a stored proc or a batch?</param>        /// <returns>The number of rows affected.</returns>        public int Execute(string sql, object param = null, IDbTransaction transaction = null, int? commandTimeout = null, CommandType? commandType = null)        {            return _connection.Execute(sql, param, transaction, commandTimeout, commandType);        }        /// <summary>        /// Executes a single-row query, returning the data typed as type.        /// </summary>        /// <param name="type">The type to return.</param>        /// <param name="sql">The SQL to execute for the query.</param>        /// <param name="param">The parameters to pass, if any.</param>        /// <param name="transaction">The transaction to use, if any.</param>        /// <param name="buffered">Whether to buffer results in memory.</param>        /// <param name="commandTimeout">The command timeout (in seconds).</param>        /// <param name="commandType">The type of command to execute.</param>        /// <returns>A sequence of data of the supplied type; if a basic type(int, string, etc) is queried then the data from the first column in assumed, otherwise an instance is created per row, and a direct column-name===member-name mapping is assumed(case insensitive).        /// 异常:T:System.ArgumentNullException:type is null.        /// </returns>        public IEnumerable<object> Query(Type type, string sql, object param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)        {            return _connection.Query(type, sql, param, transaction, buffered, commandTimeout, commandType);        }        //这时省略n个方法     }

剩下的就是注入了,使用读时用IDapperPlusRead就可以,写就用IDapperPlusWrite。

public void ConfigureServices(IServiceCollection services){     services.AddControllers();     services.AddScoped<IDbConnection, MySqlConnection>();     services.AddScoped<IDapperPlusRead, DapperPlusRead>();     services.AddScoped<IDapperPlusWrite, DapperPlusWrite>();}

appsettings.json

  "ConnectionStrings": {    "ReadConnectionString": "server=127.0.0.1;uid=root;pwd=root;database=read_mysql_testdb",    "WriteConnectionString": "server=127.0.0.1;uid=root;pwd=root;database=write_mysql_testdb"  }



往期精彩回顾




【推荐】.NET Core开发实战视频课程 ★★★

.NET Core实战项目之CMS 第一章 入门篇-开篇及总体规划

【.NET Core微服务实战-统一身份认证】开篇及目录索引

Redis基本使用及百亿数据量中的使用技巧分享(附视频地址及观看指南)

.NET Core中的一个接口多种实现的依赖注入与动态选择看这篇就够了

10个小技巧助您写出高性能的ASP.NET Core代码

用abp vNext快速开发Quartz.NET定时任务管理界面

在ASP.NET Core中创建基于Quartz.NET托管服务轻松实现作业调度

现身说法:实际业务出发分析百亿数据量下的多表查询优化

关于C#异步编程你应该了解的几点建议

C#异步编程看这篇就够了


浏览 17
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报
评论
图片
表情
推荐
点赞
评论
收藏
分享

手机扫一扫分享

分享
举报