Your Friend the C# Using Statement

May 2007

Overview

The full value of the C# using statement is often underrated. This article will describe what this statement does and why you should use it often. Note that this article is discussing the using statement in the context of defining object scope and disposal, not of namespace inclusion (i.e. not "using System.Data;").

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 if you're not careful to clean up the resources they reference you'll have problems. Forget to call the Close method after calling the Open method and you're just asking for trouble.

This is where the using statement comes in. In this article I'm going to make the bold suggestion that you never call the Close method of a database connection method. Suicidal? We'll see...

Typical Safety

Before I back up my suggestion let's first take a look at how we usually ensure that our resource critical objects are properly cleaned up. It's normal practice to first write code that achieves it's primary purpose. For instance 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.

Due to the plethora of database connection code samples out there almost everyone knows 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?

Well, I'm afraid I only give the above code a "B" grade. One of the reasons for this is because it's not fool-proof. A good coder codes defensively, not only for how the code currently runs but also for what could happen when the code is later adjusted (the code is always adjusted). 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 poor coder has done his best to add some functionality he told his boss would be very easy to do - "it will only take 5 seconds". However what happens when ret is null or something goes wrong in the EmailerClass.EmailMe method which of course doesn't handle it's own exceptions? 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. So you don't have to worry about other programmers not being careful about what code they inject and where they inject it. The code that you wrote is safe.

There you have it - a database connection object being used with no call being made to it's Close method. If you're interested in how this "magic" works and what the performance implications are, read on. Otherwise just start coding with using statements.

IDisposable

So how does our using statement, as coded above, know that for the particular object involved (the database connection object) the connection should be closed when the object goes out of scope? The answer is that this is done in the usual object-oriented way - via the implementation of an interface. The interface for this is called IDisposable and it is important enough to be found in the System namespace.

When a class implements the IDisposable interface it must implement a method called Dispose. It is this method that gets called when an object goes out of the scope of a using statement. In effect the above code is functionally equivalent to:

IDisposable connection = null;
try
{
    connection = new SqlConnection(connectionString);
    SqlCommand command = connection.CreateCommand();

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

    ((SqlConnection)connection).Open();
    object ret = command.ExecuteScalar();
}
finally
{
    connection.Dispose();
}

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.

But "wait a minute" you say! "What if I need my object to be a class member and therefore can't wrap it's scope in a using statement?" Good question! The answer to is to have your class implement IDisposable and in the Dispose method call the Dispose method of your member object. Then whenever you instantiate your class, do so in a using statement.

Comments
by Jason Yip You put the ret declaration inside the try / using block, how do you return it?

using (...)
{
object ret = ...
}

return ret; // ???
by Malcolm van Raalte Jason, "ret" will be out of scope in your return statement. You can do either of the following:

using(...)
{
object ret = ....
return ret;
}

- or -

object ret;
using(...)
{
object ret = ....
}
return ret;
by Naye Nice article...very informative and clear :-)
by murali very nice
by murali using(...)
{
object ret = ....
return ret;
}
Jason, after return statement
if you put } for using

will connection will be closed?
by Malcolm van Raalte murali: yes, if the variable declared in the using statements parentheses () is a SqlCommand object it will be closed after the "return" statement.
by murali Malcolm van Raalte
Great,
Thanks for quick update .
by MURALI How about datareader?
if i return the datareader if i use the using statement

will using statement works?

using(...)
{

return DataReader;
}
i am always getting error datareader is empty.

by MURALI How about datareader?
if i return the datareader if i use the using statement

will using statement works?

using(...)
{

return DataReader;
}
i am always getting error datareader is empty.

by Malcolm van Raalte murali: yes, if the variable declared in the using statements parentheses () is any sort of DbDataReader object, or the data reader's DbConnection object, it will be closed after the "return" statement. So you typically don't want to return a data reader from within a using statement. In scenario's like this I usually switch to a IDisposable class implementation of the data retriever - see the last paragraph of the article.
by milind Malcolm really this is illuminating article for me.
by sree ya it's great .can give real time example's
by griph Perfect content! Perfect style! Thanks a lot.
by reggie thank you
by craig malcom, is there any sql side performance benefit to having your return statement after the end of the using statements scope?

ie any difference in performance between

using(...)
{
object ret = ....
return ret;
}

-VS-

object ret;
using(...)
{
object ret = ....
}
return ret;

thanks.
by Malcolm van Raalte craig: if you look at the IL for each implementation there is only a very small difference concerning when a temporary local variable gets set. In both cases a finally{} block is created for the clean-up of the "using" variable and the return happens after the finally. I suspect there won't be a measurable performance difference. However, as with all things to do with performance, the truth can only be known be running a performance test with your code in your environment. So fire up a loop around each implementation, measure the times with a Stopwatch instance and see what you get.
by ishti Great article..
I have a question.
i have written my code in two ways.
Can some one tell me that Which one is more efficient .
Code 1:
for (int i = 0; i < chkTypes.Items.Count; i++)
{
if (chkTypes.Items[i].Selected)
{
//some string comparioson etc
using (SPTableAdapters.BsnsadpTableAdapter adp = new SPTableAdapters.BsnsadpTableAdapter())
{
adp.InsertData_Type(retid, int.Parse(chkTypes.Items[i].Value.ToString()));
}

}
}


Code2:


using (SPTableAdapters.BsnsadpTableAdapter adp = new SPTableAdapters.BsnsadpTableAdapter())
{
for (int i = 0; i < chkTypes.Items.Count; i++)
{
if (chkTypes.Items[i].Selected)
{
//some string comparioson etc
adp.InsertData_Type(retid, int.Parse(chkTypes.Items[i].Value.ToString()));

}
}
}
.
by Malcolm van Raalte ishti: Code1 is almost certainly more efficient since the BsnsadpTableAdapter isn't instantiated i times.
by Snowill Hi Malcolm, will the using statement work for socket programming, for example, objects like TCPClient and Networkstream?
by Snowill Hi Malcolm, will the using statement work for socket programming, for example, objects like TCPClient and Networkstream?
by Malcolm van Raalte Snowill: yes, any class that implements IDisposable will work with the using statement.
by Jon The problem I have with this example is that you don't explain how the "poor coder" trying to add the email functionality is able to do so with the using statement.

If you had originally implemented this with a using statement, and the "poor coder" comes along, he would have to switch it back to a try-catch to get it to work. Granted he would have to have a seperate try-catch around the call to EmailMe.
by Winkie Very nice article.
It's nice to get a clear presentation of these things.
by Azim Malik Nice
by irina <a href="http://www.onlinecasinobonukset.net/keno">keno</a>
Your correspondence helped me a lot. decided many unknowns pytanb for themselves. how wonderful to find such people.
by Luke one thing I noticed is that the connectionstring gets reset when the using statement is used. In an application I wrote I used the same OleDBconnection object several times, and various methods would open then close the connection, the using statement caused me issues in this scenario as I would have to keep initializing the connectionstring property.

Is this because using effectively calls Dispose, whereas if you simple use close, the Dispose is left to the GC?
by Luke one thing I noticed is that the connectionstring gets reset when the using statement is used. In an application I wrote I used the same OleDBconnection object several times, and various methods would open then close the connection, the using statement caused me issues in this scenario as I would have to keep initializing the connectionstring property.

Is this because using effectively calls Dispose, whereas if you simple use close, the Dispose is left to the GC?
by Kevin Great article, much easier on the eyes then the MSDN article.
by Rajesh Rinhayat this is nice article and clear to understand
by sdf
Pictures, prints and onto canvas created from your own photos, framed and ready to hang. For the best quality canvas prints at the lowest online prices guaranteed, with fast delivery.
http://www.picstocanvas.co.uk/

<a href="http://www.picstocanvas.co.uk">Photos printed on canvas</a>

by Shay I have used the following using statement

using (var connection = new OracleConnection(DatabaseConnectionString))
{
connection.Open();
using (var dbCommand = new OracleCommand(WEB_PACK_CHECK_PORTFOLIO, connection))
{
.....

etc...
}

}


do I need to add a
connection.Close();
connection.Dispose();

I have noticed when I throttle the service 10 or 12 times is eventually falls over which leads me to believe my connetcion is not cleared up
by Jay Thanks Malcolm, I'm a bit late to the game, very useful and clear article.

Could you correct me if I am wrong please? With regards to ishti's question of efficiency, I would have thought Code 2 would be more efficient as the using{} statement is outside the loop.

Kind regards, Jay
by Sanjeev This is a good stuff. During this analysis i have observed in the SQL Profiler that even after using the 'using' block, one connection remain active. What I did is
- Create the connection in the using block
- execture a SP
run a loop and executed the above for 100 times.
So, everytime, reset connection was called in SQL profiler, but when i refresh it after complete execution, there was one connection was still active and it will remain until you close the application.

Any idea why one connection remain active even after using or disposing
by Awesome Really helpful.
by Josto Hi Malcom.

Very nice article.

I have a different scenario: My code is a webpart which has several eventhandlers invoked from outside (button clicks, dropdownlist texchanged, etc.) and I need a SPWeb object to make operations inside every eventhandler. A possible approach is an using clause in every eventhandler but there are more than 20. I hate to repeat the same code in each of them:

protected void eventhandler_1(object sender, EventArgs e)
{
using(...)
{
...
}
}

protected void eventhandler_2(object sender, EventArgs e)
{
using(...)
{
...
}
}

...

The best solution could be to put the using clause in the scope where all handlers are called, but that´s not my code! What can I do to avoid repeat the using clause in every eventhandler?

Thanks in advance
by khan kjghkj
Add a Comment Name: Comment: Enter the text in the image below:
w3 enterprises