Logging SQL Statements generated by NHibernate

Asked how to log the SQL statements generated by NHibernate, the internets answer: define a log4net appender, since NHibernate uses log4net.

You need to add nhibernate and log4net sections to your app.config (or web.config) file:

<configuration>
  <configSections>
    <section name="nhibernate" type="System.Configuration.NameValueSectionHandler,
      System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089" />
    <section name="log4net"
      type="log4net.Config.Log4NetConfigurationSectionHandler,log4net"/>
  </configSections>

These <section> entries allow to add user-defined tags under the <configuration> tag.

For NHibernate, we need to activate logging:

<nhibernate>
  <add key="hibernate.show_sql" value="true" />
</nhibernate>

Then we define appenders for log4net:

<log4net>
  <appender name="DebugSQL" type="log4net.Appender.FileAppender">
    <param name="File" value="c:\projects\nhlog\nhsql.log"/>
    <param name="AppendToFile" value="true" />
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern 
        value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
    </layout>
  </appender>
  <logger name="NHibernate.SQL" additivity="false">
    <level value="DEBUG" />
    <appender-ref ref="DebugSQL" />
  </logger>

  <appender name="DebugNH" type="log4net.Appender.FileAppender">
    <param name="File" value="c:\projects\nhlog\nh.log"/>
    <param name="AppendToFile" value="true" />
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern 
        value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
    </layout>
  </appender>
  <logger name="NHibernate" additivity="false">
    <level value="ALL" />
    <appender-ref ref="DebugNH" />
  </logger>
</log4net>

Somewhere in your startup code, initialize log4net to read from app.config:

log4net.Config.XmlConfigurator.Configure();

The NHibernate.SQL logger will only receive generated SQL statements. The NHibernate logger receives every log message. Output is always filtered by the level parameter.

Thanks to the internet goes here, here, here, here, and here.

I have previously written about tracing SQL Statements generated by Entity Framework, and where I saved every SQL statement to a separate file. To accomplish this, one would need to implement their own log4net appender, I guess.