There are several items I would like to comment on. First, Cor
Ligthert, you are dead wrong about .Net 1.x. If you use Reflector to
look at the Dispose() method, here is the actual source:
protected override void Dispose(bool disposing)
{
if (disposing)
{
switch (this._objectState)
{
case ConnectionState.Open:
{
this.Close();
break;
}
}
this._constr = null;
}
base.Dispose(disposing);
}
So on SqlConnection, calling Dispose() calls close if the connection is
still open. For the most part in the .Net framework, the Dispose()
implementation also calls close for connections, files, etc, but it is
not guaranteed. Unless you know for _certain_ that Dispose() also
calls Close() (and a properly implemented Dispose() method _will_ close
all unmanaged resources), use caution. For code readability, you could
call Close() and then Dispose(), but if you prefer, it's real easy to
use Reflector to make sure of what the Dispose() is doing. Then you
can code with confidence.
It is very important _not_ to leave connection closing and disposing up
to the GC. Not only will you have connection pooling problems under
load, but your app will use more memory than necessary.
If a class has a finalizer, and GC.SuppressFinalization() isn't called
on it, it cannot be collected from Gen 0. It will always be promoted
to Gen 1 and then clean up from there later when a Gen 1 collection
runs. Yes, at this time the collection will run the Finalizer (which
_should_ call Dispose() if implemented correctly), and your connection
would be closed and memory reclaimed. While you are waiting for a Gen
1 collection, you could up all the connections in the pool. I have
seen this happen, and it's not pretty.
If you dispose an object as soon as you don't need it anymore, you
minimize memory and resource usage. I suggest this approach.
Muzzy, even if you only need 5-10 connections now, plan for more.
Dispose of your connections immediately after using them. Don't wait
for the GC to do it. You don't know how long that will be.
Best regards,
Jeffrey Palermo
Blog:
http://www.jeffreypalermo.com