Using, Dispose, Close and Connections
Please note that my explanation in this post is incorrect. I am sorry if I caused any trouble.
In .NET 2.0 disposing the SqlConnection DOES return the connection into the connectionpool.
In my experience .NET 1.1 acted differently; I will try to investigate this and let you know.
In many talks, tutorials and books you will find C# code that explains the use of using like this:
using(SqlConnection connection = new SqlConnection(connectionString))
{
…
}
Whenever and however execution leaves this using block (power outage disregarded) the connection will be closed because when leaving a using block the Dispose method will be called on the connection object.
The MSDN states[1]:
SqlConnection.Dispose Method
Releases the resources used by the SqlConnection.
And poses this example:
public void SqlConnectionHereAndGone(){
SqlConnection myConnection = new SqlConnection(“…”);
myConnection.Open();
//Calling Dispose also calls SqlConnection.Close.
myConnection.Dispose(); }
All this leads many developers to think that it doesn’t matter whether you call Dispose or Close.
BUT IT DOES!
Dispose looks like this (thanks to Reflector[2]):
protected override void Dispose(bool disposing)
{
if (disposing)
{
this._userConnectionOptions = null;
this._poolGroup = null;
this.Close();
}
this.DisposeMe(disposing);
base.Dispose(disposing);
}
Yes it calls Close but: it also calls base.Dispose which releases all claimed resources INCLUDING the unmanaged resources that keep the connection in the connection pool alive.
Lessons to be learned:
Use Close and Dispose depending on the situation.Do NOT explain the using keyword by using an example that shows a DbConnection.
It’s great to be a trainer and a geek[3].
[1] SqlConnection.Dispose
[2] Reflector
[3] Geek