Using a DataSet to Retrieve Multiple Rows

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.

QuickStart

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

Using ExecuteDataSet

The following code shows how to use the ExecuteDataSet method with a DbCommand object.

Database db = DatabaseFactory.CreateDatabase();

string sqlCommand = "GetProductsByCategory";
DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand);

// Retrieve products from category 7.
int category = 7;
db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, category);

DataSet productDataSet = db.ExecuteDataSet(dbCommand); 

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

Dim sqlCommand As String = "GetProductsByCategory"
Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)

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

' DataSet that will hold the returned results
Dim customerproductDataSet As DataSet = Nothing

customerproductDataSet = db.ExecuteDataSet(dbCommand)

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

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.