Logging NHibernate SQL queries

Question

Is there a way to access the full SQL query, including the values, inside my code?

I am able to log SQL queries using log4net:

<logger name="NHibernate.SQL" additivity="false">
    <level value="ALL"/>
    <appender-ref ref="NHibernateSQLFileLog"/>
</logger>

However, I would like to find a way to log SQL queries from the code also. This way I will log the specific SQL query that causes an exception in my try/catch statement.

Right now I have to data-mine the SQLFileLog to find the query that caused the exception when an exception occurs and it is not efficient.

Answer 1

Either use sql profiler or have a look at nhprof at http://nhprof.com/

Both will let you see sql output.

Also set the show_sql property in hibernate config file

<property name="show_sql">true</property>

Answer 2

You can override driver:

public class LoggerSqlClientDriver:SqlClientDriver, IEmbeddedBatcherFactoryProvider
{      
    public override void AdjustCommand(IDbCommand command)
    {
        //log here
        base.AdjustCommand(command);
    }

    //protected override void OnBeforePrepare(IDbCommand command)
    //{
    //    //log here
    //    base.OnBeforePrepare(command);
    //}
}

And then use it in configuration:

var config = Fluently.Configure().
            Database(MsSqlConfiguration.MsSql2005.Driver<LoggerSqlClientDriver>();

Answer 3

you can use an interceptor to do this:

public class LoggingInterceptor : EmptyInterceptor {
    public override SqlString OnPrepareStatement(SqlString sql) {

        Debug.WriteLine(sql);

        return sql;
    }
}

See Nhibernate Docs for the different ways to register it with nhibernate.

Answer 4

Use a log4net appender with specific target (afair it supports being toggled on/off) or just extend it and toggle it within your try-catch-finally-off.