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

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<string> s_queries = new List<string>();
    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:

<log4net>
    <...other config...>

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

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

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