Show NHibernate query in the trace of pages

When you use NHibernate and 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 trace engine.

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

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

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

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

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

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

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

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.



Published by

Ricci Gian Maria

.Net programmer, User group and community enthusiast, programmer - aspiring architect - and guitar player :). Visual Studio ALM MVP

2 thoughts on “Show NHibernate query in the trace of pages”

Comments are closed.