.NET SqlConnection class, connection pooling and reconnection logic

Question

We have some client code which is using the SqlConnection class in .NET to talk to a SQLServer database. It is intermittently failing with this error:

"ExecuteReader requires an open and available Connection. The connection's current state is Closed"

The "temporary" solution is to reboot the process, after which everything works - however, that's obviously unsatisfactory.

The code is keeping a cache of SqlConnection instances, one for each database.

We'd like to re-write the code, but before I do, I need to know a few things:

My first question is: Is it inefficient to repeatedly connect and disconnect SqlConnection objects, or does the underlying library perform connection pooling on our behalf?

// Is this bad/inefficient?
for(many-times)
{
    using(SQLConnection conn = new SQLConnection(connectionString))
    {
        // do stuff with conn
    }
}

Because our code does not do the above, what seems the likely cause of the problem is that something happens to the underlying SQLServer database during the "lifetime" of the connection that causes the connection to be closed...

If it turns out that it is worthwile to "cache" SqlConnection objects, what is the recommended way to handle all errors that could be resolved simply by "reconnecting" to the database. I'm talking about scenarios such as:

• The database is taken offline and brought back online, but the client process had no open transactions while this was happening
• The database was "disconnected", then "reconnected"

I notice that there is a "State" property on SqlConnection... is there an appropriate way to query that?

Finally, I have a test SQLServer instance set up with full access rights: how can I go about reproducing the exact error "ExecuteReader requires an open and available Connection. The connection's current state is Closed"

Answer 1

No, it's not inefficient to create lots of SqlConnection objects and close each of them when you're done. That's exactly the right thing to do. Let the .NET framework connection pooling do its job - don't try to do it yourself. You don't need to do anything specific to enable connection pooling (although you can disable it by setting Pooling=false in your connection string).

There are many things that could go wrong if you try to cache the connection yourself. Just say no :)