Will SqlConnection's Dispose Method Interfere

Question

From my understanding, .Net will pool SqlConnection objects where the connection string is the same by default. Will the connection still be pooled if I call the Dispose method?

This question is asked under the context of an ASP.NET application that at times makes many hits to the database in a single PageLoad event. I want the connections to be pooled, but would like confirmation that Closing and Disposing of the connection once the data operation is complete does not interfere with .NET's handling of the connection pool.

Answer 1

From my understanding, .Net will pool SqlConnection objects

No, the SQL provider will pool the underlying connections to SQL Server (with separate pools based on the connection string).

The SqlConnection objects will gain a pooled connection (or create a new one, etc, based on what's configured for pooling, whether a pooled connection is available) when Open is called, and will release the connection when Close or Dispose is called.

The two concepts (SqlConnection objects, and actual connections to SQL Server) are distinct, but obviously somewhat related.

Answer 2

When using connection pooling, closing a SqlConnection simply tells the connection pool that you are done with it. The pool will then decide whether or not to actually close the connection, or to reuse it.

From the MSDN docs:

If the SqlConnection goes out of scope, it won't be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent. If the connection pooling value Pooling is set to true or yes, the underlying connection is returned back to the connection pool. On the other hand, if Pooling is set to false or no, the underlying connection to the server is closed.

Answer 3

The MSDN documentation on SQL Server Connection Pooling says

Connections are released back into the pool when they are closed or disposed

and

We strongly recommend that you always close the connection when you are finished using it so that the connection will be returned to the pool. You can do this using either the Close or Dispose methods of the Connection object ...

Also, it is the connection pooler that will determine if the connections are dropped from the pool

The connection pooler removes a connection from the pool after it has been idle for a long time, or if the pooler detects that the connection with the server has been severed