How do I enable "prepared statements" in NHibernate using SQL Server 2005 and how do I verify it?

Question

My configuration looks like this:

<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
  <session-factory name="kvws.kist.suche.dbadapter.nhentities">
    <!-- Driver -->
    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>

    <!-- DB-Connection settings -->
    <property name="connection.connection_string">
    ...
    </property>

    <property name="adonet.batch_size">10</property>
    <property name="show_sql">true</property>
    <property name="format_sql">true</property>
    <property name="prepare_sql">true</property>
    <property name="command_timeout">0</property>

    <!-- other settings -->
    <!-- using a proxy is mandantory, LinFu is easiest to setup (no setup...) -->
    <property name="proxyfactory.factory_class">NHibernate.ByteCode.LinFu.ProxyFactoryFactory, NHibernate.ByteCode.LinFu</property>
  </session-factory>
</hibernate-configuration>

I was under the impression that the setting of prepare_sql ensured the use of prepared statements in NHibernate. However, It does not "feel" that way.

How can I test/see if "prepared statements" are used?

Is the above the correct setting for use of "prepared statements"?

Could other settings interfere with the execution?

Answer 1

Your configuration file looks correct to me. I know of no other setting that will override the prepare_sql value. The only way I know of to prove that it's working correctly is to use Sql Profiler to see if the execution plan is being cached / reused.

One thing to note, you'll want to specify the size of your parameters that are of variable length (varchar etc...). See this article for more about that: http://nhforge.org/wikis/howtonh/tuning-queries-with-ms-sqlserver.aspx