Share via

Using a DbDataReader to Retrieve Multiple Rows

  • Article

A common database task is to retrieve and display information. For example, an online retail application may need to display a list of products within a specified category.

Typical Goals

In this scenario, you want to retrieve multiple rows from a database and display them immediately and only once with a Web browser. You do not want to explicitly cache the data, manipulate it by using a DataSet object, or pass it to other components within your application. You simply want to use the results as quickly as possible.

These goals can be summarized as follows:

  • You want to retrieve data for read-only purposes, which, in most cases, means that you want to display the data.
  • You want to bind to a Web Form control for display.
  • You do not need to cache the data. After it is used, it is discarded.

Solution

Use the ExecuteReader method (provided by the Database class) with a stored procedure. For example, if the application uses an online catalog, you might pass a category ID to the stored procedure to indicate which set of products to retrieve.

The ExecuteReader method returns an object that implements the IDataReader interface. The Database class implementation of ExecuteReader returns a DbDataReader object. The DbDataReader supports data binding and can act as a data source for a number of ASP.NET server controls—for example, the DataList or DataGrid controls. This provides an efficient and flexible way to render the results in a browser.

Using ExecuteDataReader

The following code shows how to use the ExecuteDataReader method with a SQL statement. It assumes that you have resolved the Database class you require and stored a reference in the variable named db.

For more information on instantiating objects, see Creating and Referencing Enterprise Library Objects.

using (IDataReader reader = db.ExecuteReader(CommandType.Text, "Select Name, Address, City From Customers" ))
{
  customerGrid.DataSource = reader;
  customerGrid.DataBind();
}

'Usage
Using reader As IDataReader = db.ExecuteReader(CommandType.Text, "Select Name, Address, City From Customers")

  customerGrid.DataSource = reader
  customerGrid.DataBind()

End Using

There are other overloads available that allow developers to call the ExecuteReader method in different ways. For a description of the types of overloads that are available and the factors that influence which overload you should use, see the section "Writing Code to Use the Database Classes" in Creating a Database Object.

Usage Notes

When using the Data Access Application Block, you do not need to specify a CommandBehavior parameter. By default, the method will automatically set the CommandBehavior property on the underlying reader to CloseConnection unless you specify a transaction when you call the method. If you do specify a transaction, it does not set the CommandBehavior property.

You must close the reader immediately after it finishes. Closing the reader causes the connection to be closed and returned to the connection pool (assuming you have not explicitly disabled connection pooling). The using statement in the example ensures that the reader is correctly disposed.