Recently we have been introducing NHibernate and a domain layer to an older system that relies primarily on the Enterprise Library Data Access application block and stored procedures for database access.

This has mostly gone pretty smoothly, except in situations where we mix the two strategies inside a transaction. Enterprise Library and NHibernate both manage their own connections and if we try to wrap them both in a TransactionScope it gets promotes to a distributed transaction, causing all sorts of headaches. Wouldn’t it be great if NHibernate and Enterprise Library could just share a single SqlConnection instead?

Luckily NHibernate makes this sort of thing really easy to achieve. All you need to do is write a custom IConnectionProvider that wraps Enterprise Library’s Database.CreateConnection(). Then just drop into your hibernate.xml.cfg:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
  <session-factory>
    <property name="connection.provider">Xyz.EntLibConnectionProvider, Xyz</property>
    <property name="dialect">NHibernate.Dialect.MsSql2005Dialect</property>
    <property name="connection.driver_class">NHibernate.Driver.SqlClientDriver</property>
    <property name='proxyfactory.factory_class'>NHibernate.ByteCode.Spring.ProxyFactoryFactory, NHibernate.ByteCode.Spring</property>
  </session-factory>
</hibernate-configuration>

Note you don’t need a connection.connection_string anymore because it retrieves it from the Enterprise Library’s dataConfiguration section:

<dataConfiguration defaultDatabase="XyzProd">
  <connectionStrings>
    <add name="XyzProd"
         providerName="System.Data.SqlClient"
         connectionString="server=localhost; database=AdventureWorks; UID=user;PWD=word;" />
  </connectionStrings>
</dataConfiguration>

You can grab the code here: EntLibConnectionProvider.cs

Yesterday we had a very strange problem in production with an old sproc-based ASP.NET web application. We had just switched from using a handful of “SqlHelper” classes to the Microsoft Enterprise Library Data Access Application block (DAAB).

Strangely though, after releasing these changes to production, the application became plagued by connection and memory leaks, causing connection pool overflows (over 200 connections to the SQL box at one point) and the IIS application pool to fall over every few minutes.

Memory leaks are notoriously difficult to diagnose. We eventually were able to narrow it down to this offending piece of code, used for running FOR XML queries:

public XmlDocument ExecuteNativeXml(string sprocName, params object[] parameters)
{
    SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();

    using (DbCommand command = db.GetStoredProcCommand(sprocName, parameters))
    {
        XmlDocument document = new XmlDocument();

        using (XmlReader reader = db.ExecuteXmlReader(command))
            document.Load(reader);

        return document;
    }
}

Can you spot the memory leak? Everything that implements IDisposable is wrapped in a using block, and DAAB takes care of opening/closing SqlConnections — our code doesn’t have any contact with them at all. So what’s the problem then?

This article about connection pools in SQLMag from 2003 explains it:

…My test application shows that even when you use this [CommandBehavior.CloseConnection] option, if you don’t explicitly close the DataReader (or SqlConnection), the pool overflows. The application then throws an exception when the code requests more connections than the pool will hold.

Some developers insist that if you set the CommandBehavior.CloseConnection option, the DataReader and its associated connection close automatically when the DataReader finishes reading the data. Those developers are partially right—but the option works this way only when you’re using a complex bound control in an ASP.NET Web application. Looping through a DataReader result set to the end of its rowset (that is, when —the DataReader’s Read method— returns false) isn’t enough to trigger automatic connection closing. However, if you bind to a complex bound control such as the DataGrid, the control closes the DataReader and the connection— but only if you’ve set the CommandBehavior.CloseConnection option.

If you execute a query by using another Execute method (e.g., ExecuteScalar, ExecuteNonQuery, ExecuteXMLReader), you are responsible for opening the SqlConnection object and, more importantly, closing it when the query finishes. If you miss a close, orphaned connections quickly accumulate.

The fix is pretty bit ugly and unexpected — you have to reach inside the DBCommand and explicitly close its connection yourself:

public XmlDocument ExecuteNativeXml(string sprocName, params object[] parameters)
{
    SqlDatabase db = (SqlDatabase)DatabaseFactory.CreateDatabase();

    using (DbCommand command = db.GetStoredProcCommand(sprocName, parameters))
    {
        XmlDocument document = new XmlDocument();

        using (XmlReader reader = db.ExecuteXmlReader(command))
            document.Load(reader);

        // If you do not explicitly close the connection here, it will leak!
        if (command.Connection.State == ConnectionState.Open)
            command.Connection.Close();

        return document;
    }
}

So the bug was not the fault of DAAB, but it was caused by its use of CommandBehavior.CloseConnection — the recommended technique. A nice trap for young players!