Will there be a SqlConnection Dispose Interfere method with a connection pool?

Question

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

This question is asked in the context of an ASP.NET application that sometimes makes many database calls in a single PageLoad event. I want the connections to be merged, but I would like to confirm that closing and deleting the connection after the data operation is complete does not interfere with the processing of the .NET connection pool.

Answer 1

When using the connection pool, closing SqlConnection simply tells the connection pool that you are working with it. The pool then decides whether to really close the connection or reuse it.

In the MSDN docs :

If the SqlConnection is out of scope, it will not be closed. Therefore, you must explicitly close the connection by calling Close or Dispose. Close and Dispose are functionally equivalent. If the pool pool is set to true or yes, the underlying connection is returned back to the connection pool. On the other hand, if the pool is set to false or not, the basic connection to the server is closed.

Answer 2

MSDN documentation on SQL Server Connection Pool says

Connections are brought back to the pool when they are closed or located

and

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

It is also a connection pool that will determine if connections are removed from the pool

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

Answer 3

From my understanding, .Net will merge SqlConnection objects

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

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

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