Using Statement and Connection Pooling

Question

I recently came to know the concept of 'connection pooling' in .NET, and as such I have a little doubt I would like anyone to clarify it for me. If I use the following piece of code, when will the database connection be returned to the pool so that it can be used by another part of the application?

using (SqlConnection NewConnection = new SqlConnection(ConnectionString))
{
    using (SqlCommand NewCommand = new SqlCommand("SomeCommand", NewConnection))
    {
        try
        {
            NewConnection.Open();

            // Do some work...

            NewConnection.Close(); // <-- Here?
        }
        catch
        {
            // Error handling...
        }
    }
}

// <-- Here?

Thank you very much.

score 2 · Accepted Answer · answered Jun 06 '11 at 12:12

The connection will indeed be returned to the pool after the using block has finished executing.

The using statement is syntactic sugar - the compiler generates a correct Dispose block which closes the connection, thus returning it to the connection pool.

score 0 · Answer 2 · answered Jun 06 '11 at 12:14

You need to consider the SqlConnection object and the underlying connection as separate. It is the underlying connection that is pooled. This is returned to the pool when the SqlConnection is disposed, either by explicit usage of Dispose(), or by a using block*. Later, a new (different) SqlConnection might be created with the same underlying connection.

So: the magic happens:

using (SqlCommand NewCommand = new SqlCommand("SomeCommand", NewConnection))
{
    ...
} <==== here

*=it might also (I didn't check) be released back to the pool by GC/finalizer - but we shouldn't focus on that because if that happens you're already doing it wrong.

Using Statement and Connection Pooling

2 Answers2

Linked