37

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.

Keoki
  • 535
  • 2
  • 6
  • 11
  • 2
    Possible duplicate of [Is it necessary to manually close and dispose of SqlDataReader?](http://stackoverflow.com/questions/744051/is-it-necessary-to-manually-close-and-dispose-of-sqldatareader) –  Oct 20 '15 at 12:03
  • Also related: [Will putting a “using” statement around a DataReader close it?](http://stackoverflow.com/q/2157276/456814). –  Oct 20 '15 at 12:03

3 Answers3

42

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.

Daniel Kelley
  • 7,579
  • 6
  • 42
  • 50
  • 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? – Developer Webs Feb 05 '20 at 15:11
20

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]));
            }
        }
    }
}
BinaryTox1n
  • 3,486
  • 1
  • 36
  • 44
  • @eaglei22 because it implements ‘IDisposable’ - see this answer for more details: https://stackoverflow.com/a/23186013/376474 – BinaryTox1n May 07 '19 at 22:49
  • @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. – BinaryTox1n May 07 '19 at 23:06
2

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?

SimonA
  • 21
  • 4