ADO.net Connection Pooling

Question

In the code below, the Provider object only has one instance of a DbConnection. Each reader will reference the same connection instance. According to the Microsoft documentation, the second reader will get a second connection from the connection pool. This works correctly.

using (var reader1 as IDataReader = Provider.GetReader(sqlStatement1))
{
    while(reader1.Read())
    {
        using (var reader2 as IDataReader = Provider.GetReader(sqlStatement2))
        {
            while(reader2.Read())
            {
            //Do stuff with both statements
            }
        }
    }
}

I would like to the leave the connection open as long as I'm using the Provider object. However, I don't want to waste connections in the connection pool. Would calling Provider.DbConnection.Close() attempt to return both connections to the pool? If so, how could I return the second connection to the connection pool?

Answer 1

I would honestly try to refactor such that you don't need to have two open readers at one time. You're opening yourself up to increasing the chances of deadlocks in your database by holding on to resources for longer than it takes it actually to read.

This a problem sometimes referred to as SELECT 1 + N: for each row returned in the first select, you're performing an additional select. You will need to refactor to fix this, but it all depends on what kind of data you're trying to read.

Answer 2

After testing different connection providers, I've found my answer.

The OleDb provider maintains its connection pool different than the SqlClient provider. generally the SqlClient provider should create a new SqlConnection object each time you want to connect to the database. the connection should be closed or disposed. This releases the underlying connection into the pool.

The OleDb provider however handled differently. Instead of instantiating a new connection each time, the same connection object should be used and disposed at the end of the application. If used the same way as the SqlClient connection, and error is thrown "Unspecified error". Each OleDbCommand can be assigned the same OleDbConnection instance. If it is already being use, the underlying provider will assign a new connection to it. Doing this with the SqlClient provider it will generate an exception.

My original connection string was this: Data Source=....mdb;Provider=Microsoft.Jet.OLEDB.4.0; This turned out to fail after 128 connections... With a very friendly "Unspecified Error". After researching connection pooling (not the error), this parameter needed to be added to turn on connection pooling. OLE DB Services = -1;

With this turned on, the connections behaved as expected and no limit was reached.