SqlConnection in .NET -- How can i best harness connection pooling?

Question

I read that .NET uses connection pooling. And, for example, if I instantiate a bunch of SqlConnection objects with the same connection string, then internally .NET will know to use the same connection.

Is this correct? And, in a big web-based application, any tips on the best way to harness this "power" ?

Thanks.

Answer 1

Setting up the TCP connection between your Web application and SQL Server can be an expensive operation. Connection pooling allows connections to the database to be reused for subsequent data requests. Rather than setting up a new TCP connection on each request, a new connection is set up only when one is not available in the connection pool. When the connection is closed, it is returned to the pool where it remains connected to the database, as opposed to completely tearing down that TCP connection.

Always close your connections when you're finished with them. No matter what anyone says about garbage collection within the Microsoft .NET Framework, always call Close or Dispose explicitly on your connection when you are finished with it. Do not trust the common language runtime (CLR) to clean up and close your connection for you. The CLR will eventually destroy the class and force the connection closed, but you have no guarantee when the garbage collection on the object will actually happen.

To use connection pooling optimally, there are a couple of rules to live by. First, open the connection, do the work, and then close the connection. It's okay to open and close the connection multiple times on each request if you have to, rather than keeping the connection open and passing it around through different methods. Second, use the same connection string (and the same thread identity if you're using integrated authentication). If you don't use the same connection string, for example customizing the connection string based on the logged-in user, you won't get the same optimization value provided by connection pooling. And if you use integrated authentication while impersonating a large set of users, your pooling will also be much less effective.

The .NET CLR data performance counters can be very useful when attempting to track down any performance issues that are related to connection pooling.

http://msdn.microsoft.com/en-us/magazine/cc163854.aspx

Answer 2

not sure if this is entirely related, but I just took over a project and noticed the original programming team failed to do something very important.

when you have a SQLConnection, let's call it conn and you do this:

conn.Open();

and then you perform some SQL statement, be it a select, insert or update. it is entirely possible that it will fail. So of course, you should do this:

try { conn.Open() } 
catch (SqlException ex) 
 { 
       //do your logging/exception handling 
 }

however, people forget to add the Finally block.

finally {
       if (conn.State == System.Data.ConnectionState.Open)
         conn.Close();
}

you want to make sure if you have an exception that the connection does not stay open, so make sure you close it.

Answer 3

If you use the following syntax, when ever the using block is left the dispose method will be called, even if an exception occurs.

using(SqlConnection connection = new SqlConnection())
{
    // Work with connection object here.
}

//connection object gets disposed here.