Geeks With Blogs
Łukasz Kuryło's blog

After done mapping for my entities I wanted to test the basic CRUD operations. I stuck with a problem of inserting a data to tables in many to many relation, so I wanted to look at the generated sql by the NHibernate to find out, what is really generated to investigate the problem in my mappings. The NHibernate have a property called show-sql to show the generated statements.

I enabled it in the config:

var configure = new Configuration();
            configure.DataBaseIntegration(db =>
                db.LogFormattedSql = true;
                db.LogSqlInConsole = true;
                db.AutoCommentSql = true;

To set the output to the build-in console in Visual Studio I used a Maciej Aniserowicz solution.

His post is all in polish language, so a little clarification.

Every line of text send to System.Diagnostics.Debug.WriteLine(..) is going to the VS console. So Maciej added a simple wrapper for it:

public class CustomDebugWriter : TextWriter
        public override void WriteLine(string value)
        public override void Write(string value)
        public override Encoding Encoding
            get { return Encoding.UTF8; }

At the beginning of the application there must be placed a

            Console.SetOut(new CustomDebugWriter());

to set the new class responsible for operating on the output set to the console.


Running that code results ALMOST in that way I wanted to. Generating sql is placed in the VS console, but only for sql SELECT statements and I didn't know why (really I still don't ;).

So I decided to use the log4net logging library to get the result in the file at least. After adding a reference to this library and set the framework target there must be done two things. One is placed in app.config/web.config the configuration for the library. I found somewhere a little config which work ok:

    <section name="log4net" type="log4net.Config.Log4NetConfigurationSectionHandler,log4net"/>
    <appender name="NHibernateFileLog" type="log4net.Appender.FileAppender">
      <file value="logs/nhibernate.txt"/>
      <appendToFile value="false"/>
      <layout type="log4net.Layout.PatternLayout">
        <conversionPattern value="%d{HH:mm:ss.fff} [%t] %-5p %c - %m%n"/>
    <logger name="NHibernate.SQL" additivity="false">
      <level value="DEBUG"/>
      <appender-ref ref="NHibernateFileLog"/>


And second is to add at the beginning of the application a single line



In this moment we have all, including INSERT and UPDATE statements in the logs/nhibernate.txt file. But this is not all.


When we now check the VS console, we can see that we have there all the logs sended to the file, that means the INSERT/SELECT/UPDATE statements, so using this log file is useless :)


Don't know, why after added and configured the log4net library is started to work as I expected, but I'm glat it did :)

Posted on Tuesday, July 12, 2011 11:36 PM NHibernate | Back to top

Comments on this post: showing a sql generated by NHibernate on the VS build-in console

Comments are closed.
Comments have been closed on this topic.
Copyright © Łukasz Kuryło | Powered by: