October 12, 2002 - The SqlDataReader Class

October 12, 2002 The SqlDataReader Class Tips: October 2002 Yehuda Shiran, Ph.D. Doc JavaScript

To create an SqlDataReader instance object, you need to call the ExecuteReader() method of the SqlCommand class, rather than directly using a constructor.

Reading records from the database is asynchronous. This means that if you don't wait for the last record to be read and try to access one of the records, the results are not guaranteed to be valid. In order to avoid accessing records before they are read in, you need to close the reading channel. You can close the connection by sending the constant CommandBehavior.CloseConnection to the ExecuteReader() method of the SqlCommand class. Here is an example that shows how the myCommand instance object of the SqlCommand class closes the connection:

  var result : SqlDataReader  = 
    myCommand.ExecuteReader(CommandBehavior.CloseConnection);

  public function GetProductCategories() : SqlDataReader {
    var myConnection : SqlConnection = new SqlConnection(ConfigurationSettings.AppSettings["ConnectionString"]);
    var myCommand : SqlCommand = new SqlCommand("ProductCategoryList", myConnection);
    myCommand.CommandType = CommandType.StoredProcedure;
    myConnection.Open();
    var result : SqlDataReader  = myCommand.ExecuteReader(CommandBehavior.CloseConnection);
    return result;
  }