The Very Handy "Using" Statement

I’ve lost count how many times I had to refactor code that looks like this:

SqlConnection conn = new SqlConnection (...);
SqlCommand cmd = new SqlCommand (...);
// ... 
conn.Open();
cmd.ExecuteNonQuery ();
// ...

I rejoice if such code has conn.Close() at the end. What happens if an exception is thrown in ExecuteNonQuery(), for example? Your database connection stays open for a while because, well, nothing closed it. Keep doing this, and you exhaust SQL Server’s connection pool. Besides leaking resources, your application becomes unresponsive.

There’s an easy way out: the using statement (MSDN):

using (SqlConnection conn = new SqlConnection (...))
using (SqlCommand cmd = new SqlCommand (...))
{
    // ... 
    conn.Open();
    cmd.ExecuteNonQuery ();
    // ... 
}

The code above expands into something like this (“note the extra curly braces to create the limited scope for the object”):

{
    SqlConnection conn = new SqlConnection (...);
    try 
    {
        SqlCommand cmd = new SqlCommand (...);
        try 
        {
            conn.Open();
            cmd.ExecuteNonQuery();
        }
        finally 
        {
          if (cmd != null)
              cmd.Dispose ();
        }
    }
    finally 
    {
      if (conn != null)
          conn.Dispose ();
    }
}

In other words, you get a try-finally block for free. You still end up with an exception (notice the lack of catch there?), but the finally clause is guaranteed to dispose of expensive resources properly.

Naturally, this trick works only with classes which implement IDisposable. However, IDisposable comes with a lot of baggage, so don’t rush to implement it all over the place.

As a side benefit, calling Dispose() on SqlConnection also closes it.

To me, this is unwelcome coupling because it exposes me to the inner workings of the SqlConnection class—something I shouldn’t worry about. If I call a method, thinking, “Oh, and it also calls Xxxxx() inside”, I see it as bad API design.

A neat trick

Here’s a neat trick I picked up from Bill Wagner’s More Effective C#. Look at this code:

public void GetThingsDone ()
{
  T driver = new T();
  using (driver as IDisposable)
  {
     driver.DoWork();
  }
}

In Bill’s words:

The compiler creates a hidden local variable that stores a reference to the driver cast as an IDisposable. If T does not implement IDisposable, then the value of the local variable is null. In those cases, the compiler does not call Dispose(), because it checks against null before doing extra work. However, in all cases where T implements IDisposable, the compiler generates a call to the Dispose() method upon exiting the using block.

We saw the null check in finally above, but the interesting point here is that if a soft cast of IDisposable produces a null, the compiler just bails. Pretty cool!

Wow! That's great it save you time from non stop writing try catch statements. Thnx for the great tip.

Don't be too harsh on code like this - .Net 1 SqlConnection didn't implement IDisposable, so the using() statement was pointless.

Rich, good point! I guess nobody goes back to refactor that code.

There's something peculiar about old data-access code---it always calls for a lot of refactoring.

What is your take on this:
using (var sdc = new siteDataContext())
{
var Something = from p in sdc.properties
where p.ID == "LANT"
select p;

foreach (var thing in Something)
{
literalOutput.Text = thing.Name;
}
}

Sorry, in the previous example siteDataContext is LINQ to SQL. And I'm wondering if the using statement is of any use in this case.

Mark, I always put a Ling-to-SQL context in a using(....) block. To me, it's a big black box, so I'd rather it cleaned up after itself.

Yes, I also always contain my LINQ to SQL data contexts in a using block but recently I ran into an issue with needing the same data context in two methods and it threw me off a bit because it was disposed before it was used the second time. It just got me considering if I was just being overly cautious.

This blog post is the very reason I began using the using block every time. Thanks for that. :)

Ah, I see. Yes, the code above would dispose of the context. If you need to use it in two methods, you'd need to put using(...) elsewhere.