Will putting a "using" statement around a DataReader close it?

Question

I usually write my DataReader code like this:

try
{
    dr = cmd.ExecuteReader(CommandBehavior.SingleResult);
    while (dr.Read())
    {
        // Do stuff
    }
}
finally
{
    if (dr != null) { dr.Close(); }
}

Is it safe to replace the try and finally with just a using block around the DataReader's creation? The reason I wonder is because in all the Microsoft examples I've seen they use a using for the connection but always explicitly call Close() on the DataReader.

Heres's an example from Retrieving Data Using a DataReader (ADO.NET):

static void HasRows(SqlConnection connection)
{
    using (connection)
    {
        SqlCommand command = new SqlCommand(
          "SELECT CategoryID, CategoryName FROM Categories;",
          connection);
        connection.Open();

        SqlDataReader reader = command.ExecuteReader();

        if (reader.HasRows)
        {
            while (reader.Read())
            {
                Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
                    reader.GetString(1));
            }
        }
        else
        {
            Console.WriteLine("No rows found.");
        }
        reader.Close();
    }
}

As a side note, most of the MS examples I've seen are either overly complicated or don't really leverage much of the language features available. In part it's because they simply roll over the example code from previous releases and really only check that it still runs. My guess is that this code is not maintained by their brightest because the code is never clever or efficient. — NotMe, Jan 28 '10 at 19:30
@NotMe from the quality of the example code, I've suspected *for years* that it's written and maintained by entry-level interns, not the people who actually designed the classes in the first-place. It's things like Microsoft's lackluster .NET documentation that kind of make me look forward to [SO's new documentation expansion](http://meta.stackoverflow.com/questions/303865/warlords-of-documentation-a-proposed-expansion-of-stack-overflow). — , Oct 20 '15 at 11:05
Possible duplicate of [Is it necessary to manually close and dispose of SqlDataReader?](http://stackoverflow.com/questions/744051/is-it-necessary-to-manually-close-and-dispose-of-sqldatareader) — , Oct 20 '15 at 12:02
@Cupcake not a dupe in my opinion. This one here asks if a `using` block will close a data reader, the other one asks if it's required to close it. Not the same thing. — Shadow The GPT Wizard, Oct 20 '15 at 13:19

score 22 · Accepted Answer · edited Aug 16 '12 at 08:37

Yes. using calls Dispose. Calling Dispose on SqlDataReader closes it.

This is psuedo-code of SqlDataReader gleaned from Reflector:

    public void Dispose()
    {
        this.Close();
    }

    public override void Close()
    {
        if( !IsClosed )
            CloseInternal(true);
    }

    private void CloseInternal(bool closeReader)
    {
        try
        {
            // Do some stuff to close the reader itself
        }
        catch(Exception ex)
        {
            this.Connection.Abort();
            throw;
        }

        if( this.Connection != null && CommandBehavior.CloseConnection == true )
        {
            this.Connection.Close();
        }
    }

naivists · Answer 2 · 2010-01-28T19:29:58.893

Typically, using() calls Dispose() and that calls close() in turn.

In case of a DataReader, the Close is called only when CommandBehavior.CloseConnection is set (see comments of this article http://weblogs.asp.net/joseguay/archive/2008/07/22/ensure-proper-closure-amp-disposal-of-a-datareader.aspx).

EDIT: This article says something interesting:

The Close() method on the SqlDataReader calls an InternalClose() method, which does not call Dispose. Note that previously we stated the correct way to do this was to have your close call dispose. To make it even more confusing the Dispose() method actually calls the Close() method so for this object the order is reversed.

score 1 · Answer 3 · edited Sep 27 '11 at 14:29

1

From what I can recall, if an exception occurs in a Using block, then the Dispose method is still called on the object. I usually have a Using statement for all disposable objects, without a Try..Catch.

EDIT: Forgot to say that for some objects, calling Dispose will in turn call Close for that object.

edited Sep 27 '11 at 14:29

answered Jan 28 '10 at 19:23

Jason Evans

28,906
14
90
154

score 1 · Answer 4 · answered Dec 07 '10 at 17:09

Unlike the example here, my practice has been to employ a using block for the connection, the command and the reader. Note that you can stack nested using blocks to lower the indentation cost.

static void HasRows(SqlConnection connection)
{
    using (connection)
    using (SqlCommand command = new SqlCommand(
    "SELECT CategoryID, CategoryName FROM Categories;",
    connection))
    {
        connection.Open();
        using (SqlDataReader reader = command.ExecuteReader())
        {
            if (reader.HasRows)
            {
                while (reader.Read())
                {
                    Console.WriteLine("{0}\t{1}", reader.GetInt32(0),
                        reader.GetString(1));
                }
            }
            else
            {
                Console.WriteLine("No rows found.");
            }
            reader.Close();
        }   
    }
}

Will putting a "using" statement around a DataReader close it?

4 Answers4

Linked