讓Dapper讀寫分離
在上一篇說了封閉Dapper擴展方法為一個接口來支持Mock，接下來看看如何實現(xiàn)讀寫分離。
其實定義兩個接口，一個用來實現(xiàn)讀，一個用來實現(xiàn)寫。在讀的接口里只有Query的方法，在寫的接口里實現(xiàn)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個方法}
按照讀寫接口實現(xiàn)各自的類，這里就要在兩個類的構造里取出各自己的連接字符串(讀寫庫的實例或庫名有所區(qū)別)，這里用了個約定，就是在配置里，連接字符串的名稱有read或write字樣，通過這個約定來分別取讀寫連接字符串。
DapperPlusRead.cs
/// <summary>/// DapperPlus讀實現(xiàn)類/// </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開發(fā)實戰(zhàn)視頻課程 ★★★
.NET Core實戰(zhàn)項目之CMS 第一章 入門篇-開篇及總體規(guī)劃
【.NET Core微服務實戰(zhàn)-統(tǒng)一身份認證】開篇及目錄索引
Redis基本使用及百億數(shù)據(jù)量中的使用技巧分享（附視頻地址及觀看指南）
.NET Core中的一個接口多種實現(xiàn)的依賴注入與動態(tài)選擇看這篇就夠了
用abp vNext快速開發(fā)Quartz.NET定時任務管理界面
在ASP.NET Core中創(chuàng)建基于Quartz.NET托管服務輕松實現(xiàn)作業(yè)調(diào)度
評論
圖片
表情