有两种方法可以记录执行的 SQl 语句:
使用 DbContext.Database.Log 属性
实现 IDbCommandInterceptor 接口
一 使用 DbContext.Database.Log 属性
下面截图显示了 Database 属性和 Log 属性, 可以看出这个属性是一个委托, 类型为 Action<string>
对 Log 属性的解释为:
Set this property to log the SQL generated by the System.Data.Entity.DbContext to the given delegate. For example, to log to the console, set this property to System.Console.Write(System.String).
使用方法:
1) 在自定义上下文中获得执行的 SQL 相关信息, 即在自定上下文的构造函数中使用 Database.Log
- /// <summary>
- /// 自定义上下文
- /// </summary>
- [DbConfigurationType(typeof(MySqlEFConfiguration))]
- public class CustomDbContext : DbContext
- {
- public CustomDbContext()
- : base("name=Master")
- {
- //this.Configuration.LazyLoadingEnabled = false;
- //new DropCreateDatabaseIfModelChanges<CustomDbContext>()
- //new DropCreateDatabaseAlways<CustomDbContext>()
- Database.SetInitializer<CustomDbContext>(null);
- this.Database.Log = Log;
- }
- public DbSet<User> Users { get; set; }
- protected override void OnModelCreating(DbModelBuilder modelBuilder)
- {
- base.OnModelCreating(modelBuilder);
- EntityConfiguration.Set(modelBuilder);
- }
- private void Log(string cmd)
- {
- // 或输出到控制台
- //Console.Write(cmd);
- // 或输出到文件
- //using (StreamWriter sw = new StreamWriter(@"E:\EFCmdLogger.txt"))
- //{
- // sw.WriteLine(cmd);
- //}
- // 或输出到调试信息窗口
- Debug.WriteLine(cmd);
- }
- }
执行结果如下截图
2) 在具体的方法中使用
- public class EFOPerations
- {
- public static void ReadUser()
- {
- Stopwatch stw = new Stopwatch();
- stw.Start();
- using (CustomDbContext db = new CustomDbContext())
- {
- db.Database.Log = Console.WriteLine;
- User user = db.Users.Find(1);
- var userDTO = new { Account = user.Account };
- }
- stw.Stop();
- var time = stw.ElapsedMilliseconds;
- }
- }
注意
db.Database.Log = Console.WriteLine; 这条语句的位置; 如果将其放到查询语句, 即 User user = db.Users.Find(1); 之后则无法输出信息!
还可以改变日志的格式:
创建继承自 DatabaseLogFormatter 的类, 实现新的格式化器, 然后使用
System.Data.Entity.DbConfiguration.SetDatabaseLogFormatter(System.Func<System.Data.Entity.DbContext,System.Action<System.String>,System.Data.Entity.Infrastructure.Interception.DatabaseLogFormatter>)
DatabaseLogFormatter 的三个方法
LogCommand: 在 SQL 语句或存储过程执行前记录它
LogParameter: 记录参数, 默认被 LogCommand 调用 (未能验证这一点)
LogResult: 记录 SQL 语句或存储过程执行后的一些相关信息
这三个方法包含的参数为:
DbCommand command:SQL 语句或存储过程相关的信息
DbCommandInterceptionContext<TResult> interceptionContext: 执行结果相关的信息
DbParameter parameter:System.Data.Common.DbCommand 的参数
重写 LogCommand 或 LogResult 都可以改变 SQL 语句或存储过程相关信息格式, 但是注意这两个方法 interceptionContext 参数的值可能会不一样
继承 DatabaseLogFormatter, 实现格式化器
- public class CustomDatabaseLogFormatter : DatabaseLogFormatter
- {
- public CustomDatabaseLogFormatter(DbContext context, Action<string> writeAction)
- : base(context, writeAction)
- {
- }
- public override void LogCommand<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
- {
- }
- public override void LogResult<TResult>(DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext)
- {
- StringBuilder sb = new StringBuilder();
- for (int i = 0; i < command.Parameters.Count; i++)
- {
- sb.AppendLine(string.Format("参数名称:{0}, 值:{1}", command.Parameters[0].ParameterName, command.Parameters[0].Value));
- }
- Write(command.CommandText + Environment.NewLine
- + command.CommandTimeout + Environment.NewLine
- + command.CommandType + Environment.NewLine
- + Environment.NewLine
- + sb.ToString());
- }
- }
设置新的格式化器
- public class CustomDbConfiguration : MySqlEFConfiguration
- {
- public CustomDbConfiguration():base()
- {
- //this.AddInterceptor(new CommandInterceptor(new Logger()));
- SetDatabaseLogFormatter((context, writeAction) => new CustomDatabaseLogFormatter(context, writeAction));
- }
- }
使用自定义 CustomDbConfiguration
- [DbConfigurationType(typeof(CustomDbConfiguration))]
- public class CustomDbContext : DbContext
- {
- public CustomDbContext()
- : base("name=Master")
- {
- //this.Configuration.LazyLoadingEnabled = false;
- //new DropCreateDatabaseIfModelChanges<CustomDbContext>()
- //new DropCreateDatabaseAlways<CustomDbContext>()
- Database.SetInitializer<CustomDbContext>(null);
- this.Database.Log = Log;
- }
- ......
- }
二 实现 IDbCommandInterceptor 接口
实现 IDbCommandInterceptor, 同时为了灵活的记录执行信息, 定义了日志接口
- public class CommandInterceptor : IDbCommandInterceptor
- {
- private ICommandLogger logger;
- public CommandInterceptor(ICommandLogger logger)
- {
- this.logger = logger;
- }
- public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
- {
- this.logger.Log<int>(command, interceptionContext);
- }
- public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
- {
- this.logger.Log<int>(command, interceptionContext);
- }
- public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
- {
- this.logger.Log<DbDataReader>(command, interceptionContext);
- }
- public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<System.Data.Common.DbDataReader> interceptionContext)
- {
- this.logger.Log<DbDataReader>(command, interceptionContext);
- }
- public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
- {
- this.logger.Log<object>(command, interceptionContext);
- }
- public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
- {
- this.logger.Log<object>(command, interceptionContext);
- }
- }
- public interface ICommandLogger
- {
- void Log<T>(DbCommand command, DbCommandInterceptionContext<T> interceptionContext);
- }
- public class Logger : ICommandLogger
- {
- public void Log<T>(System.Data.Common.DbCommand command, System.Data.Entity.Infrastructure.Interception.DbCommandInterceptionContext<T> interceptionContext)
- {
- StringBuilder sb = new StringBuilder();
- for(int i =0;i<command.Parameters.Count;i++)
- {
- sb.AppendLine(string.Format("参数名称:{0}, 值:{1}", command.Parameters[0].ParameterName, command.Parameters[0].Value));
- }
- Debug.WriteLine(command.CommandText+Environment.NewLine
- + command.CommandTimeout + Environment.NewLine
- + command.CommandType + Environment.NewLine
- + Environment.NewLine
- + sb.ToString());
- }
- }
如何使用这两个类呢?
1 使用配置文件
- <entityFramework>
- <interceptors>
- <interceptor type="ConsoleApp_EntityFramework.Interceptor.CommandInterceptor, ConsoleApp_EntityFramework.Interceptor">
- </interceptor>
- </interceptors>
- </entityFramework>
但是采用这种方式要对上面的 CommandInterceptor 进行改造
- public class CommandInterceptor : IDbCommandInterceptor
- {
- private ICommandLogger logger;
- public CommandInterceptor()
- {
- this.logger = new Logger();
- }
- ......
- }
但是如果 EF 操作的是 Mysql 那么这种方法不行, 抛出异常: 无法识别的元素 interceptors
2 编码方式
只有上面两个类还不够, 还要定义创建一个继承自 DbConfiguration 的配置类
- public class CustomDbConfiguration : DbConfiguration
- {
- public CustomDbConfiguration():base()
- {
- this.AddInterceptor(new CommandInterceptor(new Logger()));
- }
- }
在自定义数据库上下文上使用此特性
- /// <summary>
- /// 自定义上下文
- /// </summary>
- [DbConfigurationType(typeof(CustomDbConfiguration))]
- public class CustomDbContext : DbContext
- {
- ......
- }
一切准备好后运行程序, 却抛出异常:
The ADO.NET provider with invariant name 'MySql.Data.MySqlClient' is either not registered in the machine or application config file, or could not be loaded. See the inner exception for details.
似乎是 MySql.Data.MySqlClient 的问题, 其实不是!
如果是 SQL Server 则没问题, 但这里 EF 框架操作的是 MySql, 要是使用 MySql.Data.Entity.MySqlEFConfiguration 这个类, 而不是 System.Data.Entity.DbConfiguration, 所以 CustomDbConfiguration 应该派生自 MySql.Data.Entity.MySqlEFConfiguration
- public class CustomDbConfiguration : MySqlEFConfiguration
- {
- public CustomDbConfiguration():base()
- {
- this.AddInterceptor(new CommandInterceptor(new Logger()));
- }
- .....
- }
这样修改后, 运行程序得到下面的结果:
可以看到日志打印了两次, 这是因为 ReaderExecuting 和 ReaderExecuted 各调用了一次, 执行的顺序是先 ReaderExecuting 然后 ReaderExecuted
-----------------------------------------------------------------------------------------
来源: https://www.cnblogs.com/hdwgxz/p/8487945.html