Tip of the day for NHibernate #2: show_sql and format_sql

January 19, 2011

As always with tips, either you already know this or it would be invaluable. This time I’m talking about 2 configuration properties: “show_sql” and “format_sql”. To switch on these options, add the corresponding lines to your hibernate.cfg.xml file as in the following example:


<?xml version=”1.0″ encoding=”utf-8″ ?>
<hibernate-configuration xmlns=”urn:nhibernate-configuration-2.2″>
  <session-factory>
    <property name=”connection.connection_string”>Data Source=.;Initial Catalog=NHibernateDemo;Integrated Security=True</property>
    <property name=”dialect”>NHibernate.Dialect.MsSql2005Dialect</property>
    <property name=”proxyfactory.factory_class”>NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
    <property name=”show_sql”>True</property>
    <property name=”format_sql”>True</property>
    <mapping assembly=”BusinessLogic”/>
  </session-factory>
</hibernate-configuration>


What is does?


The “show_sql” property tells NHibernate to output all the SQL statements it executes onto the console. This way you can see exactly what NH is doing against your DB. The problem is that it sends every statement as a single line which can be very long and therefore difficult to understand. The “format_sql” solves this problem by formatting the same SQL into several lines in a much more readable fashion.


Note that if your application is not a console app, then you can see the output while debugging in Visual Studio inside the “Output” pane (select “Debug” from the “show output from:” combo box)


For example, without “format_sql”, the output would look like this:


NHibernate: SELECT this_.OrderId as OrderId3_2_, this_.Date as Date3_2_, this_.CustomerSSN as Customer3_3_2_, this_.CashierSSN as CashierSSN3_2_, customer2_.SSN as SSN1_0_, customer2_.FirstName as FirstName1_0_, customer2_.LastName as LastName1_0_, customer2_.ClubMemberId as ClubMemb5_1_0_, cashier3_.SSN as SSN1_1_, cashier3_.FirstName as FirstName1_1_, cashier3_.LastName as LastName1_1_ FROM Orders this_ left outer join Persons customer2_ on this_.CustomerSSN=customer2_.SSN left outer join Persons cashier3_ on this_.CashierSSN=cashier3_.SSN;


And with “format_sql” it would look like this:


NHibernate:
    SELECT
        this_.OrderId as OrderId3_2_,
        this_.Date as Date3_2_,
        this_.CustomerSSN as Customer3_3_2_,
        this_.CashierSSN as CashierSSN3_2_,
        customer2_.SSN as SSN1_0_,
        customer2_.FirstName as FirstName1_0_,
        customer2_.LastName as LastName1_0_,
        customer2_.ClubMemberId as ClubMemb5_1_0_,
        cashier3_.SSN as SSN1_1_,
        cashier3_.FirstName as FirstName1_1_,
        cashier3_.LastName as LastName1_1_
    FROM
        Orders this_
    left outer join
        Persons customer2_
            on this_.CustomerSSN=customer2_.SSN
    left outer join
        Persons cashier3_
            on this_.CashierSSN=cashier3_.SSN;


Note: “format_sql” is working in NH 3.0, but as I read it doesn’t work in previous versions.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*

one comment

  1. LorenzoJuly 14, 2012 ב 12:05

    Which version of Visual Studio and Nhibernate Are you using?
    I’m non able to view SQL in output window.
    I’m using Visual Studio 2008 with NHibernate 3.3.1.4000.

    Reply