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
– Vincent De Smet, Commented Apr 29, 2015 at 7:26

Community · Accepted Answer · 2017-05-23 11:43:29Z

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.

marc_s · Accepted Answer · 2015-04-29 07:35:50Z

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, 2015 at 7:35

marc_s

760k186 gold badges1.4k silver badges1.5k bronze badges

answered Apr 29, 2015 at 7:25

Denzil Soans

6771 gold badge7 silver badges28 bronze badges

1 Comment

Sergey Berezovskiy Over a year ago

Don't know who upvoted this, but question was "Does closing SqlCommand cause closing DataReader"

Collectives™ on Stack Overflow

Ado.Net: Does closing SqlCommand cause closing DataReader

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related