Is it necessary to manually close and dispose of SqlDataReader?

Technology CommunityCategory: ADO.NETIs it necessary to manually close and dispose of SqlDataReader?
VietMX Staff asked 3 years ago

Try to avoid using readers like this:

SqlConnection connection = new SqlConnection("connection string");
SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection);
SqlDataReader reader = cmd.ExecuteReader();
connection.Open();
if (reader != null)
{
      while (reader.Read())
      {
              //do something
      }
}
reader.Close(); // <- too easy to forget
reader.Dispose(); // <- too easy to forget
connection.Close(); // <- too easy to forget

The using statement will ensure correct disposal of the object and freeing of resources. If you forget then you are leaving the cleaning up to the garbage collector, which could take a while.

Instead consider:

using(SqlConnection connection = new SqlConnection("connection string"))
{
    connection.Open();
    using(SqlCommand cmd = new SqlCommand("SELECT * FROM SomeTable", connection))
    {
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            if (reader != null)
            {
                while (reader.Read())
                {
                    //do something
                }
            }
        } // reader closed and disposed up here
    } // command disposed here
} //connection closed and disposed here