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

http://stackoverflow.com/questions/10704462/how-can-i-have-nhibernate-only-generate-the-sql-without-executing-it — Mauricio Scheffer, Jul 01 '13 at 19:56

score 11 · Accepted Answer · answered Aug 12 '09 at 07:47

11

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

answered Aug 12 '09 at 07:47

Vijay Patel

17,094
6
31
35

I like this method, and iv implemented the Interception logic and currently intercept the SaveOrUpdate method, but where do i go from there to catch the sql statement? Invocation.Request...? – furier Sep 28 '12 at 13:36

score 3 · Answer 2 · edited Dec 21 '10 at 13:08

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

score 0 · Answer 3 · answered Aug 12 '09 at 19:55

0

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.

answered Aug 12 '09 at 19:55

Shane Courtrille

13,960
22
76
113

I realise NHibernate Profiler is good at what it does, but if you read the question again I am not after a profiler or looking for potential problems. – Craig Aug 12 '09 at 21:55
Ahhh so you need to see the SQL at runtime? I was thinking you just wanted to see the actual SQL which is what I use NHProf for most of the time. – Shane Courtrille Aug 14 '09 at 14:00

Get executed SQL from nHibernate

3 Answers3

Linked