Get executed SQL from nHibernate

Question

I am using nHibernate ICriteria to execute a query, and I would like to be able to get the SQL that was executed after the statement runs. So for example I have something like this.

ISession session = NHibernateSessionManager.Instance.GetSession();
DetachedCriteria query = BuildCriteria(); // Goes away and constructs the ICriteria
var result = query.GetExecutableCriteria(session).List<object>()

// somehow here get the sql that was just run
string sql = query.GetSqlSomehow();

I know I can log it and see the sql in the log, but I want to get it immediately after executing the statement so I can display the SQL to the user (even if it doesn't look nice).

Answer 1

You can attach an IInterceptor to your NH ISession, then use the OnPrepareStatement() method to trap (even modify) the SQL.

Answer 2

Personally I use the "NHibernate Profiler" tool for this. It's well worth the price since it also does a good job analyzing your usage of NHibernate and noticing potential problems.

Answer 3

You can use Log4Net configuration to capture the SQL being used. To start you'd need to create a custom appender such as this:

using System; using System.Collections.Generic; using log4net.Appender; using log4net.Core;

public class NHibernateQueryAppender : AppenderSkeleton { private static List s_queries = new List(); private static int s_queryCount = 0;

public static IList<string> CurrentQueries
{
       get { return s_queries.AsReadOnly(); }
}

public static int CurrentQueryCount
{
 get { return s_queryCount; }
}

public static void Reset()
{
 s_queryCount = 0;
 s_queries.Clear();
}

protected override void Append(LoggingEvent loggingEvent)
{
 s_queries.Add(loggingEvent.RenderedMessage);
 s_queryCount++;
}

}

---

Then configure log4net like so: <...other config...>

<appender name="nhquerycheck" type="NHibernateExecutor.Loggers.NHibernateQueryAppender, NHibernateExecutor" />

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

---

The above class can then be queried at runtime such as to display the sql output to screen

---

Edit: for some reason post didn't come out correctly, so found example on web http://nhforge.org/blogs/nhibernate/archive/2008/09/06/how-to-configure-log4net-for-use-with-nhibernate.aspx