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;
  }

People who read this tip also read these tips:

• Deploying and Testing IsPrime's Consumer
• Using override
• Avoiding Hard-Coded Numbers in XSLT
• Writing Web Forms
• Specifying Assemblies