c# - 在 Entity Framework 中记录插入和更新命令 sql 查询

Question

我正在尝试在我的网络应用程序中记录所有数据库操作。

我有一个写在数据库上的日志类

public partial class LOGS
{
    public static int AddLogs(LOGS log)
    {
        int ret = 0;
        try
        {
            using (var context = new Entities())
            {
                log.Data = DateTime.Now;
                context.LOGS.Add(log);
                ret += context.SaveChanges();
            }
            return ret;
        }
        catch (Exception ex)
        {
            string w = ex.Message;
            return -1;
        }
    }

    public static void WriteDetailed(string query)
    {
        if (u == null || u.LOGLevel == 0)
            return;
        else
        {
            StackTrace st = new StackTrace();
            StackFrame sf = st.GetFrame(1);
            if (sf != null)
            {
                MethodBase currentMethodName = sf.GetMethod();
                String metodo = currentMethodName.ReflectedType.FullName + " " + currentMethodName.Name;

                LOGS newLog = new LOGS();
                newLog.Tipo = "Q";
                newLog.TipoLog = metodo;
                newLog.Testo = query;

                AddLogs(newLog);
            }
        }
    }
}

以这种方式记录方法中的选择操作:

 public static List<Agent> GetAgents()
    {
        try
        {
            using (var context = new Entities())
            {
                var entities = (from a in context.Agent
                                select a);
                LOGS.WriteDetailed(entities.ToString());
                return entities.ToList();
            }
        }
        catch (Exception ex)
        {
            LOGS.WriteExceptionLog(ex);
            return null;
        }
    }

但对我来说，不可能记录插入、更新、结束删除语句

我读到我可以使用这个方法

context.Database.Log = msg => LOGS.WriteDetailed(msg);

我尝试过以这种方式使用它:

 public static bool AddAgent(Agent newAgent)
    {
        bool ret = true;

        using (var context = new Entities())
        {
            using (var dbContextTransaction = context.Database.BeginTransaction())
            {
                try
                {
                    newAgent.DateLM = DateTime.Now;
                    context.Agent.Add(newAgent);

                    context.Database.Log = msg => LOGS.WriteDetailed(msg);
                    ret = ret && context.SaveChanges() > 0;
                    if (ret)
                        dbContextTransaction.Commit();
                    else
                        dbContextTransaction.Rollback();

                    return ret;

                }
                catch (Exception ex)
                {
                    LOGS.WriteExceptionLog(ex);
                    return false;
                }
            }
        }

    }

这是可行的，但以一种奇怪的方式:在单个 INSERT 操作中，它将在方法 WriteDetailed 中传递 8 次...

1. INSERT [dbo].[Agent]([Name], [Active], [UserLM], [DateLM]) VALUES (@0, @1, @2, @3) SELECT [ID_Agent] FROM [dbo] .[Agent] WHERE @@ROWCOUNT > 0 AND [ID_Agent] = scope_identity()
2. (空)3.-- @0: 'a'(类型 = AnsiString，大小 = 100)
3. -- @1: 'True' (Type = Boolean)
4. -- @2: '1' (Type = Int32)
5. -- @3: '25/08/2017 13:34:16' (Type = DateTime2)
6. -- 执行时间 25/08/2017 13:46:17 +02:00

7. -- 在 8 毫秒内完成，结果:SqlDataReader

   不可能一次拥有完整的 INSERT 语句吗？

Answer 1

你必须使用比 context.Database.Log 更复杂的机制: https://msdn.microsoft.com/en-us/library/dn469464(v=vs.113).aspx

除了提到的context.Database.Log还有DatabaseLogFormatter和IDbCommandInterceptor。这是链接中的示例:

public class NLogCommandInterceptor : IDbCommandInterceptor 
{ 
    private static readonly Logger Logger = LogManager.GetCurrentClassLogger(); 

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

    public void NonQueryExecuted( 
        DbCommand command, DbCommandInterceptionContext<int> interceptionContext) 
    { 
        LogIfError(command, interceptionContext); 
    } 

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

    public void ReaderExecuted( 
        DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) 
    { 
        LogIfError(command, interceptionContext); 
    } 

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

    public void ScalarExecuted( 
        DbCommand command, DbCommandInterceptionContext<object> interceptionContext) 
    { 
        LogIfError(command, interceptionContext); 
    } 

    private void LogIfNonAsync<TResult>( 
        DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext) 
    { 
        if (!interceptionContext.IsAsync) 
        { 
            Logger.Warn("Non-async command used: {0}", command.CommandText); 
        } 
    } 

    private void LogIfError<TResult>( 
        DbCommand command, DbCommandInterceptionContext<TResult> interceptionContext) 
    { 
        if (interceptionContext.Exception != null) 
        { 
            Logger.Error("Command {0} failed with exception {1}", 
                command.CommandText, interceptionContext.Exception); 
        } 
    } 
}

最简单的设置方法是使用静态方法:

DbInterception.Add(new NLogCommandInterceptor());