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:

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

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

For NHibernate, we need to activate logging:

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

Then we define appenders for 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">
        value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
  <logger name="NHibernate.SQL" additivity="false">
    <level value="DEBUG" />
    <appender-ref ref="DebugSQL" />
  <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">
        value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
  <logger name="NHibernate" additivity="false">
    <level value="ALL" />
    <appender-ref ref="DebugNH" />

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


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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: