ADO.NET 1.1 Performance Guidelines - DataReader

From Guidance Share

Jump to: navigation, search

- J.D. Meier, Srinath Vasireddy, Ashish Babbar, and Alex Mackman


Contents

Close DataReader Objects

Close your DataReader object as soon as you are finished with it, either by calling its Close method or by calling its Dispose method. It is best to use a finally block to ensure that the DataReader is closed as shown in the following code fragment.

using (SqlConnection conn = new SqlConnection(connString))
{
  SqlCommand cmd = new SqlCommand("CustomerRead",conn);

  conn.Open();
  SqlDataReader dr = cmd.ExecuteReader();
  try
  {
    while (dr.Read())
      Console.WriteLine(dr.GetString(0));
  }
  finally
  {
    dr.Close();
  }
}


Consider Using CommandBehavior.CloseConnection to Close Connections

If you need to return a DataReader from a method, consider using the CommandBehavior.CloseConnection method to ensure that the associated connection is closed when the DataReader is closed. The following code fragment shows this approach.

public SqlDataReader RetrieveRowsWithDataReader()
{
  SqlConnection conn = new SqlConnection(
        "server=(local);Integrated Security=SSPI;database=northwind");
  SqlCommand cmd = new SqlCommand("RetrieveProducts", conn );
  cmd.CommandType = CommandType.StoredProcedure;
  try
  {
    conn.Open();
    // Generate the reader. CommandBehavior.CloseConnection causes
    // the connection to be closed when the reader object is closed.
    return( cmd.ExecuteReader( CommandBehavior.CloseConnection ) );
  }
  finally
  {
    if(null!=conn)
       conn.Close();
  }
}

// Display the product list using the console.
private void DisplayProducts()
{
  SqlDataReader reader = RetrieveRowsWithDataReader();
  try
  {
    while (reader.Read())
    {
      Console.WriteLine("{0} {1}",
                       reader.GetInt32(0).ToString(),
                       reader.GetString(1));
     }
  }
  finally
  {
     if(null!= reader)
        reader.Close(); // Also closes the connection due to the CommandBehavior
                       // enumerator used when generating the reader.
  }
}


Cancel Pending Data

When you call the Close method, the method does not return until all the remaining data has been fetched. If you know you have pending data when you want to close your DataReader, you can call the Cancel method before you call Close to tell the server to stop sending data.

This approach does not always result in a performance improvement, because Cancel is not guaranteed to make the server stop sending data. Control information is still exchanged after the call to Cancel, and the control information may or may not be interleaved with leftover data. Therefore, before you restructure your code to call Cancel before Close, test Cancel to learn if it actually helps in your particular scenario and to learn if you really need the extra performance at the expense of readability.

Note If you need output parameters, do not call Close until you have retrieved the output parameters. After you retrieve the output parameters, you can then call Close.


Consider Using CommandBehavior.SequentialAccess with ExecuteReader

If you do not have to have random access to columns, use CommandBehavior.SequentialAccess when you call the ExecuteReader method of the Command object.


Use GetOrdinal When Using an Index-Based Lookup

Using an index or ordinal-based lookup is faster than using string-based column names. However, using an index adds code maintenance overhead. Using an index requires you to change the index when the query column-order changes or when table columns are changed. Instead of hard coding the values, you can use GetOrdinal to get the index as shown in the following code fragment.

cmd.CommandText = "Select RegionDescription, RegionId from Region";
SqlDataReader dr = cmd.ExecuteReader();

int RegionId = dr.GetOrdinal("RegionId");
int RegionDescription = dr.GetOrdinal("RegionDescription");

while( dr.Read())
{
  Console.WriteLine(dr[RegionId] + " - " + dr[RegionDescription]);
}
Personal tools