常用网站建设工具,2024全民核酸又开始了,wordpress 首页错误,沪佳装修官方电话前言本文主要是讲解EF Core3.0 通过拦截器实现读写分离与SQL日志记录注意拦截器只有EF Core3.0 支持,2.1请考虑上下文工厂的形式实现.说点题外话..一晃又大半年没更新技术博客..唉,去年一年发生了太多事情..博主真的 一言难尽..有兴趣的可以去看看:记录一下,也许是转折,也许是结… 前言本文主要是讲解EF Core3.0 通过拦截器实现读写分离与SQL日志记录注意拦截器只有EF Core3.0 支持,2.1请考虑上下文工厂的形式实现.说点题外话..一晃又大半年没更新技术博客..唉,去年一年发生了太多事情..博主真的 一言难尽..有兴趣的可以去看看:记录一下,也许是转折,也许是结束,也许是新希望的一年 正文1.通过拦截器实现读写分离先讲一下本文实现的方式吧SQL 通过数据库本身的功能 实现主从备份 大概原理如图: EF Core在查询的时候通过DbCommandInterceptor 拦截器(PS:这个功能在EF6.0中也实现了)来拦截对数据库的访问,从而切换主从数据库下面直接上代码吧首先我们创建一个类 继承DbCommandInterceptor: public class DbMasterSlaveCommandInterceptor : DbCommandInterceptor {private string _masterConnectionString;private string _slaveConnectionString;public DbMasterSlaveCommandInterceptor(string masterConnectionString, string slaveConnectionString){_masterConnectionString masterConnectionString;_slaveConnectionString slaveConnectionString;}
}通过构造函数传递主库连接地址与从库地址(可有多个 通过|分割)添加一个随机分配从表读取连接的方法(PS:这里只是demo所以很简陋的随机,如果正式要用,应包含权重判断,定时心跳从库连接情况,请自行修改): /// summary /// 通过随机数分配获取多个从库/// /summary/// returns/returnsprivate string GetSlaveConnectionString(){var readArr _slaveConnectionString.Split(new char[] { | }, StringSplitOptions.RemoveEmptyEntries);var resultConn string.Empty;if (readArr ! null readArr.Any()){resultConn readArr[Convert.ToInt32(Math.Floor((double)new Random().Next(0, readArr.Length)))];}return resultConn;}添加判断是否主从操作连接方法: private void UpdateToSlave(DbCommand command) {//判断是否配置了主从分离if (!string.IsNullOrWhiteSpace(GetSlaveConnectionString()))//如果配置了读写分离就进入判断{//判断是否为插入语句(EF 插入语句会通过Reader执行并查询主键),否则进入if (command.CommandText.ToLower().StartsWith(insert, StringComparison.InvariantCultureIgnoreCase) false){// 判断当前会话是否处于分布式事务中bool isDistributedTran Transaction.Current ! null Transaction.Current.TransactionInformation.Status !TransactionStatus.Committed;//判断该 context 是否处于普通数据库事务中bool isDbTran command.Transaction ! null;//如果不处于事务中,则执行从服务器查询if (!isDbTran !isDistributedTran){command.Connection.Close();command.Connection.ConnectionString GetSlaveConnectionString();command.Connection.Open();}}}} 重载DbCommandInterceptor当中的拦截方法,代码如下: //如果是写入,则正常执行public override InterceptionResultint NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResultint result){return base.NonQueryExecuting(command, eventData, result);}public override TaskInterceptionResultint NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResultint result, CancellationToken cancellationToken default){return base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);}public override InterceptionResultDbDataReader ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResultDbDataReader result){this.UpdateToSlave(command);return base.ReaderExecuting(command, eventData, result);}public override TaskInterceptionResultDbDataReader ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResultDbDataReader result, CancellationToken cancellationToken default){this.UpdateToSlave(command);return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);}public override InterceptionResultobject ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResultobject result){this.UpdateToSlave(command);return base.ScalarExecuting(command, eventData, result);}public override TaskInterceptionResultobject ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResultobject result, CancellationToken cancellationToken default){this.UpdateToSlave(command);return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);}最后在EF core的上下文中注入拦截器(PS:我这里使用的Autofac模块注入): builder.Register(c {var optionsBuilder new DbContextOptionsBuilderTestEFContext();//注入拦截器optionsBuilder.AddInterceptors(new DbMasterSlaveCommandInterceptor(WriteConnect, ReadConnect));//MaxBatchSize 处理批量操作BUGoptionsBuilder.UseMysql(WriteConnect, bb.MaxBatchSize(1));return optionsBuilder.Options;}).AsDbContextOptionsTestEFContex().SingleInstance();这样就实现了通过拦截器实现读写分离. 2.通过拦截器实现SQL日志记录同理,我们可以通过拦截器实现EF Core SQL语句的记录与调试首先我们创建一个新的拦截器DBlogCommandInterceptor 如下:public class DBlogCommandInterceptor : DbCommandInterceptor{//创建一个队列记录SQL执行时间static readonly ConcurrentDictionaryDbCommand, DateTime MStartTime new ConcurrentDictionaryDbCommand, DateTime();private ILoggerDBlogCommandInterceptor _logger { get; set; }//通过构造函数注入日志public DBlogCommandInterceptor(ILoggerDBlogCommandInterceptor Logger){_logger Logger;}
}创建2个私有的方法,一个记录执行开始时间,一个记录SQL //记录SQL开始执行的时间 private void OnStart(DbCommand command){MStartTime.TryAdd(command, DateTime.Now);}//通过_logger输出日志private void Log(DbCommand command){DateTime startTime;TimeSpan duration;//得到此command的开始时间MStartTime.TryRemove(command, out startTime);if (startTime ! default(DateTime)){duration DateTime.Now - startTime;}else{duration TimeSpan.Zero;}var parameters new StringBuilder();//循环获取执行语句的参数值foreach (DbParameter param in command.Parameters){parameters.AppendLine(param.ParameterName param.DbType param.Value);}_logger.LogInformation({starttime}开始执行SQL语句:{sql},参数:{canshu},执行时间{readtime},startTime.ToString(), command.CommandText, parameters.ToString(), duration.TotalSeconds);}最后重载拦截器的方法:public override InterceptionResultint NonQueryExecuting(DbCommand command, CommandEventData eventData, InterceptionResultint result){OnStart(command);return base.NonQueryExecuting(command, eventData, result);}public override TaskInterceptionResultint NonQueryExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResultint result, CancellationToken cancellationToken default){OnStart(command);return base.NonQueryExecutingAsync(command, eventData, result, cancellationToken);}public override int NonQueryExecuted(DbCommand command, CommandExecutedEventData eventData, int result){Log(command);return base.NonQueryExecuted(command, eventData, result);}public override Taskint NonQueryExecutedAsync(DbCommand command, CommandExecutedEventData eventData, int result, CancellationToken cancellationToken default){Log(command);return base.NonQueryExecutedAsync(command, eventData, result, cancellationToken);}public override InterceptionResultobject ScalarExecuting(DbCommand command, CommandEventData eventData, InterceptionResultobject result){OnStart(command);return base.ScalarExecuting(command, eventData, result);}public override TaskInterceptionResultobject ScalarExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResultobject result, CancellationToken cancellationToken default){OnStart(command);return base.ScalarExecutingAsync(command, eventData, result, cancellationToken);}public override object ScalarExecuted(DbCommand command, CommandExecutedEventData eventData, object result){Log(command);return base.ScalarExecuted(command, eventData, result);}public override Taskobject ScalarExecutedAsync(DbCommand command, CommandExecutedEventData eventData, object result, CancellationToken cancellationToken default){Log(command);return base.ScalarExecutedAsync(command, eventData, result, cancellationToken);}public override InterceptionResultDbDataReader ReaderExecuting(DbCommand command, CommandEventData eventData, InterceptionResultDbDataReader result){OnStart(command);return base.ReaderExecuting(command, eventData, result);}public override TaskInterceptionResultDbDataReader ReaderExecutingAsync(DbCommand command, CommandEventData eventData, InterceptionResultDbDataReader result, CancellationToken cancellationToken default){OnStart(command);return base.ReaderExecutingAsync(command, eventData, result, cancellationToken);}public override TaskDbDataReader ReaderExecutedAsync(DbCommand command, CommandExecutedEventData eventData, DbDataReader result, CancellationToken cancellationToken default){Log(command);return base.ReaderExecutedAsync(command, eventData, result, cancellationToken);}public override DbDataReader ReaderExecuted(DbCommand command, CommandExecutedEventData eventData, DbDataReader result){Log(command);return base.ReaderExecuted(command, eventData, result);}这样,我们就实现了通过拦截器实现SQL日志记录~效果如下: 调试SQL语句就方便了很多~
