Ado.Net - How to use connection pooling?

Question

.Net allows connection pooling which based on what I've read is simply by adding parameters to App.config

The question is, am I suppose to do anything in my code to use the connection pool? In my code I open a connection every time data is needed and I close it as soon as I'm done. Am i suppose to do anything special to reuse connections?

Answer 1

You don't need to do anything special as long as your connections use the same connection string. Use the connection, close it and will automatically return to the pool.

From SQL Server connection pooling:

Connections are pooled per process, per application domain, per connection string and when integrated security is used, per Windows identity. Connection strings must also be an exact match; keywords supplied in a different order for the same connection will be pooled separately.

You can configure certain pool related options in the connection string itself:

• Pooling (enabled by default)
• Connection Lifetime (or Load Balance Timeout)
• Enlist
• Max Pool Size
• Min Pool Size

Answer 2

The point is to not do anything to re-use connections :) If you store the connection and re-use it, the pool is defeated.

A good pattern is to take advantage of IDisposable and using. For the ado.net connection classes, dispose calls close. If you do that, you can't go wrong.

using (conn = new SqlConnection(...))
{
    // use the connection
}