Overview

The full value of the C# using statement is often underrated and underused.

The Common Problem

Although a managed language like C# typically handles managing memory and other resources on your behalf there are still certain objects you need to be careful with. One of these that almost any web programmer is aware of is a database connection object. Since these objects are often pooled and therefore not immediately destroyed, a consistent design pattern must be used for cleanup. Forget to call the Close method after calling the Open method or forget to call the close on a reader – the object will stick around taking up server resources.

Typical Design

If we were coding a data fetch we'd probably code something like:

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = connection.CreateCommand();

command.CommandText = "mysp_GetValue";
command.CommandType = CommandType.StoredProcedure;

connection.Open();
object ret = command.ExecuteScalar();
connection.Close();

In this case we've correctly released the connection when we're done with it. However what if something goes wrong while executing the query? A big fat exception will be thrown and the Close method will never be called. If this code is in a web page every time someone hits the page we're going to open another connection to the database. If you've never had the joy of this happen to you on a busy production server you're really missing out my friend.

So many designs offered out there offer the "proper" way to do this:

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = connection.CreateCommand();

command.CommandText = "mysp_GetValue";
command.CommandType = CommandType.StoredProcedure;

object ret = null;

try
{
    connection.Open();
    ret = command.ExecuteScalar();
}
finally
{
    if (connection != null)
        connection.Close();
}

There, we've now done our duty and the code is now safe. Regardless of any pesky exceptions the SqlConnection will get closed after it's been opened. We couldn't do any better, right?

Suppose some clever coder comes along and does this:

SqlConnection connection = new SqlConnection(connectionString);
SqlCommand command = connection.CreateCommand();

command.CommandText = "mysp_GetValue";
command.CommandType = CommandType.StoredProcedure;

object ret = null;

try
{
    connection.Open();
    ret = command.ExecuteScalar();
}
finally
{
    EmailerClass.EmailMe("ret = " + ret.ToString() + "!!!");

    if (connection != null)
        connection.Close();
}

The late night working coder has add some functionality. However what happens when ret is null or something goes wrong in the EmailerClass.EmailMe method? An exception is thrown that prevents the connection from being closed.

Using Using

The answer to these problems is the using statement. This statement guarantees that resources are properly cleaned up and that they're cleaned up in the most timely manner. Recoded with the using statement our code block would look like:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    SqlCommand command = connection.CreateCommand();

    command.CommandText = "mysp_GetValue";
    command.CommandType = CommandType.StoredProcedure;

    connection.Open();
    object ret = command.ExecuteScalar();
}

The big change here is that we're wrapping the instantiation of our resource critical object in the "resource-acquisition" part of a using statement. The curly braces after this define the scope of the object. When the object goes out of scope it is automatically cleaned up. In the case of our SqlConnection object, it's connection will be closed. This is true regardless of any exception that gets thrown. 

There you have it - a database connection object being used with no call being made to it's Close method.  This implies that only objects that implement IDisposable can be used in a using statement, which is important to note. It is also important to note that calling Dispose explicitly (or implicitly via a using statement) can have performance benefits. As MSDN states: "Code that is using a resource can call Dispose to indicate that the resource is no longer needed. If Dispose is not called, then automatic disposal eventually occurs as a consequence of garbage collection." Yet another reason to use using statements.

What if you write a custom class that is consuming an expensive resource?  Have your class implement IDisposable and in the Dispose method call the Dispose method of the object you are using.

Thank You for listening.