在實際業務系統中，當單個資料庫不能承載負載壓力的時候，一般我們采用資料庫讀寫分離的方式來分擔資料庫負載，主庫承擔寫以及事務操作，從庫承擔讀操作，
為了支持多種資料庫我們先定義一個資料型別字典，key為連接字串，value為資料庫型別：
/// <summary> /// 資料庫方言集合 /// </summary> private readonly Dictionary<string, DatabaseDialectEnum> DialectDictionary = new Dictionary<string, DatabaseDialectEnum> { ["sqlconnection"] = DatabaseDialectEnum.MSSQL, ["sqlceconnection"] = DatabaseDialectEnum.SQLCE, ["npgsqlconnection"] = DatabaseDialectEnum.POSTGRES, ["sqliteconnection"] = DatabaseDialectEnum.SQLLITE, ["mysqlconnection"] = DatabaseDialectEnum.MYSQL, ["fbconnection"] = DatabaseDialectEnum.FIREBASE };
這樣我們切換不同的資料庫只需要配置資料庫連接字串即可，
以mssql為例，配置資料庫連接字串
"ConnectionString": { "sqlconnection": "Data Source=.;Initial Catalog=Db;User ID=sa;Password=**;Enlist=false;Max Pool SIZE=500;Min Pool SIZE=50;MultipleActiveResultSets=True", "sqlconnection_slaver_1": "Data Source=.;Initial Catalog=Db;User ID=sa;Password=**;Enlist=false;Max Pool SIZE=500;Min Pool SIZE=50;MultipleActiveResultSets=True", "sqlconnection_slaver_2": "Data Source=.;Initial Catalog=Db;User ID=sa;Password=**;Enlist=false;Max Pool SIZE=500;Min Pool SIZE=50;MultipleActiveResultSets=True" }
Key: sqlconnection為主庫（master）連接字串，Key: sqlconnection_slaver_1和sqlconnection_slaver_2為兩個從庫（slaver）連接字串，多個從庫(slaver)可以實作隨機訪問，也可以采用其他演算法來負載均衡，
根據字串連接配置我們得到 主庫 連接串，和從庫連接串集合，同時根據連接串的key 確定資料庫種類，代碼如下：
/// <summary> /// 主資料庫連接串 /// </summary> private string MasterConnectionString { get; set; } /// <summary> /// 從資料庫連接串集合 /// </summary> private List<string> SlaverConnectionStrings { get; set; } = new List<string>(); public ConnectionFactory(IConfiguration configuration, ILoggerFactory loggerFactory) { _logger = loggerFactory.CreateLogger<ConnectionFactory>(); var connectionKeys = configuration.GetSection("ConnectionString").GetChildren().Select(s => s.Key).ToArray(); foreach (var connKey in connectionKeys) { var connSplit = connKey.Split('_'); if (connSplit.Length == 1) { MasterConnectionString = configuration[$"ConnectionString:{connKey}"];
//根據連接字串約定，確定資料庫型別 DatabaseDialect = DialectDictionary[connKey]; } else { SlaverConnectionStrings.Add(configuration[$"ConnectionString:{connKey}"]); } } }
/// <summary> /// 資料庫型別 /// </summary> public DatabaseDialectEnum DatabaseDialect { get; private set; }
獲取主庫連接
private IDbConnection GetMasterConnection() { return GetConnection(MasterConnectionString); }
獲取從庫連接，這里采用隨機演算法，如果沒有配置從庫，這里會回傳主庫連接，
private IDbConnection GetSlaverConnection() { int sc = SlaverConnectionStrings.Count(); if (sc > 0) { Random random = new Random(); int index = random.Next(0, sc); return GetConnection(SlaverConnectionStrings[index]); } else { _logger.LogInformation("沒有設定從庫，將建立主庫連接"); return GetMasterConnection(); } }
private IDbConnection GetConnection(string connectionString) => DatabaseDialect switch { DatabaseDialectEnum.MSSQL =>new ProfiledDbConnection(new SqlConnection(connectionString),MiniProfiler.Current), DatabaseDialectEnum.MYSQL => new ProfiledDbConnection(new MySqlConnection(connectionString), MiniProfiler.Current), _ => throw new NotImplementedException() };
注：這里采用MiniProfiler來監控資料庫連接性能，所以 回傳的connection用ProfiledDbConnection進行了包裝，
主從資料源型別如下：
public enum DataSourceEnum { MASTER, SLAVE }
本ConnectionFactory為單例模式，存在多執行緒訪問的情況，所以資料源設定為ThreadLocal<DataSourceEnum>,執行緒內共享，
private static ThreadLocal<DataSourceEnum> threadLocal = new ThreadLocal<DataSourceEnum>(); /// <summary> /// 當前執行緒資料源 /// </summary> /// <param name="sourceEnum"></param> public DataSourceEnum DataSource { set { threadLocal.Value =https://www.cnblogs.com/wangyfb/p/ value; } get { return threadLocal.Value; } }
下面正式獲取IDbConnection
public IDbConnection GetDbConnection() { if (DataSource == DataSourceEnum.MASTER) { return GetMasterConnection(); } else { return GetSlaverConnection(); } }
使用：
根據文章開頭所描述的實際操作來進行主從庫訪問，
private IDbConnection GetDbConnection(DataSourceEnum dataSource) { ConnectionFactory.DataSource = dataSource; return ConnectionFactory.GetDbConnection(); }
using var connection = GetDbConnection(DataSourceEnum.MASTER); connection.Execute(sql, param, CurrentTransaction, null, commandType)
using var connection = GetDbConnection(DataSourceEnum.SLAVE); connection.Get<T>(id, CurrentTransaction, CommandTimeout)
奉上全部代碼
public class ConnectionFactory : IConnectionFactory { private readonly ILogger _logger; private static ThreadLocal<DataSourceEnum> threadLocal = new ThreadLocal<DataSourceEnum>(); static ConnectionFactory() { //設定dapper的tableName取值 SqlMapperExtensions.TableNameMapper = (type) => type.Name; } /// <summary> /// 當前執行緒資料源 /// </summary> /// <param name="sourceEnum"></param> public DataSourceEnum DataSource { set { threadLocal.Value =https://www.cnblogs.com/wangyfb/p/ value; } get { return threadLocal.Value; } } /// <summary> /// 主資料庫連接串 /// </summary> private string MasterConnectionString { get; set; } /// <summary> /// 從資料庫連接串集合 /// </summary> private List<string> SlaverConnectionStrings { get; set; } = new List<string>(); public ConnectionFactory(IConfiguration configuration, ILoggerFactory loggerFactory) { _logger = loggerFactory.CreateLogger<ConnectionFactory>(); var connectionKeys = configuration.GetSection("ConnectionString").GetChildren().Select(s => s.Key).ToArray(); foreach (var connKey in connectionKeys) { var connSplit = connKey.Split('_'); if (connSplit.Length == 1) { MasterConnectionString = configuration[$"ConnectionString:{connKey}"]; DatabaseDialect = DialectDictionary[connKey]; } else { SlaverConnectionStrings.Add(configuration[$"ConnectionString:{connKey}"]); } } } /// <summary> /// 資料庫方言集合 /// </summary> private readonly Dictionary<string, DatabaseDialectEnum> DialectDictionary = new Dictionary<string, DatabaseDialectEnum> { ["sqlconnection"] = DatabaseDialectEnum.MSSQL, ["sqlceconnection"] = DatabaseDialectEnum.SQLCE, ["npgsqlconnection"] = DatabaseDialectEnum.POSTGRES, ["sqliteconnection"] = DatabaseDialectEnum.SQLLITE, ["mysqlconnection"] = DatabaseDialectEnum.MYSQL, ["fbconnection"] = DatabaseDialectEnum.FIREBASE }; /// <summary> /// 資料庫方言 /// </summary> public DatabaseDialectEnum DatabaseDialect { get; private set; } private IDbConnection GetConnection(string connectionString) => DatabaseDialect switch { DatabaseDialectEnum.MSSQL =>new ProfiledDbConnection(new SqlConnection(connectionString),MiniProfiler.Current), DatabaseDialectEnum.MYSQL => new ProfiledDbConnection(new MySqlConnection(connectionString), MiniProfiler.Current), _ => throw new NotImplementedException() }; public IDbConnection GetDbConnection() { if (DataSource == DataSourceEnum.MASTER) { return GetMasterConnection(); } else { return GetSlaverConnection(); } } private IDbConnection GetMasterConnection() { return GetConnection(MasterConnectionString); } private IDbConnection GetSlaverConnection() { int sc = SlaverConnectionStrings.Count(); if (sc > 0) { Random random = new Random(); int index = random.Next(0, sc); return GetConnection(SlaverConnectionStrings[index]); } else { _logger.LogInformation("沒有設定從庫，將從建立主庫連接"); return GetMasterConnection(); } } } public enum DataSourceEnum { MASTER, SLAVE }
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/81940.html
標籤：.NET Core