8

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.

Jim Aho
  • 9,932
  • 15
  • 56
  • 87
NoAlias
  • 9,218
  • 2
  • 27
  • 46

3 Answers3

11

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.

wsanville
  • 37,158
  • 8
  • 76
  • 101
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

Pero P.
  • 25,813
  • 9
  • 61
  • 85
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.

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
  • regarding your first point this is what I thought also but the MSDN doc I pointed to in my answer seems ambiguous "When a pool is created, multiple connection *objects* are created and added to the pool so that the minimum pool size requirement is satisfied" and "When a SqlConnection object is requested, it is obtained from the pool if a usable connection is available"?? I think this is misleading as it contradicts some points earlier in the docs.. – Pero P. Jul 20 '11 at 17:33
  • @cpedros - yes, I'd say it's misleading - it's conflating "connection objects" (the nebulous underlying connections) and "SqlConnection objects" - `SqlConnection`s are managed objects, and you can create 10000 of them, all sharing a single connection string with Max Pool=100. It will fail when you try to *open* all of them simultaneously. – Damien_The_Unbeliever Jul 20 '11 at 17:36