CODEWITHSUNDEEP
c#ado.net
Eldar
Eldar

Reputation: 330

Ado.Net: Does closing SqlCommand cause closing DataReader

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();
}

Upvotes: 1

Views: 423

Answers (2)

Denzil Soans
Denzil Soans

Reputation: 667

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.

Upvotes: 2

Heinzi
Heinzi

Reputation: 172260

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:

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.

Upvotes: 2

Related Questions