c# using statement with double IDisposable

Question

I have following statement using using:

using (var reader = data.CreateCommand(sql).ExecuteDataReader())

in this case data is some object which internally holds SqlConnection. CreateCommand(sql) function returns SqlCommand and ExecuteDataReader returns SqlDataReader. Since SqlCommand and SqlDataReader are both IDisposable, will they both be disposed by this use of using statement?

For now I have done it like this:

using (var cmd = data.CreateCommand(sql))
using (var reader = cmd.ExecuteDataReader())

But I would like to know if it's possible to combine them as stated above?

Answer 1

"if it's possible to combine them" - two using are totally fine, because both needs to be disposed.

You can combine them by extracting into method:

void RunWithReader(string sql, Action<SQLDataReader> action)
{
    using (var cmd = data.CreateCommand(sql))
    using (var reader = cmd.ExecuteDataReader())
        action(reader);
}

Then you can use lambda

RunWithReader("whatever command", reader =>
{
    ... // while(reader.Read() { ... } - this could also be extracted
});

Answer 2

Agree with Matthew Watson's comment. You need to have both of usings statemets. Here is the related question with additional reasoning. SqlConnection SqlCommand SqlDataReader IDisposable

Answer 3

The way you have presented your code the inner IDisposable (IDbCommand) is not disposed.

You have two choices:

You can put it all in one using like this:

using (IDisposable cmd = data.CreateCommand(), reader = ((IDbCommand)cmd).ExecuteReader())
{
    // code here
}

But that is rather cumbersome. The other option is nested using statements:

using (var cmd = data.CreateCommand())
{
    using (var reader = cmd.ExecuteReader())
    {
        // code here
    }
}

Other than that you can get a bit complicated and write an extension method to help (sort of) clean it up for you.

public static class Ex
{
    public static void Using<T, R>(this T obj, Func<T, R> create, Action<T, R> use) where R : IDisposable
    {
        using (var d = create(obj))
        {
            use(obj, d);
        }
    }
}

Then you can do this:

data.Using(d => d.CreateCommand(), (d, c) => c.Using(c2 => c2.ExecuteReader(), (c3, r) =>
{
    // code here
}));

But perhaps that's not much of an improvement.

Answer 4

In General

You should Dispose every class that implements IDisposable.

However, your question is specifically dealing with a scenario where all the resources are all related, which if my understanding is correct, the Garbage Collector will dispose these resources for you automatically.

---

The Finalize() Method ("Destructors" or "Finalizers")

The Finalize() method makes sure that resources are disposed when the instance is no longer referenced by the application.

In concert together with the Dispose() method

The Dispose() method will "release all resources used by the component". Which would mean that this code block (taken from MSDN's ExecuteReader Method Example) will properly dispose of all the compenents used by it...

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    SqlCommand command = new SqlCommand(queryString, connection);
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        // ...
    }
}

In this case, when disposing SqlConnection, it will properly release all resources used by the component. Which means:

• SqlCommand

• SqlDataReader

  ** Note: This has always been my understanding...

---

Refactoring of the above code to the following:

using (SqlConnection connection = new SqlConnection(connectionString))
{
    connection.Open();

    using (SqlCommand command = new SqlCommand(queryString, connection))
    {
        using (SqlDataReader reader = command.ExecuteReader())
        {
            while (reader.Read())
            {
                // ...
            }
        }
    }
}

The main advantage of manually disposing of each resource, would be for a performance gain since we are not processing up to the Finalize method (which is relatively a more expensive than doing Dispose().

However, that doesn't mean you want to Dispose() everything, as in some cases the Finalize() method is a better choice.

---

Assuming that I am still in good shape so far, and my understanding of the Garbage Collector's behavior is valid.

I would consider that SqlCommand (full code reference here), would then also dispose of SqlReader, which would mean that you could just get away with:

using (SqlCommand command = new SqlCommand(queryString, connection))
{
    SqlDataReader reader = command.ExecuteReader();
    while (reader.Read())
    {
        // ...
    }
}

which in your case would translate to:

using (var cmd = data.CreateCommand(sql))
{
    var reader = cmd.ExecuteDataReader();
    // ...
}