Suspended With Pay

Displaying NHibernate SQL in Visual Studio’s Output Window

Posted by rjester on April 3, 2009

I am creating an ASP.NET MVC application that uses NHibernate to pull data from SQL Server. I wanted to see the generated SQL statements in Visual Studio’s Output Window as I debug the code. NHibernate contains a property, show_sql which should do what I need, right?  Not exactly.  The SQL statements are displayed when I run my unit tests, but I want to see them as I step through the code.

The answer lies in the log4net configuration. A TraceAppender should be used to direct messages to the Output Window.  Add an appender of type, log4net.Appender.TraceAppender to the web.config. My appender looks like this:

    <appender name="DebugSQL" type="log4net.Appender.TraceAppender">
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date &#91;%thread&#93; %-5level %logger &#91;%property{NDC}&#93; - %message%newline" />

NHibernate logs all SQL statements using a logger named, NHibernate.SQL.   Add a logger to the web.config.  The name must be NHibernate.SQL, and  DEBUG must be assigned to the level for this to work.  My logger looks like this:

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

Now I can step through my code and see the SQL statements that are generated by NHibernate.


5 Responses to “Displaying NHibernate SQL in Visual Studio’s Output Window”

  1. Andy said

    nice post!

    Your appender tag must have the name “NHibernate.SQL”, otherwise nothing happens 😉

  2. Scott said

    Many thanks. Except for the appender having the wrong name, it worked for me.

    • Justin said

      I think I should help clarify some of the comments. This works great as long as the tag is fixed to (ref should match name of the appender whatever you choose to name it). Thank you for this post because I couldn’t figure out why I wasn’t getting any logging in my Visual Studio Output pane even though I had a console appender defined ().

  3. hardywang said

    In this case, shouldn’t your logger looks like

  4. developer said


Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: