Is it necessary to manually close and dispose of SqlDataReader?

by

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

Instead, wrap them in using statements:

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

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.

More Related Contents:

  1. Check if a SQL table exists
  2. Do we have transactions in MS-Access?
  3. How can I add user-supplied input to an SQL statement?
  4. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  5. What is the syntax for an inner join in LINQ to SQL?
  6. Get output parameter value in ADO.NET
  7. Read SQL Table into C# DataTable
  8. When should “SqlDbType” and “size” be used when adding SqlCommand Parameters?
  9. SQLite Database Locked exception
  10. Populate data table from data reader
  11. SqlConnection SqlCommand SqlDataReader IDisposable
  12. SqlBulkCopy from a List
  13. Exception when AddWithValue parameter is NULL
  14. Insert into C# with SQLCommand
  15. Does using parameterized SqlCommand make my program immune to SQL injection?
  16. Executing query with parameters
  17. MultipleActiveResultSets=True or multiple connections?
  18. How to fill Dataset with multiple tables?
  19. How DataReader works?
  20. SqlConnection.Close() inside using statement
  21. Join and Include in Entity Framework
  22. How do I re-write a SQL query as a parameterized query?
  23. Get affected rows on ExecuteNonQuery
  24. How do I translate a List into a SqlParameter for a Sql In statement? [duplicate]
  25. Enabling Foreign key constraints in SQLite
  26. Passing datatable to a stored procedure
  27. How do I extract data from a DataTable?
  28. Reading a date using DataReader
  29. What ‘length’ parameter should I pass to SqlDataReader.GetBytes()
  30. How do I connect to a SQL database from C#?

Leave a Comment