ADO.NET 3.5 Cookbook, 2nd Edition

3.13. Executing a Query That Returns Multiple Result Sets

Problem

You have a batch SQL query that returns multiple result sets and you need to work with the result sets in ADO.NET.

Solution

Use the techniques shown in this solution to use a batch query with both a DataSet and with a DataReader.

The solution defines a SQL batch query statement that selects the top three rows of each Sales.SalesOrderHeader and Sales.SalesOrderDetails from AdventureWorks.

A DataAdapter is used to load the multiple result sets into a DataSet. The data values in the DataSet are accessed through the collection of DataTable objects in the DataSet and output to the console.

The NextResult() method of the DataReader is used to iterate through and the multiple result sets that the batch query returns. The DataReader values are output to the console.

The C# code in Program.cs in the project ExecuteBatchQuery is shown in Example 3-17.

Example 3-17. File: Program.cs for ExecuteBatchQuery solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace ExecuteBatchQuery
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect = "SELECT TOP 3 * FROM Sales.SalesOrderHeader;" +
                "SELECT TOP 3 * FROM Sales.SalesOrderDetail";

            int rsNumber;

            // SQL batch using a DataSet
            Console.WriteLine("---DataSet---");

            // Fill the DataSet with the results of the batch query. SqlDataAdapter da = new SqlDataAdapter(sqlSelect, ...