Dapper读写分离

在上一篇说了封闭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"
  }

 

© 版权声明

☆ END ☆
喜欢就点个赞吧
点赞0 分享
评论 抢沙发
  • 武穆逸仙

    昵称

  • 取消回复

    请填写用户信息:

图片正在生成中,请稍后...