代码之家  ›  专栏  ›  技术社区  ›  Andrew

EF6拦截SQL并记录到db outofmemoryException

  •  1
  • Andrew  · 技术社区  · 6 年前

    我正在尝试将记录到表中的SQL记录到日志中,但收到OutofMemoryException。我知道为什么会有例外,但我不知道如何避免。

    应用程序上下文:

    public class ApplicationDbContext : IdentityDbContext<ApplicationUser> {   
        public ApplicationDbContext() :
                base("IdentityDBContext", false) {
            DbInterception.Add(new InsertUpdateInterceptor());
        }
    }
    

    Web数据实体:

       public WebDataEntities()
                : base("name=WebDataEntities")
            {
            }
    

    日志类:

     public class InsertUpdateInterceptor : IDbCommandInterceptor {
            public virtual void NonQueryExecuting(
                DbCommand command, DbCommandInterceptionContext<int> interceptionContext) {
                logCommand(command);
            }
    
            public virtual void ReaderExecuting(
                DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) {
                // this will capture all SELECT queries if you care about them..
                // however it also captures INSERT statements as well 
                logCommand(command);
            }
    
            public virtual void ScalarExecuting(
             DbCommand command, DbCommandInterceptionContext<object> interceptionContext) {
                logCommand(command);
            }
    
    
            private void logCommand(DbCommand dbCommand) {
                StringBuilder commandText = new StringBuilder();
    
                commandText.AppendLine("-- New statement generated: " + System.DateTime.Now.ToString());
                commandText.AppendLine();
    
                // as the command has a bunch of parameters, we need to declare
                // those parameters here so the SQL will execute properly
    
                foreach (DbParameter param in dbCommand.Parameters) {
                    var sqlParam = (SqlParameter)param;
    
                    commandText.AppendLine(String.Format("DECLARE {0} {1} {2}",
                                                            sqlParam.ParameterName,
                                                            sqlParam.SqlDbType.ToString().ToLower(),
                                                            getSqlDataTypeSize(sqlParam)));
    
                    var escapedValue = sqlParam.SqlValue.ToString().Replace("'", "''");
                    commandText.AppendLine(String.Format("SET {0} = '{1}'", sqlParam.ParameterName, escapedValue));
                    commandText.AppendLine();
                }
    
                commandText.AppendLine(dbCommand.CommandText);
                commandText.AppendLine("GO");
                commandText.AppendLine();
                commandText.AppendLine();
    
                using(var context = new WebDataEntities()) {
                    context.tbl_FSV_AspNetIdentity_SQL_Log.Add(new tbl_FSV_AspNetIdentity_SQL_Log { Sql = commandText.ToString() });
                    context.SaveChanges();
                }
    
                //System.IO.File.AppendAllText("outputfile.sql", commandText.ToString());
            }
    
            private string getSqlDataTypeSize(SqlParameter param) {
                if (param.Size == 0) {
                    return "";
                }
    
                if (param.Size == -1) {
                    return "(MAX)";
                }
    
                return "(" + param.Size + ")";
            }
    
    
            // To implement the IDbCommandInterceptor interface you need to also implement these methods like so
    
            public void NonQueryExecuted(
                DbCommand command, DbCommandInterceptionContext<int> interceptionContext) {
            }
    
            public void ReaderExecuted(
                DbCommand command, DbCommandInterceptionContext<DbDataReader> interceptionContext) {
            }
    
            public void ScalarExecuted(
                DbCommand command, DbCommandInterceptionContext<object> interceptionContext) {
            }
        }
    

    我收到这个异常是因为当我试图记录SQL时,它会陷入一个循环,试图记录应该记录SQL到数据库的SQL。但我使用了两种不同的上下文,所以为什么它要尝试将SQL记录两次?

    2 回复  |  直到 6 年前
        1
  •  2
  •   Gert Arnold    6 年前

    DbInterception.Add(new InsertUpdateInterceptor());
    

    InsertUpdateInterceptor ApplicationDbContext

    WebDataEntities

    SqlCommand

    DbInterception.Add(new InsertUpdateInterceptor())

        2
  •  0
  •   Steve Py    6 年前

    public ApplicationDbContext() :
                base("IdentityDBContext", false) 
    {
        this.AddInterceptor(new InsertUpdateInterceptor());
    }