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 aDataSet
with multiple tables or aCommand
object is used to create aDataReader
containing multiple result sets. In either case the results are displayed in a data grid for theDataSet
and in a text box for theDataReader
.- 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 theDataReader
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 theDataSet
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 = ...
Get ADO.NET Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.