Walkthrough: Using a DataSet to Retrieve Multiple Rows

This walkthrough demonstrates how to use a DataSet to retrieve multiple rows of data. It assumes that the following stored procedure has been created in the database to which the connection string in the code refers.

CREATE Procedure GetProductsByCategory
 (
  @CategoryID int 
 )
 AS
 SELECT ProductID, ProductName, CategoryID, UnitPrice, LastUpdate
 FROM Products
 WHERE CategoryID = @CategoryID

To reproduce the demonstration

1. Configure the database. For the necessary steps, see "QuickStart Configuration" in Data Access QuickStarts.

2. Create a method named GetProductsInCategory that accepts a category identifier as an integer parameter and returns a DataSet. Add the following code to this method.

   public DataSet GetProductsInCategory(int Category)
   {
   }
   

   'Usage
   Public Function GetProductsInCategory(ByRef Category As Integer) As DataSet
   End Function
   

3. Create the database by adding the following code. The factory creates a Database object that has the default configuration.

   Database db = DatabaseFactory.CreateDatabase();
   

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

4. Create the command by adding the following code. This code creates the DbCommand object used with a stored procedure. In this case, the code calls GetProductsByCategory. The DbCommand takes one input parameter, CategoryID.

   string sqlCommand = "GetProductsByCategory";
   DbCommand dbCommand = db.GetStoredProcCommand(sqlCommand); 
   db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category);
   

   'Usage
   Dim sqlCommand As String = "GetProductsByCategory"
   Dim dbCommand As DbCommand = db.GetStoredProcCommand(sqlCommand)
   db.AddInParameter(dbCommand, "CategoryID", DbType.Int32, Category)
   

5. Declare the DataSet, which will be created by the ExecuteDataSet call.

   // DataSet that will hold the returned results.
   DataSet productDataSet = null;
   

   'Usage
   ' DataSet that will hold the returned results.
   Dim productDataSet As DataSet = Nothing
   

6. Call ExecuteDataSet by adding the following code. ExecuteDataSet passes the DbCommand object, which indicates that GetProductsByCategory will populate the DataSet.

   productDataSet = db.ExecuteDataSet(dbCommand);
   

   'Usage
   productDataSet = db.ExecuteDataSet(dbCommand)
   

7. Return the results to the user interface code. The user interface can process the results by adding the following code, which binds the returned DataSet to a DataGrid.

   resultsDataGrid.SetDataBinding(productDataSet, "Table");
   

   'Usage
   resultsDataGrid.SetDataBinding(productDataSet, "Table")