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

Question

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.

Solution 1

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.

Solution 2

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.

Solution 3

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.

Comments

[Michael Maddox]

The output window in Visual Studio shows Console, Debug, and Trace output. The ShowSql method writes to the Console, not Debug or Trace. Your recommendation is not different from what he has said he is already doing.

[Tom Bushell]

@Michael -I just re-tested this, and it works EXACTLY as described! Possibly a difference in implementation of FNH interface to SQLite vs SQL Server?

[Michael Maddox]

That's not the same "Debug" (notice there is no option for "Trace" or "Console"). :) To see if it is truly writing to Debug, use something like technet.microsoft.com/en-us/sysinternals/bb896647.aspx

[Adam Tal]

Great solution, simple as hell and it just works, added it to my tests session factory although possible to just add with #if DEBUG. Thanks!

[Mike de Klerk]

Maybe its possible to fill in the parameters as well? I get ? (question marks) instead of values in the queries like SELECT * FROM MyObject WHERE Id = ?. I see there is a IEnumerable NHibernate.SqlCommand.SqlString.GetParameters()

[mindplay.dk]

@Mike replace sql.ToString() with base.OnPrepareStatement(sql)

[Mike de Klerk]

Noobish question, but, where to put this configuration file in a winforms project? A lot of Hibernate's documentation websites are down, and I just can't find where to put this XML information. Thanks.

[Michael Maddox]

@Mike - it should go into app.config in the project root directory, which gets copied to the bin directory as <exe name>.exe.config at compile time.

[Mike de Klerk]

Great to have response at a comment on an answer of more than 3 years old! Thanks big time!

[dav_i]

@mindplay.dk I know it's an ancient answer, but base.OnPrepareStatement(sql) still outputs ?s. Any ideas?