Show NHibernate query in the trace of aspnet pages

2009, Oct 13 2 mins read

When you use NHibernate and asp.net one of the most interesting stuff is looking at sql generated for each page call. While NHProf is the best tool to accomplish this task, it is interesting to trace issued sql queries using asp.net trace engine.

To accomplish this task is really straightforward thanks to log4net, first of all configure a suitable appender in log4net config file.

1
2
3
4
5
<appender name="AspNetTraceAppender" type="log4net.Appender.AspNetTraceAppender" >
    <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%date [%thread] %-5level %logger [%property{NDC}] - %message%newline" />
    </layout>
</appender>

This appender sends all messages to asp.net trace engine, then enable tracing in the web.config.

1
 <trace enabled="true" pageOutput="true" requestLimit="100" localOnly="true" />

Now you need to configure log4net to issue all nhibernate logs to the previous appender.

1
2
3
4
5
6
7
8
9
<logger name="NHibernate" additivity="false">
    <level value="INFO" />
    <appender-ref ref="AspNetTraceAppender" />
</logger>

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

I keep the standard level of NHIbernate to INFO, but the NHibernate.SQL to DEBUG. The result is the following.

Et voila, you get all the query that were issued to the database in the trace of your page, without the need to attach debugger or trace or whatever else. This is especially interesting if you use continuous integration and all testers usually work with latest site version running on test server. With such a facility everyone can look at what is really happening to the database.

alk.

Tags: NHibernate