NHibernate SQL Logger

As a traditional SQL 'backend' developer, I've always relied on SQL Profiler to let me know what is happening in the back end at any given point, but now that I'm using NHibernate for our persistence layer, I've found that the SQL that is generated is not very readable. If you've used NHibernate you'll know what I mean!

Besides the aliased fields, I don't like the fact that the entire statement is packed onto one line, and is using the sp_exec statement for parameter passing - Not very readable.

In order to remedy this, I configured an appender within log4net that allows the SQL to be pushed to its own file, with the following config:

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

where "nh" is just a standard Log4Net file appender. This works, but still means I have to dissect the SQL and reformat it as I go.

Of course, this is not ideal, so I have decided to write a small log4net appender that formats this output. The only problem is that I will need to write an SQL formatter! Not trivial.





In the interim, I have written a rough 'formatter' which simply uses find and replace to get the SQL slightly readable.  It places each field on its own line, and creates distinct blocks for each join.

SELECT
    FieldA,
    FieldB,
    FieldC
FROM dbo.Table1 AS T1
JOIN dbo.Table2 AS T2
  ON T1.Field1 = T2.Field1
  
JOIN dbo.Table3 AS T3
  ON T2.Field2 = T3.Field2
  
WHERE T3.Field4 = 1
  AND T2.FieldA <> 0

It’s not perfect, and will place new lines in regardless of whether they are necessary, but it does substitute parameters so generally it does the job!

If you happen to read this and think this sounds like something you *must* have, let me know.. It’s quite easy to use, simply reference the assembly, and place this into the log4net config:

    <appender name="sqlFormatter" type="NHibernate.Appender.NHibernateAppender,NHibernate.Appender">
      <param name="FileName" value="C:\Logs\MatrixSQL.log"/>
    </appender>

and then reference it accordingly within the NHibernate.SQL logging level:

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

The next step will be to build a reasonable SQL parser, so that I can create a syntax tree of the statements in order to present them neatly. 

To get this happening I will need a tokeniser (lexer), a grammar parser, and a formatter.  Sounds like an interesting project. I’ll make this project available on GoogleCode at some point, assuming that I’m making progress.