In my experience it is better for developers who do not understand database connection pooling to not use it than to use it incorrectly. In many of the new frameworks this is handled for you, the creating and tearing down of connections, and the return of a closed connection object to the connection pool. In those instances when you roll your own it is essential for you to understand how they work.
I continue to work with customers having database performance issues, thinking the problem is in their TSQL code or the database server configurations. I’m no longer surprised to find an enormous number of open connections to the database because they do not understand how connection pooling works. Connections remain open and locks are held on SQL Server objects, blocking other transactions from being executed. The longer the blocking continues, the worse the situation becomes.
In short, an ADO.Net connection is not returned to the pool until the connection is closed. While this may seem counter intuitive, it is how connection pooling works. It is the act of closing the connection that returns it to the pool. ADO.Net connection pooling handles the event, and rather than closing the connection instance from the database server, it simply places it back into the pool. The next time a connection is needed, if it has the same connection string, it may be re-used without negotiating and establishing a connection with the database server. However, if the needed connection string is different, and no other connection objects are available, the existing connection is closed and a new connection is created.
If you are unsure about the behavior on your system, it is safer to not use connection pooling at all than to use it incorrectly. Simply have your application create a new connection every time. No matter what, always close database connections when your work is completed or rolled back.
Regardless of using or not using connection pooling, you best bet is to use the USING keyword in C#, creating and enclosing connection activity in the USING clause. In this fashion, even if you forget to close your connection, it will automatically be closed and deallocated, or returned to a connection pool, once the using phrase completes.
USING (var conn = new SQLConnection(connectionString))
{
…
}
Following the last } the connection is now closed or returned to the pool.
A final note, you turn connection pooling off in the sql server connection string. Find out how to do this at connectionstring.com if you are not sure.
Cheers,
Ben