Ado.Net: Does closing SqlCommand cause closing DataReader

Question

Today I looked through some legacy code and I have began worrying. It is required to close a DataReader explicitly.

My question is: does closing the SqlCommand close the associated DataReader as well?

This is my code:

    using (var conn = new SqlConnection(this.ConnectionString))
    {
        conn.Open();

        using (var cmd = conn.CreateCommand())
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.CommandText = "House_GetHouseById";

            SqlCommandBuilder.DeriveParameters(cmd);

            cmd.Parameters["@HouseId"].Value = houseId;

            var reader = cmd.ExecuteReader())

            while (reader.Read())
            {

            }
        }
    }

In this snippet from msdn command is not closed explicitly:

string queryString =
    "SELECT OrderID, CustomerID FROM dbo.Orders;";

using (SqlConnection connection =
           new SqlConnection(connectionString))
{
    SqlCommand command =
        new SqlCommand(queryString, connection);
    connection.Open();

    SqlDataReader reader = command.ExecuteReader();

    // Call Read before accessing data.
    while (reader.Read())
    {
        Console.WriteLine(String.Format("{0}, {1}",
            reader[0], reader[1]));
    }

    // Call Close when done reading.
    reader.Close();
}

it might be good to wrap the reader in a using clause as well — Vincent De Smet, Apr 29 '15 at 07:26

score 2 · Accepted Answer · edited May 23 '17 at 11:43

No. SqlCommand.Dispose is essentially a no-op¹, and it won't close your SqlDataReader.

Technically, closing the SqlConnection should close all resources, see this question for details:

Is closing/disposing an SqlDataReader needed if you are already closing the SqlConnection?

However, this is bad practice -- you are relying on an implementation detail of the SqlClient library. The "correct" way would be to dispose (via Dispose or using) everything that is IDisposable. Thus, your code should be written as follows:

using (var conn = new SqlConnection(this.ConnectionString))
{
    conn.Open();

    using (var cmd = conn.CreateCommand())
    {
        cmd.CommandType = CommandType.StoredProcedure;
        cmd.CommandText = "House_GetHouseById";

        SqlCommandBuilder.DeriveParameters(cmd);

        cmd.Parameters["@HouseId"].Value = houseId;

        using (var reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                // do something
            }
        }
    }
}

¹ Note that this is not true for the command classes of other libraries such as OleDbCommand and SqlCeCommand, so don't get in the habit of not disposing commands.

score 2 · Answer 2 · edited Apr 29 '15 at 07:35

2

You could always wrap the datareader in a using directive so that the command is closed as soon the execution is out of the scope,just like the sqlcommand

    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        if (reader != null)
        {
            while (reader.Read())
            {
                //do something
            }
        }
    } // reader is closed here

reader.Close(); //is another way but its too easy to be forgotten.

edited Apr 29 '15 at 07:35

marc_s

732,580
175
1,330
1,459

answered Apr 29 '15 at 07:25

Denzil Soans

667
1
7
27

Don't know who upvoted this, but question was **"Does closing SqlCommand cause closing DataReader"** – Sergey Berezovskiy Apr 29 '15 at 07:45

Ado.Net: Does closing SqlCommand cause closing DataReader

2 Answers2