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

lfoulkrod_1642688 0
Viewed 40482

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

nhibernate
10 Answers

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.

mathieu_9710

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.

Ian_Nelson_20840

Use sql server profiler.

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

IainMH_11220
Sean_Carpenter_7290

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

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.

user_57797320
Related