Using a DataSet to Retrieve Multiple Rows

  • Article

In a multi-tier system, you may need to pass data from a data access component to a middle-tier business component. The data is retrieved from the database and sent back, through the data access layer, to the business layer. The information is contained in the DataSet object.

Typical Goals

When you access data in a multi-tier system, you generally have one of the following goals:

  • You want to retrieve multiple tables or tables from different data sources.
  • You want to exchange data with either another application or a component such as an XML Web service.
  • You have to perform extensive processing with each record you get from the database. If you use a data command and data reader, processing each record as you read it can result in the connection being held open for a long period, which in turn can affect the performance and scalability of your application.
  • You have to access interdependent records for data processing (for example, looking up information in related records).
  • You want to perform XML operations, such as XSLT transformations on the data.

Solution

Using a DataSet object is a good way to meet these goals. The ADO.NET DataSet is a data container that consists of one or more data tables and, optionally, the relationships that link the tables together. It is a disconnected object and has no knowledge of any underlying data source. It supports XML manipulation of data and is an ideal vehicle for passing data between the components and tiers of a multi-tier application.

Using ExecuteDataSet

The following code shows how to use the ExecuteDataSet method with a DbCommand object. 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.

string sql = "GetProductsByCategory";
DbCommand cmd = db.GetStoredProcCommand(sql);

// Retrieve products from category 7.
int category = 7;
db.AddInParameter(cmd, "CategoryID", DbType.Int32, category);
 
DataSet productDataSet = db.ExecuteDataSet(cmd); 

'Usage
Dim sql As String = "GetProductsByCategory"
Dim cmd As DbCommand = db.GetStoredProcCommand(sql)

' Retrieve products from the category 7.
Dim category As Integer = 7
db.AddInParameter(cmd, "CategoryID", DbType.Int32, category)

Dim productDataSet As DataSet = db.ExecuteDataSet(cmd)

There are other overloads available that allow developers to call the ExecuteDataSet 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

Consider the following when you use the ExecuteDataSet method overloads:

  • The Data Access Application Block generates DataSet objects with default names for the contained DataTable objects: for example, Table, Table1, and Table2.
  • If you want to reuse an existing DataSet instead of creating a new one to hold the results of your query, use the LoadDataSet method in the Database class.