ADO.NET Cookbook

2.4. Processing a Batch SQL Statement

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 NextResult( ) method to iterate through and process SQL queries that return multiple result sets.

The sample code contains three event handlers:

Go Button.Click

Defines a SQL batch query statement that selects all Orders and Order Details records from Northwind. Depending on the radio button checked by the user, either a DataAdapter is used to fill a DataSet with multiple tables or a Command object is used to create a DataReader containing multiple result sets. In either case the results are displayed in a data grid for the DataSet and in a text box for the DataReader.

DataSet RadioButton.CheckedChanged

Displays a data grid to show the results of a batch query when loaded into a DataSet. Hides the text box for the DataReader results.

DataReader RadioButton.CheckedChanged

Displays a text box to show the results of a batch query when loaded into a DataReader. Hides the data grid for the DataSet results.

The C# code is shown in Example 2-3.

Example 2-3. File: BatchSqlStatementForm.cs

// Namespaces, variables, and constants using System; using System.Configuration; using System.Text; using System.Data; using System.Data.SqlClient; // Table name constants private const String ORDERS_TABLE = "Orders"; private const String ORDERDETAILS_TABLE = "OrderDetails"; // Field name constants private const String ORDERID_FIELD = ...