How do I view the SQL that is generated by nHibernate?

Question

How do I view the SQL that is generated by nHibernate? version 1.2

Answer 1

You can put something like this in your app.config/web.config file :

in the configSections node :

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

in the configuration node :

<log4net>
  <appender name="NHibernateFileLog" type="log4net.Appender.FileAppender">
    <file value="logs/nhibernate.txt" />
    <appendToFile value="false" />
    <layout type="log4net.Layout.PatternLayout">
      <conversionPattern value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n"  />
    </layout>
  </appender>
  <logger name="NHibernate.SQL" additivity="false">
    <level value="DEBUG"/>
    <appender-ref ref="NHibernateFileLog"/>
  </logger>
</log4net>

And don't forget to call

log4net.Config.XmlConfigurator.Configure();

at the startup of your application, or to put

[assembly: log4net.Config.XmlConfigurator(Watch=true)]

in the assemblyinfo.cs

In the configuration settings, set the "show_sql" property to true.

Answer 2

I am a bit late I know, but this does the trick and it is tool/db/framework independent. Instead of those valid options, I use NH Interceptors.

At first, implement a class which extends NHibernate.EmptyInterceptor and implements NHibernate.IInterceptor:

using NHibernate;

namespace WebApplication2.Infrastructure
{
    public class SQLDebugOutput : EmptyInterceptor, IInterceptor
    {
        public override NHibernate.SqlCommand.SqlString
           OnPrepareStatement(NHibernate.SqlCommand.SqlString sql)
        {
            System.Diagnostics.Debug.WriteLine("NH: " + sql);

            return base.OnPrepareStatement(sql);
        }
    }
}

Then, just pass an instance when you open your session. Be sure to do it only when in DEBUG:

public static void OpenSession() {

#if DEBUG
    HttpContext.Current.Items[SessionKey] = _sessionFactory.OpenSession(new SQLDebugOutput());

#else
    HttpContext.Current.Items[SessionKey] = _sessionFactory.OpenSession();
            
#endif
}

And that's it.

From now on, your sql commands like these...

 var totalPostsCount = Database.Session.Query<Post>().Count();
 
 var currentPostPage = Database.Session.Query<Post>()
        .OrderByDescending(c => c.CreatedAt)
        .Skip((page - 1) * PostsPerPage)
        .Take(PostsPerPage)
        .ToList();

.. are shown straight in your Output window:

NH: select cast(count(*) as INT) as col_0_0_ from posts post0_

NH:select post0_.Id as Id3_, post0_.user_id as user2_3_, post0_.Title as Title3_, post0_.Slug as Slug3_, post0_.Content as Content3_, post0_.created_at as created6_3_, post0_.updated_at as updated7_3_, post0_.deleted_at as deleted8_3_ from posts post0_ order by post0_.created_at desc limit ? offset ?

Answer 3

In the configuration settings, set the "show_sql" property to true. This will cause the SQL to be output in NHibernate's logfiles courtesy of log4net.