文章出處
文章列表
前幾天看了一個基于sqlserver的負載均衡與讀寫分離的軟件Moebius,實現的方式還是不錯的，這使得用sqlserver數據庫的同學時有機會對數據庫進行更有效的優化了
看著人有做的東西，自己也想用ＥＦ來實現一個讀寫分離，所以就有了本篇文章，倉儲大叔讀寫分離的思路是：
1 用sqlserver自帶的發布、訂閱實現主，從數據庫的結構，同步這事由sql幫我們完成
2 配置文件建立幾個供只讀的數據庫連接串
3 建立ＳＱＬ命令攔截器
4 修改大叔的DbContextRepository基數，添加攔截行為
5 測試，搞定
有了上面的想法，咱就可以干事了，第一步不用說了，可以自己百度，從第２步說起
2 配置文件建立幾個供只讀的數據庫連接串
<!-- 只寫-->
<add name="backgroundEntities" connectionString="metadata=res://*/background.csdl|res://*/background.ssdl|res://*/background.msl;provider=System.Data.SqlClient;provider connection string="data source=.;initial catalog=background;persist security info=True;user id=sa;password=zzl123;multipleactiveresultsets=True;application name=EntityFramework"" providerName="System.Data.EntityClient" />
<!-- 只讀-->
<add name="backgroundEntitiesRead" connectionString="metadata=res://*/background.csdl|res://*/background.ssdl|res://*/background.msl;provider=System.Data.SqlClient;provider connection string="data source=.;initial catalog=background_Read1;persist security info=True;user id=sa;password=zzl123;multipleactiveresultsets=True;application name=EntityFramework"" providerName="System.Data.EntityClient" />
3 建立ＳＱＬ命令攔截器
/// <summary> /// SQL命令攔截器 /// </summary> public class NoLockInterceptor : DbCommandInterceptor { private static readonly Regex _tableAliasRegex = new Regex(@"(?<tableAlias>AS \[Extent\d+\](?! WITH \(NOLOCK\)))", RegexOptions.Multiline | RegexOptions.IgnoreCase); [ThreadStatic] public static bool SuppressNoLock; public override void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext) { string conn = command.Connection.ConnectionString; base.NonQueryExecuting(command, interceptionContext); } public override void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext) { command.Connection.Close(); command.Connection.ConnectionString = "data source=.;initial catalog=background_Read1;persist security info=True;user id=sa;password=zzl123;multipleactiveresultsets=True;application name=EntityFramework"; command.Connection.Open(); if (!SuppressNoLock) { command.CommandText = _tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)"); } } public override void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) { command.Connection.Close(); command.Connection.ConnectionString = "data source=.;initial catalog=background_Read1;persist security info=True;user id=sa;password=zzl123;multipleactiveresultsets=True;application name=EntityFramework"; command.Connection.Open(); if (!SuppressNoLock) { command.CommandText = _tableAliasRegex.Replace(command.CommandText, "${tableAlias} WITH (NOLOCK)"); } } }
4 修改大叔的DbContextRepository基數，添加攔截行為
public DbContextRepository(IUnitOfWork db, Action<string> logger) { UnitWork = db; Db = (DbContext)db; Logger = logger; ((IObjectContextAdapter)Db).ObjectContext.CommandTimeout = 0; //SQL語句攔截器 System.Data.Entity.Infrastructure.Interception.DbInterception.Add(new EntityFrameworks.Data.Core.Common.NoLockInterceptor()); EntityFrameworks.Data.Core.Common.NoLockInterceptor.SuppressNoLock = true; }
5 大功造成，感謝閱讀！
本文章代碼沒有全部展示，只是展示一種思想，希望可以給大家帶來幫助。
文章列表
全站熱搜
留言列表
