Capture NHibernate generated SQL Query real-time / at runtime

There are 6 things you need to do before you can capture the SQL Query generated by NHibernate at runtime.

  1. Create a class to store the NHibernate generated SQL Query
  2. Create an Interceptor class that implements the IInterceptor interface
  3. Implement the OnPrepareStatement method and set the NHibernate generated SQL Query created in step 1
  4. Set the Interceptor during the NHibernate configuration startup
  5. Pass the the Interceptor as a parameter to the ISessionFactory.OpenSession method
  6. Retrieve and display the NHibernate generated SQL Query from the class created in step 1

The code required to store the SQL generated by NHibernate functions similar to a global variable:

[sourcecode language="csharp" padlinenumbers="true" autolinks="false" gutter="false" toolbar="false"]
public static class NHSQL
{       
    public static string NHibernateSQL { get; set; }
}
[/sourcecode]

The interceptor should inherit from the EmptyInterceptor class and implement the IInterceptor interface. You will override the OnPrepareStatement method. This method returns an NHibernate.SqlCommand.SqlString and accepts the same as a parameter. Within the method you need to capture the SqlString set it to your property and then return the SqlString that was sent as a parameter.

[sourcecode language="csharp" autolinks="false" gutter="false" toolbar="false"]
public class NHSQLInterceptor : EmptyInterceptor, IInterceptor
{
    NHibernate.SqlCommand.SqlString 
        IInterceptor.OnPrepareStatement
            (NHibernate.SqlCommand.SqlString sql)
    {
        SQL.NHibernateSQL = sql.ToString();
        return sql;
    }
}
[/sourcecode]

When you build your configuration settings and code, include a call to the SetInterceptor method and pass it the above NHSQLInterceptor. In my experience, not certain if it makes a difference, I set all the required NHibernate.Cfg.Environment variables before calling the SetInterceptor method.

[sourcecode language="csharp" autolinks="false" collapse="false" gutter="false" toolbar="false"]
Configuration config new Configuration();
...
...
config.SetInterceptor(new SQLInterceptor());
...
...
ISessionFactory factory = config.BuildSessionFactory();
...
...
factory.OpenSession(config.Interceptor);
...
...
[/sourcecode]

At some location with your system you will want to display or access the NHibernate generated SQL Query. To do this you only need to access the get method of the class you created in step 1.

[sourcecode language="csharp" autolinks="false" gutter="false" toolbar="false"]
string generatedSQL = NHSQL.NHibernateSQL;
[/sourcecode]

And there you have it. Instead of configuring logging and trying to look at the relatively unfriendly log files, you can capture the NHibernate generated SQL Query at runtime and view it immediately. 
Implementing this will still render the NHibernate generated SQL Query in an unfriendly format. To see how to implement a SQL formatter, check out my other blog post here.




Leave a Comment

Your email address will not be published.