Should I be using SqlDataReader inside a "using" statement?

Question

Which of the following two examples are correct? (Or which one is better and should I use)

In the MSDN I found this:

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

However looking other pages some users suggest to do it this way:

private static void ReadOrderData(string connectionString)
{
   string queryString = "SELECT OrderID, CustomerID FROM dbo.Orders;";

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

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

So, the question is: should I use the using statement also in the SqlCommand and in the SqlDataReader or they are automatically disposed at the end of the SqlConnection using code block.

Moonmoonbeam

Possible duplicate of Is it necessary to manually close and dispose of SqlDataReader? – Gloxinia

Also related: Will putting a “using” statement around a DataReader close it?. – Gloxinia

Answer 1

The second option means your reader will be closed in the event of an exception after it has been created, so it is preferred.

It is effectively transformed by the compiler to:

SqlDataReader reader = command.ExecuteReader();
try
{
    ....
}
finally
{
  if (reader != null)
      ((IDisposable)reader).Dispose();
}

See MSDN for more info.

Halley

Is there any ill effect at all if the reader is not closed? Eventually garbage collection will throw it away. It doesn't leave file handles open to files on disk etc right? – Foramen

Answer 2

You can actually list usings together, a la:

private static void ReadOrderData(string connectionString)
{
   string queryString =
       "SELECT OrderID, CustomerID FROM dbo.Orders;";

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

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

Delve

@eaglei22 because it implements ‘IDisposable’ - see this answer for more details: https://mcmap.net/q/426419/-when-is-quot-using-quot-block-used-for-in-c-how-to-use-quot-using-quot-block-in-c – Delve

@eaglei22 I don’t know why the MSDN documentation doesn’t include a dispose, but as a general rule you should dispose all disposable objects you own. In other words, the only time you wouldn’t is if “someone else” was responsible for the lifecycle of that instance and will take care of disposing at the appropriate time for you. Here, since I am needing up the instance I own it and I dispose of it with using. – Delve

Answer 3

Would it not be simpler to use this code?

    private static void ReadOrderData(string connectionString)
    {
        string queryString =
            "SELECT OrderID, CustomerID FROM dbo.Orders;";

        using (SqlDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, queryString))
        {
            // Call Read before accessing data.
            while (reader.Read())
            {
                Console.WriteLine(String.Format("{0}, {1}",
                reader[0], reader[1]));
            }
        }
    }

This should dispose of the reader, and the implicit connection and command when the using is terminated.

Or have I missed something?

Carrell