Setup log4net to view the parameterized SQL generated by NHibernate

Published on October 06, 2009 by Toran Billups

When I first started working with NHibernate I was able to view the actual SQL it generated using a commercial tool called NHibernate Profiler. But for some reason I always wanted to see if I could view the same thing using an open source tool like log4net.

To start you will need to add a reference to the log4net assembly that is included with the NHibernate assemblies you downloaded already. Next add the following in your configuration file to ensure log4net is available at runtime.

1
  <section name='log4net' type='log4net.Config.Log4NetConfigurationSectionHandler,log4net' />

Next up is the configuration required by log4net to tell it exactly how you want this information logged, and how much detail you want to see in the log itself. In this example I simply log to a file, but if you want to log this information to the console or output window you could do that as well.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
  <log4net>
    <appender name='rollingFile' type='log4net.Appender.RollingFileAppender,log4net' >
      <param name='File' value='log.txt' />
      <param name='AppendToFile' value='true' />
      <param name='DatePattern' value='yyyy.MM.dd' />
      <layout type='log4net.Layout.PatternLayout,log4net'>
        <conversionPattern value='%d %p %m%n' />
      </layout>
    </appender>

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

And the final configuration change required is in the NHibernate section, toggle the 'show_sql' property to true.

1
  <property name='show_sql'>true</property>

Now because I'm not using the default location for configuration I need to tell log4net where to find it. In the example below I'm using a web.config file. So in the global.asax 'Application_Start' method I would manually configure log4net with something like the below.

1
2
3
4
5
6
7
public void Application_Start()
{   
    string basePath = AppDomain.CurrentDomain.SetupInformation.ApplicationBase;
    string fullpath = Path.Combine(basePath, 'Web.config');
   
    XmlConfigurator.ConfigureAndWatch(new FileInfo(fullpath));
}

This provides log4net with the information needed to configure it. In this example we are appending to a file named 'log.txt'. After you run this and actually do something with NHibernate that generates parameterized SQL, it will be logged so you open this text file and view exactly what was sent to SQL Server.

This post doesn't cover all the great things that you can do with log4net, but it provides a simple way to show what is happening behind the scenes if you don't have a license for something like NHProf.

As always, the source code for this sample is available for download.