SqlDataReader Close and SqlConnection

Question

What happens with the connection in this case? I don't know if reader.Close() close the open sqlconnection.

private static void ReadOrderData(string connectionString)
{
    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();
    }
}

Sabrina Fernandez Vazzino · Accepted Answer · 2017-07-20 19:59:48Z

3

reader.Close() won't close the SqlConnection, but since you have a using statement, once you exit the using block the connection will get closed anyway.

answered Jul 20, 2017 at 19:59

Sabrina Fernandez Vazzino

1562 silver badges6 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Graham · Accepted Answer · 2017-09-22 02:02:03Z

2

Closing the reader will not alter the state of the connection. If you did want to do this you can pass CommandBehavior.CloseConnection to the ExecuteReader method of the SqlCommand instance.

SqlDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection);

It is recommended you put a using block around all your disposable types like your SqlDataReader instance.

using(SqlDataReader reader = command.ExecuteReader()) {
  // rest of code
}

See also CommandBehavior for more options.

edited Sep 22, 2017 at 2:02

Graham

7,86020 gold badges67 silver badges92 bronze badges

answered Jul 20, 2017 at 20:02

Igor

62.5k10 gold badges111 silver badges181 bronze badges

Comments

Sandip Bantawa · Accepted Answer · 2017-07-20 20:10:45Z

1

Check Connection State

if(connection.State == ConnectionState.Open)
{
    connection.Close();
}
connection.Open();

And since you are doing

using (SqlConnection connection =
               new SqlConnection(connectionString))
    {

this will make sure connection is disposed as it inherits from IDisposable despite Exception. And object are disposed once they exit their corresponding scope.

And better use

using(SqlCommand command = new SqlCommand(queryString, connection))

and

using(SqlDataReader reader = command.ExecuteReader())

for the same reason mentioned above.

edited Jul 20, 2017 at 20:10

answered Jul 20, 2017 at 19:56

Sandip Bantawa

2,9004 gold badges35 silver badges48 bronze badges

Collectives™ on Stack Overflow

SqlDataReader Close and SqlConnection

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related