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.

Answer 4

Use sql server profiler.

EDIT (1 year later): As @Toran Billups states below, the NHibernate profiler Ayende wrote is very very cool.

Answer 5

You can also try NHibernate Profiler (30 day trial if nothing else). This tool is the best around IMHO.

This will not only show the SQL generated but also warnings/suggestions/etc

Answer 6

There is a good reference for NHibernate logging at: How to configure Log4Net for use with NHibernate. It includes info on logging all NHibernate-generated SQL statements.

Answer 7

Nhibernate Profiler is an option, if you have to do anything serious.

Answer 8

If you're using SQL Server (not Express), you can try SQL Server Profiler.

Answer 9

Or, if you want to show the SQL of a specific query, use the following method (slightly altered version of what suggested here by Ricardo Peres) :

private String NHibernateSql(IQueryable queryable)
{
  var prov = queryable.Provider as DefaultQueryProvider;
  var session = prov.Session as ISession;

  var sessionImpl = session.GetSessionImplementation();
  var factory = sessionImpl.Factory;
  var nhLinqExpression = new NhLinqExpression(queryable.Expression, factory);
  var translatorFactory = new NHibernate.Hql.Ast.ANTLR.ASTQueryTranslatorFactory();
  var translator = translatorFactory.CreateQueryTranslators(nhLinqExpression, null, false, sessionImpl.EnabledFilters, factory).First();
  var sql = translator.SQLString;

  var parameters = nhLinqExpression.ParameterValuesByName;
  if ( (parameters?.Count ?? 0) > 0)
  {
    sql += "\r\n\r\n-- Parameters:\r\n";
    foreach (var par in parameters)
    {
      sql += "-- " + par.Key.ToString() + " - " + par.Value.ToString() + "\r\n";
    }
  }

  return sql;
}

and pass to it a NHibernate query, i.e.

var query = from a in session.Query<MyRecord>()
  where a.Id == "123456" 
  orderby a.Name
  select a;

var sql = NHibernateSql(query);

Answer 10

You are asking only for viewing; but this answer explains how to log it to file. Once logged, you can view it in any text editor.

Latest versions of NHibernate support enabling logging through code. Following is the sample code that demonstrates this. Please read the comments for better understanding.

Configuration configuration = new Configuration();

configuration.SetProperty(NHibernate.Cfg.Environment.Dialect, ......);
//Set other configuration.SetProperty as per need
configuration.SetProperty(NHibernate.Cfg.Environment.ShowSql, "true"); //Enable ShowSql
configuration.SetProperty(NHibernate.Cfg.Environment.FormatSql, "true"); //Enable FormatSql to make the log readable; optional.

configuration.AddMapping(......);
configuration.BuildMappings();

ISessionFactory sessionFactory = configuration.BuildSessionFactory();

//ISessionFactory is setup so far. Now, configure logging.
Hierarchy hierarchy = (Hierarchy)LogManager.GetRepository(Assembly.GetEntryAssembly());
hierarchy.Root.RemoveAllAppenders();

FileAppender fileAppender = new FileAppender();
fileAppender.Name = "NHFileAppender";
fileAppender.File = logFilePath;
fileAppender.AppendToFile = true;
fileAppender.LockingModel = new FileAppender.MinimalLock();
fileAppender.Layout = new PatternLayout("%d{yyyy-MM-dd HH:mm:ss}:%m%n%n");
fileAppender.ActivateOptions();

Logger logger = hierarchy.GetLogger("NHibernate.SQL") as Logger;
logger.Additivity = false;
logger.Level = Level.Debug;
logger.AddAppender(fileAppender);

hierarchy.Configured = true;

You can further play with FileAppender and Logger as per your need. Please refer to this answer and this resource for more details. This explains the same with XML configuration; but the same should equally apply to code.