How to configure Fluent NHibernate to output queries to Trace or Debug instead of Console?

How to configure Fluent NHibernate to output queries to Trace or Debug instead of Console? I'm using MsSqlConfiguration.MsSql2008.ShowSql() but it has no parameters and I can't find anything on Google.

I can see from forum and blog posts everywhere that lots of others before me have looked for a way to get the SQL statements as they're being prepared for execution. The answer typically is something along the lines of "you can't", or "you shouldn't".

Whether I should or not, that's what I wanted.

After hours of searching, investigation and failed attempts, and finally I came up with this.

Write up an interceptor:

using NHibernate;
using System.Diagnostics;

public class SqlStatementInterceptor : EmptyInterceptor
{
    public override NHibernate.SqlCommand.SqlString OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
    {
        Trace.WriteLine(sql.ToString());
        return sql;
    }
}

Of course, you don't have to Trace.WriteLine() here, you could write it to a log file, or whatever else you need.

In your connection manager, hook up your Interceptor like so:

protected virtual void Configure(FluentConfiguration config)
{
    config.ExposeConfiguration(x =>
                                   {
                                       x.SetInterceptor(new SqlStatementInterceptor());
                                   });
}

It's not that complicated. From my perspective, certainly easier than trying to get all this XML pushed through Fluent to NHibernate - since Fluent abstracts the XML file away.

Keep in mind, you can only have a single Interceptor - so you may need to integrate this feature with your existing Interceptor, if you already have one. On that note, you might want to give it a broader name - e.g. MyAppInterceptor, so as not to imply a specific purpose, because you may want to add other features to it later.

You probably want to use log4net, not ShowSql. Here is some configuration to send queries to Debug:

  <configSections>
    <section name="log4net"
     type="log4net.Config.Log4NetConfigurationSectionHandler,log4net" />
  </configSections>

  <log4net debug="false">
    <appender name="WindowsDebugOutput" type="log4net.Appender.DebugAppender,
         log4net">
      <layout type="log4net.Layout.PatternLayout,log4net">
        <param name="ConversionPattern"
              value="%d{ABSOLUTE} %-5p %c{1}:%L - %m%n" />
      </layout>
    </appender>

    <logger name="NHibernate.SQL" additivity="false">
      <level value="DEBUG" />
      <appender-ref ref="WindowsDebugOutput" />
    </logger>
  </log4net>

And then call this from your code before opening an NHibernate session:

log4net.Config.XmlConfigurator.Configure();

When you add a reference to the log4net DLL, make sure to set its "Copy Local" property to "true".

This isn't specific to FluentNHibernate, it works the same in any variant of NHibernate.

I have not tried this with SQL Server, but with SQLite, the following code will show generated SQL in the Output window (Debug menu -> Windows -> Output, in VS2008).

The "Show output from:" combo box in the Output window should be set to "Debug" - VS2008 did that for me automatically.

            sessionFactory = Fluently.Configure()
                .Database(SQLiteConfiguration.Standard
                            .UsingFile(DbFile)
                            // Display generated SQL in Output window
                            .ShowSql()
                          )
                .Mappings(m => m.AutoMappings.Add( GetAutoPersistenceModel() ))
                .BuildSessionFactory()
                ;

A word of warning - turning this on can slow down execution considerably.