EF架构~通过EF6的SQL命名拦截器来实现数据库读写分离

回到目录

前几天看了一个基于sqlserver的负载均衡与读写分离的软件Moebius,实现的方式还是不错的,这使得用sqlserver数据库的同学时有机会对数据库进行更有效的优化了

技术分享

看着人有做的东西,自己也想用EF来实现一个读写分离,所以就有了本篇文章,仓储大叔读写分离的思路是:

1  用sqlserver自带的发布、订阅实现主,从数据库的结构,同步这事由sql帮我们完成

2  配置文件建立几个供只读的数据库连接串

3  建立SQL命令拦截器

4  修改大叔的DbContextRepository基数,添加拦截行为

5  测试,搞定

有了上面的想法,咱就可以干事了,第一步不用说了,可以自己百度,从第2步说起

2  配置文件建立几个供只读的数据库连接串

    <!-- 只写-->
    <add name="backgroundEntities" connectionString="metadata=res://*/background.csdl|res://*/background.ssdl|res://*/background.msl;provider=System.Data.SqlClient;provider connection string=&quot;data source=.;initial catalog=background;persist security info=True;user id=sa;password=zzl123;multipleactiveresultsets=True;application name=EntityFramework&quot;" 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=&quot;data source=.;initial catalog=background_Read1;persist security info=True;user id=sa;password=zzl123;multipleactiveresultsets=True;application name=EntityFramework&quot;" providerName="System.Data.EntityClient" />

3  建立SQL命令拦截器

 /// <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  大功造成,感谢阅读!

本文章代码没有全部展示,只是展示一种思想,希望可以给大家带来帮助。

回到目录

 

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。