So it has been asked here around the office and I’ve heard it in many discussions. So I decided I would do a deep dive into when and why to use each.
For the code examples, these are taken from .Net Reflector Pro. The framework version I will be using is .Net Framework 4.
First lets look at a simple T-SQL Operation using a SqlConnection and SqlCommand object. We will also be implementing the using statement.
So our code example starts out with a simple SELECT statement that retrieves the hostname from the database server.
using (SqlConnection oConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI"))
{
SqlCommand oCmd = new SqlCommand("Select HOST_NAME()", oConn);
oCmd.CommandType = System.Data.CommandType.Text;
oConn.Open();
string hostName = oCmd.ExecuteScalar().ToString();
}
Now in implementing this, it would appear as though we are leaving our SqlConnection both open and undisposed. However this is not the case.
As outlined in the documentation for the using statement. The using statement ensures the correct use of IDisposable objects. So what is actually happening is that when your code is leaving the using block the Dispose method is being called. So lets take a quick gander at the Dispose method of the SqlConnection object.
protected override void Dispose(bool disposing)
{
if (disposing)
{
this._userConnectionOptions = null;
this._poolGroup = null;
this.Close();
}
this.DisposeMe(disposing);
base.Dispose(disposing);
}
Now you’ll notice that the Dispose method contains a parameter of disposing. However when the Dispose() method is called from the using statement no parameter is passed. So lets actually look at the base dispose method. For this we actually have to go down to the base class of System.ComponentModel.Component.
public void Dispose()
{
this.Dispose(true);
GC.SuppressFinalize(this);
}
So here is where the magic happens, so you see that when the Dispose method is called, it turns around and calls the Dispose() method with the true parameter. When the Dispose method is called with a true parameter [Dispose(true)] the User Connection Options and Pool Group are both set to null, and then the connection is closed, and then, the resources are released.
In summary, explicitly calling close before dispose, implements double work. It shows up in some patterns and design books as being the proper way to handle this scenario, however it is not.
So when do we use the close, we’ll that’s simple, when we need to reopen the connection later, in an enumeration for example.
