C# and the .NET environment have automatic memory management through garbage collection. Coming from C++ I think it’s great to not having to worry about memory deallocation. Unfortunately memory from the managed heap (which is where C# objects are placed)  is just one kind of resource in a system. There are several other types of resources:

• File handles
• Network sockets
• Database connections
• Unmanaged memory

These resources still need to be freed in C#. If the .NET class handling the resource is implemented correctly it will release the unmanaged resource when the object is garbage collected. This is fine as long as it doesn’t matter when the resource is freed. In a program with small memory footprint the garbage collector might never run during the execution of the program, because the low memory usage never triggers a run. In the meantime your program can hold on to an expensive database connection instead of releasing it back to the connection pool for reuse. Eventually the database connection pool is empty and your program (or web site) will start failing.

Risks with Ignoring IDisposable

I made a small test routine that opens a database and runs a simple query:

public static int CountCars()
{
    SqlConnection conn = new SqlConnection(connectionString);
    SqlCommand cmd = conn.CreateCommand();
    conn.Open();
    cmd.CommandText = "SELECT COUNT(1) FROM Cars";
 
    return (int)cmd.ExecuteScalar();
}

Running this once is not a problem, but when I call it from within a loop that repeats 1000 times it eventually fails with an exception:

System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

If there is high load on the system this will block the system from any database calls until the garbage collector eventually collects the unused SqlConnection objects. It could take minutes. By that time the users are furious.

Always call Dispose on Objects Implementing IDisposable

The .NET way to signal that an object holds an expensive resource is that it implements the IDisposable Interface. Whenever you use an object that implements IDisposable you should make sure to call the Dispose() method. Both SqlConnection and SqlCommand do implement IDisposable.

// SqlConnection inherits from DbConnection...
public sealed class SqlConnection : DbConnection, ICloneable
// ...which implements IDispoable
public abstract class DbConnection : Component, IDbConnection, IDisposable
// SqlCommand inherits from DbCommand...
public sealed class SqlCommand : DbCommand, ICloneable
// ...which implements IDisposable
public abstract class DbCommand : Component, IDbCommand, IDisposable

We change our code to call Dispose:

public static int CountCars()
{
    SqlConnection conn = new SqlConnection(connectionString);
    SqlCommand cmd = conn.CreateCommand();
    conn.Open();
    cmd.CommandText = "SELECT COUNT(1) FROM Cars";
 
    int count = (int)cmd.ExecuteScalar();
    if(cmd != null)
      cmd.Dispose();
    if(cmd != null)  
      conn.Dispose();
 
    return count;
}

Now it can be run 1000 times without failure.

Taking Exceptions into Account

The last code example can run 1000 times. Unfortunately it has a problem. It doesn’t handle exceptions correctly. Let’s change the command text to something invalid to get an exception from the database and try again. (For this test the calling code catches the exception and ignores it.)

System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached.

The same problem as before. When the exception is thrown, the function aborts before our calls to Dispose. Time for exception safety:

public static int CountCars()
{
    SqlConnection conn = new SqlConnection(connectionString);
    try
    {
      SqlCommand cmd = conn.CreateCommand();
      conn.Open();
      try
      {
        cmd.CommandText = "SELECT COUNT(1) FROM Carsd";
 
        return (int)cmd.ExecuteScalar();
      }
      finally
      {
        if(cmd != null)
          cmd.Dispose();
      }
    }
    finally
    {
      if(conn != null)
        conn.Dispose();
    }
}

Why two try-finally blocks? The reason is to ensure that the connection is disposed properly even if the CreateCommand() statement throws an exception. This works, but is too much code to write. The using statement comes to the rescue!

using

With the using statement we can rewrite the last code example to something much more compact:

public static int CountCars()
{
    using(SqlConnection conn = new SqlConnection(connectionString))
    using(SqlCommand cmd = conn.CreateCommand())
    {
        conn.Open();
        cmd.CommandText = "SELECT COUNT(1) FROM Carsd";
 
        return (int)cmd.ExecuteScalar();
    }
 
}

This code is equivalent to the manual try-finally constructs above. When compiled they give the same results. If the manual try-finally construct is compiled to IL code and then decompiled by ILSpy it shows up as code>using constructs instead of the original try-finally blocks. The using statement is just a helper offered by the compiler to generate the try-finally blocks for us. I’ve made real production code with 5 using statements on top of each other. I’m glad I didn’t have to write 5 try-finally blocks.

Conclusion

Always call Dispose() on any object that implements IDisposable. Whenever possible, use using to ensure objects are correctly disposed even if exceptions occur.