Using a DbDataReader to Retrieve Multiple Rows

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
The latest Enterprise Library information can be found at the Enterprise Library site.

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.

QuickStart

For an extended example of how to use ExecuteDataReader to retrieve multiple rows of data, see the QuickStart walkthrough, Walkthrough: Using a DbDataReader to Retrieve Multiple Rows.

Using ExecuteDataReader

The following code shows how to use the ExecuteDataReader method with a SQL statement.

Database db = DatabaseFactory.CreateDatabase();

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

'Usage
Dim db As Database = DatabaseFactory.CreateDatabase()

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

  customerGrid.DataSource = dataReader
  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 Adding Application Code.

Usage Notes

The ExecuteReader method opens a connection to the database and uses the CommandBehavior.CloseConnection method to couple the lifetime of the connection to that of the reader. Therefore, 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 disposed of.

Retired Content
This content is outdated and is no longer being maintained. It is provided as a courtesy for individuals who are still using these technologies. This page may contain URLs that were valid when originally published, but now link to sites or pages that no longer exist.
The latest Enterprise Library information can be found at the Enterprise Library site.