Trying to understand the 'using' statement

Question

I have read a couple of articles about the using statement to try and understand when it should be used. It sound like most people reckon it should be used as much as possible as it guarantees disposal of unused objects.

Problem is that all the examples always show something like this:

using (SqlCommand scmFetch = new SqlCommand())
{
    // code
}

That makes sense, but it's such a small piece of code. What should I do when executing a query on a database? What are all the steps? Will it look something like this:

string sQuery = @"
    SELECT [ID], [Description]
    FROM [Zones]
    ORDER BY [Description] ";

DataTable dtZones = new DataTable("Zones");

using (SqlConnection scnFetchZones = new SqlConnection())
{
    scnFetchZones.ConnectionString = __sConnectionString;
    scnFetchZones.Open();

    using (SqlCommand scmdFetchZones = new SqlCommand())
    {
        scmdFetchZones.Connection = scnFetchZones;
        scmdFetchZones.CommandText = sQuery;

        using (SqlDataAdapter sdaFetch = new SqlDataAdapter())
        {
            sdaFetch.SelectCommand = scmdFetchZones;
            sdaFetch.Fill(dtZones);
        }
    }

    if (scnFetchZones.State == ConnectionState.Open)
        scnFetchZones.Close();
}

What I want to know is:
• Is it okay to have 4, 5, 10 nested using statements to ensure all objects are disposed?
• At what point am I doing something wrong and should I consider revision?
• If revision is required due to too many nested using statements, what are my options?

You might end up with a formidable hierarchy, but your code should be quite efficient, right? Or should you only put, for instance, the SqlDataAdapter object in a using statement and it will somehow ensure that all the other objects get disposed as well?

Thanx.

Answer 1

• Is it okay to have 4, 5, 10 nested using statements to ensure all objects are disposed?

Reply: You can not limit of using nested "using blocks ".

• At what point am I doing something wrong and should I consider revision?

Reply: If you have many nested "using blocks". Please try as below.

        using (var con = new SqlConnection(connStr))
        using (var cmd = new SqlCommand(queryStry))
        using (var rs = cmd.ExecuteReader())
        {
            while (rs.Read())
            {
                //Code.
            }
        }

Answer 2

You would never be wrong if you use using for every IDisposable that you use. There is no limit of how many nested using blocks you use.

Answer 3

IMHO, what you need to ask yourself is: What are the alternatives? Try/finally blocks? Are they more readable? More maintainable? In almost all cases, the answer is going to be "no".

So use using. It's the closest thing C# has to C++'s RAII pattern and it's all good :-)

Answer 4

One time I can think of where you wouldn't want to use 'using' on connections would be on ClassFactories for connected objects such as DataReaders, e.g. consider the case

private IDataReader CreateReader(string queryString,
    string connectionString)
{
    SqlConnection connection = new SqlConnection(connectionString);
    SqlCommand command = new SqlCommand(queryString, connection);
    connection.Open();
    return command.ExecuteReader(CommandBehavior.CloseConnection);
    // Don't close connection
}

(Modified from MSDN - The example on MSDN is just plain stupid)

Another reason is on WCF ServiceReference 'clients' - if the channel becomes faulted, 'using' then hides the actual exception. But this is just a buggy implementation IMHO.

Answer 5

There's no limit on the depth, so that's not a concern. You should verify that the object of the using implements IDisposable. And an object being disposed doesn't dispose of all objects connected to it, just those it creates.

So, at what point are you doing wrong: there's no limit, but generally its fairly shallow, you create the object, do a task, then the object is disposed. If you're doing it very deeply, I'd look at the design. I think you'd be hard pressed to do it more than few layers deep.

As for your options for a redesign, that really depends upon what you are doing, but you might use the same object for multiple tasks. Most likely you will end up breaking the task down into a function (passing in any surrounding objects that are needed).

Answer 6

Personally, I have used at least 3 layers (Connection, Command, Other) a number of times and I see absolutely no problem with it, but as you have already hinted at, eventually there will be a problem a readability. As with other nested constructs, you may need to balance efficiency with maintainability. That is, you don't need to necessarily sacrifice efficiency, but there is often 'more than one way to skin a cat'.

That said, you would be hard-pushed to generate 10 nested layers!