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

Hoye

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.

Oh

I found the SQL to be logged at the DEBUG level so you might wish to ensure your logger level is DEBUG. – Neuburger

My final goal is to read sql from NHibernate, see here another post – Barrens

Configuration settings? Where is that? Wanting to know where to set "show_sql". – Jablonski

this is in hibernate-configuration xml file. mkyong.com/hibernate/… – Genitourinary

The same can be achieved by code with newer versions of NHibernate. I have explained it in my answer. – Ankeny

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 ?

Falstaffian

This does the trick. I would like to add that the new API is sessionFactory.WithOptions().Interceptor(new SQLDebugOutput()).OpenSession(). This way if we have more interceptors aside from this, we can simply chain them after your Interceptor. – Alyce

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.

Turbit

Nice. I forgot about that. click – Freehold

This doesn't show full SQL for me. I see not actual values, something like this: ` SELECT application0_.ApplicationId as app===App1_101_1_, application0_.ApplicationNumberCounty as ApplicationNu2_101_1...` – Braley

Answer 4

Use sql server profiler.

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

Freehold

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

Audiology

Does not work with SQL CE (even though it's advertised as such), just FYI – Partitive

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.

Vorlage

Answer 7

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

Warfourd

Answer 8

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

Cassycast

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);

Unsung

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.

Ankeny