使用方案

使用监听接口IDbCommandInterceptor来实现监控SQL性能和记录生成的SQL(也可以使用DbCommandInterceptor来实现)

其他方案

  • MiniProfiler 详见官方文档
  • SQL Server Profiler 创建跟踪器即可。

IDbCommandInterceptor

实现此接口的对象可以在DbIntercept 中注册,以在实体框架执行命令时接收通知。官方文档

先看一下Github上的源码：(注释没有进行翻译直接Ctrl C V过来的)GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
namespace System.Data.Entity.Infrastructure.Interception
{
    using System.Data.Common;

    /// <summary>
    /// An object that implements this interface can be registered with <see cref="DbInterception" /> to
    /// receive notifications when Entity Framework executes commands.
    /// </summary>
    /// <remarks>
    /// Interceptors can also be registered in the config file of the application.
    /// See http://go.microsoft.com/fwlink/?LinkId=260883 for more information about Entity Framework configuration.
    /// </remarks>
    public interface IDbCommandInterceptor : IDbInterceptor
    {
        /// <summary>
        /// This method is called before a call to <see cref="DbCommand.ExecuteNonQuery" /> or
        /// one of its async counterparts is made.
        /// </summary>
        /// <param name="command">The command being executed.</param>
        /// <param name="interceptionContext">Contextual information associated with the call.</param>
        void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext);

        /// <summary>
        /// This method is called after a call to <see cref="DbCommand.ExecuteNonQuery" />  or
        /// one of its async counterparts is made. The result used by Entity Framework can be changed by setting
        /// <see cref="DbCommandInterceptionContext{T}.Result" />.
        /// </summary>
        /// <remarks>
        /// For async operations this method is not called until after the async task has completed
        /// or failed.
        /// </remarks>
        /// <param name="command">The command being executed.</param>
        /// <param name="interceptionContext">Contextual information associated with the call.</param>
        void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext);

        /// <summary>
        /// This method is called before a call to <see cref="DbCommand.ExecuteReader(CommandBehavior)" /> or
        /// one of its async counterparts is made.
        /// </summary>
        /// <param name="command">The command being executed.</param>
        /// <param name="interceptionContext">Contextual information associated with the call.</param>
        void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext);

        /// <summary>
        /// This method is called after a call to <see cref="DbCommand.ExecuteReader(CommandBehavior)" /> or
        /// one of its async counterparts is made. The result used by Entity Framework can be changed by setting
        /// <see cref="DbCommandInterceptionContext{T}.Result" />.
        /// </summary>
        /// <remarks>
        /// For async operations this method is not called until after the async task has completed
        /// or failed.
        /// </remarks>
        /// <param name="command">The command being executed.</param>
        /// <param name="interceptionContext">Contextual information associated with the call.</param>
        void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext);

        /// <summary>
        /// This method is called before a call to <see cref="DbCommand.ExecuteScalar" /> or
        /// one of its async counterparts is made.
        /// </summary>
        /// <param name="command">The command being executed.</param>
        /// <param name="interceptionContext">Contextual information associated with the call.</param>
        void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext);

        /// <summary>
        /// This method is called after a call to <see cref="DbCommand.ExecuteScalar" /> or
        /// one of its async counterparts is made. The result used by Entity Framework can be changed by setting
        /// <see cref="DbCommandInterceptionContext{T}.Result" />.
        /// </summary>
        /// <remarks>
        /// For async operations this method is not called until after the async task has completed
        /// or failed.
        /// </remarks>
        /// <param name="command">The command being executed.</param>
        /// <param name="interceptionContext">Contextual information associated with the call.</param>
        void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext);
    }
}

实现接口

  • 定义一个Stopwatch用来记录SQL执行时间的,在每一个Executing方法中restart sw,在每一个Executed方法中stop sw,这样就可以记录执行时间了。
  • 实现一个读取parameter详情的方法。
  • 在Executed方法中记录生成的SQL和parameter。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
public class EFMonitorHelper : IDbCommandInterceptor
{
    private readonly Stopwatch _sw = new Stopwatch();

    public void ScalarExecuting(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        _sw.Restart();
    }

    public void ScalarExecuted(DbCommand command, DbCommandInterceptionContext<object> interceptionContext)
    {
        _sw.Stop();
        if (interceptionContext.Exception != null)
        {
            Trace.TraceError("Exception:{1} \r\n --> Error ScalarExecuted Command: {0} \r\n -->ScalarExecuted Parameters:\r\n{2} ", 
                command.CommandText, interceptionContext.Exception.ToString(), FormatParameters(command.Parameters));
        }
        else
        {
            Trace.TraceInformation("\r\nExecution Time:{0} ms\r\n-->ScalarExecuted Command:{1}\r\n \r\n -->ScalarExecuted Parameters:\r\n{2} ", 
                _sw.ElapsedMilliseconds, command.CommandText, FormatParameters(command.Parameters));
        }
    }

    public void NonQueryExecuting(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        _sw.Restart();
    }

    public void NonQueryExecuted(DbCommand command, DbCommandInterceptionContext<int> interceptionContext)
    {
        _sw.Stop();
        if (interceptionContext.Exception != null)
        {
            Trace.TraceError("Exception:{1} \r\n --> Error NonQueryExecuted Command:\r\n {0} \r\n -->NonQueryExecuted Parameters:\r\n{2} ", 
                command.CommandText, interceptionContext.Exception.ToString(), FormatParameters(command.Parameters));
        }
        else
        {
            Trace.TraceInformation("\r\nExecution Time:{0} ms\r\n-->NonQueryExecuted Command:\r\n{1} \r\n -->NonQueryExecuted Parameters:\r\n{2} ", 
                _sw.ElapsedMilliseconds, command.CommandText, FormatParameters(command.Parameters));
        }
    }

    public void ReaderExecuting(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        _sw.Restart();
    }

    public void ReaderExecuted(DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext)
    {
        _sw.Stop();
        if (interceptionContext.Exception != null)
        {
            Trace.TraceError("Exception:{1} \r\n --> Error ReaderExecuted Command:\r\n{0} \r\n -->ReaderExecuted Parameters:\r\n{2} ", 
                command.CommandText, interceptionContext.Exception.ToString(), FormatParameters(command.Parameters));
        }
        else
        {
            Trace.TraceInformation("\r\nExecution Time:{0} ms \r\n -->ReaderExecuted Command:\r\n{1} \r\n -->ReaderExecuted Parameters:\r\n{2} ", 
                _sw.ElapsedMilliseconds, command.CommandText, FormatParameters(command.Parameters));
        }
    }

    private string FormatParameters(DbParameterCollection dbParameterCollection) 
    {
        var sb = new StringBuilder();
        for (int i = 0; i < dbParameterCollection.Count; i++)
        {
            var parameter = dbParameterCollection[i];
            sb.AppendLine($"{parameter.ParameterName}:{parameter.Value}");
        }
        return sb.ToString();
    }
}

然后在Application_Start中使用EFMonitorHelper就🆗了。

1
2
3
4
5
6
protected void Application_Start()
{
    ///...
    DbInterception.Add(new Helper.EFMonitorHelper());
    ///...
}