拦截包含NHibernate生成的参数值的SQL语句

问题描述

我使用一个简单的拦截器来拦截 nhibernate 为记录目的而生成的 sql 字符串，它工作正常.

I use a simple interceptor to intercept the sql string that nhibernate generates for loging purposes and it works fine.

public class SessionManagerSQLInterceptor : EmptyInterceptor, IInterceptor
    {
        NHibernate.SqlCommand.SqlString IInterceptor.OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
        {
            NHSessionManager.Instance.NHibernateSQL = sql.ToString();
            return sql;
        }
    }

然而，这捕获了没有参数值的 sql 语句.它们被替换为?"

This however captures the sql statement without the parameter values.. They are replaced by '?'

例如:...... WHERE USER0_.USERNAME = ?

Eg: .... WHERE USER0_.USERNAME = ?

到目前为止，我发现的唯一替代方法是使用 log4nets nhibernate.sql appender，它记录包含参数值的 sql 语句，但这对我来说效果不佳..

The only alternative approach i found so far is using log4nets nhibernate.sql appender which logs sql statement including parameter values but that is not serving me well..

我需要使用拦截器，例如.当我捕捉到异常时，我想记录导致持久性问题的特定 sql 语句，包括它包含的值并将其记录在邮件中等.与进入日志文件查找导致问题的查询相比，这大大加快了调试速度..

I need to use an interceptor so that for eg. when i catch an exception i want to log the specific sql statement that caused the persistence problem including the values it contained and log it mail it etc. This speeds up debuging great deal compared to going into log files looking for the query that caused the problem..

如何获取完整的 sql 语句，包括 nhibernate 在运行时生成的参数值?

How can i get full sql statements including parameter values that nhibernate generate on runtime?

推荐答案

这是(粗略的)我所做的:

Here is (roughly sketched) what I did:

1. 创建IDbCommand 接口的自定义实现，它在内部将所有实际工作委托给SqlCommand(假设它被称为LoggingDbCommand代码> 以供讨论).

1. Create a custom implementation of the IDbCommand interface, which internally delegates all to the real work to SqlCommand (assume it is called LoggingDbCommand for the purpose of discussion).

创建NHibernate类SqlClientDriver的派生类.它应该看起来像这样:

Create a derived class of the NHibernate class SqlClientDriver. It should look something like this:

public class LoggingSqlClientDriver : SqlClientDriver
{
    public override IDbCommand CreateCommand()
    {
        return new LoggingDbCommand(base.CreateCommand());
    }
}

在 NHibernate 配置中注册您的客户端驱动程序(有关详细信息，请参阅 NHibernate 文档).

Register your Client Driver in the NHibernate Configuration (see NHibernate docs for details).

请注意，我为 NHibernate 1.1.2 做了所有这些，所以新版本可能需要一些更改.但我想这个想法本身仍然有效.

Mind you, I did all this for NHibernate 1.1.2 so there might be some changes required for newer versions. But I guess the idea itself will still be working.

好的，真正的关键在于您对 LoggingDbCommand 的实现.我只会为您起草一些示例方法实现，但我想您会了解情况并且可以为其他 Execute*() 方法做同样的事情.:

OK, the real meat will be in your implementation of LoggingDbCommand. I will only draft you some example method implementations, but I guess you'll get the picture and can do likewise for the other Execute*() methods.:

public int ExecuteNonQuery()
{
    try
    {
        // m_command holds the inner, true, SqlCommand object.
        return m_command.ExecuteNonQuery();
    }
    catch
    {
        LogCommand();
        throw; // pass exception on!
    }
}

当然，关键在于 LogCommand() 方法，在该方法中您可以完全访问"所执行命令的所有详细信息:

The guts are, of course, in the LogCommand() method, in which you have "full access" to all the details of the executed command:

• 通过m_command.CommandText
的命令文本(其中包含指定的参数占位符)
• 参数及其值到 m_command.Parameters 集合

剩下要做的(我已经完成了但由于合同而无法发布 - 蹩脚但真实，抱歉)是将该信息组合成适当的 SQL 字符串(提示:不要费心替换参数在命令文本中，就像 NHibernate 自己的记录器那样在下面列出它们.

What is left to do (I've done it but can't post due to contracts - lame but true, sorry) is to assemble that information into a proper SQL-string (hint: don't bother replacing the parameters in the command text, just list them underneath like NHibernate's own logger does).

边栏:如果异常被认为是致命的(AccessViolationException、OOM 等)，您甚至可能希望避免尝试记录，以确保您不会通过尝试使事情变得更糟面对已经相当灾难性的事情登录.

Sidebar: You might want to refrain from even attempting to log if the the exception is something considered fatal (AccessViolationException, OOM, etc.) to make sure you don't make things worse by trying to log in the face of something already pretty catastrophic.

示例:

try
{
   // ... same as above ...
}
catch (Exception ex)
{
   if (!(ex is OutOfMemoryException || ex is AccessViolationException || /* others */)
     LogCommand();

   throw;  // rethrow! original exception.
}

这篇关于拦截包含NHibernate生成的参数值的SQL语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！