Recipe 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       = "OrderID";

// Relation name constants
private const String ORDERS_ORDERDETAILS_RELATION =
    "Orders_OrderDetails_Relation";

//  . . . 

private void goButton_Click(object sender, System.EventArgs e)
{
    // Batch SQL query returning two result sets
    String sqlText = "select OrderID, CustomerID, EmployeeID, OrderDate," +
        "RequiredDate, ShippedDate, ShipVia, Freight, ShipName, " +
        "ShipAddress, ShipCity, ShipRegion, ShipPostalCode, " +
        "ShipCountry " +
        "FROM Orders;" +
        "SELECT OrderID, ProductID, UnitPrice, Quantity, Discount " +
        "FROM [Order Details];";

    if (dataSetRadioButton.Checked)
    {
        SqlDataAdapter da = new SqlDataAdapter(sqlText,
            ConfigurationSettings.AppSettings["Sql_ConnectString"]);

        // Map the automatically generated table names Table and Table1.
        da.TableMappings.Add("Table", ORDERS_TABLE);
        da.TableMappings.Add("Table1", ORDERDETAILS_TABLE);

        // Fill the DataSet with the results of the batch query.
        DataSet ds = new DataSet( );
        da.Fill(ds);

        // Add a relation between the Order and Order Details tables.
        ds.Relations.Add(new DataRelation(ORDERS_ORDERDETAILS_RELATION,
            ds.Tables[ORDERS_TABLE].Columns[ORDERID_FIELD],
            ds.Tables[ORDERDETAILS_TABLE].Columns[ORDERID_FIELD],
            true));

        // Bind the default view of the Orders table to the grid.
        resultDataGrid.DataSource = ds.Tables[ORDERS_TABLE];
    }
    else
    {
        StringBuilder sb = new StringBuilder( );

        // Create a new connection and command to fill the DataReader.
        SqlConnection conn = new SqlConnection(
            ConfigurationSettings.AppSettings["Sql_ConnectString"]);
        SqlCommand cmd = new SqlCommand(sqlText, conn);
        conn.Open( );
        
        // Execute the batch query.
        SqlDataReader dr = cmd.ExecuteReader( );

        // Process each result set in the DataReader.
        int nResultSet = 0;
        do
        {
            sb.Append("RESULT SET: " + (++nResultSet) +
                Environment.NewLine);

            // Iterate over the rows in the DataReader.
            while(dr.Read( ))
            {
                // Output each field in the DataReader row.
                for(int i = 0; i < dr.FieldCount; i++)
                    sb.Append(dr[i] + "; ");

                sb.Append(Environment.NewLine);
            }

            sb.Append(Environment.NewLine);
        } while(dr.NextResult( ));
        dr.Close( );
        conn.Close( );

        // Display the results.
        resultTextBox.Text = sb.ToString( );
    }
}

private void dataSetRadioButton_CheckedChanged(object sender,
    System.EventArgs e)
{
    // Display the data grid for DataSet results.
    resultDataGrid.Visible = true;
    resultTextBox.Visible = false;
}

private void dataReaderRadioButton_CheckedChanged(object sender,
    System.EventArgs e)
{
    // Display the text box for DataReader results.
    resultDataGrid.Visible = false;
    resultTextBox.Visible = true;        
}

Discussion

A batch command is defined as a collection of SQL statements separated by semicolons. The batch command can fill a DataSet or build a DataReader. Working with the results is different for each of these scenarios as described in the following sections.

The batch statement can also be contained in a stored procedure. Everything is the same as for the example where the SQL batch command is defined in the code once the Command is executed.

The Oracle .NET data provider does not support batch SQL statements. To execute a batch query against an Oracle database it is necessary to use an Oracle package that returns multiple REF CURSOR output parameters. For more information, see the solution in Recipe 2.20.

DataSet

The Fill( ) method of the DataAdapter adds multiple result sets from a batch query to a DataSet. One table is created in the DataSet for each result set. By default, these tables will be named Table, Table1, Table2, and so on. You can make these names more meaningful by specifying table mappings in the TableMappings collection of the DataAdapter. For more information about using table mappings, see Recipe 2.16.

Data relationships between the tables added with a batch query must be created programmatically. As with non-batch queries, you can define the relations and foreign key constraints for the tables prior to filling them with the results of the batch query.

When using the Fill( ) method of the DataAdapter with a batch fill operation, if one of the result sets contains an error, all subsequent processing is skipped and result sets are not added to the DataSet.

When using the FillSchema( ) method of the DataAdapter with a batch query and the OLE DB data provider, the schema is returned for only the first query. To retrieve the schema for all result sets, use the Fill( ) method with the MissingSchemaAction argument set to AddWithKey.

DataReader

As with a single statement command, a batch command is used to build a DataReader by calling the ExecuteReader( ) method of the Command object. The NextResult( ) method of the DataReader is used to advance to the next result set where the method returns true if there is another result set. Iterating over the DataReader is demonstrated in the sample code using the following technique:

do {

     . . .  process the result set        

} while(dr.NextResult( ));

Initially, the DataReader is positioned on the first result set. Once NextResult( ) is called there is no way to return to the previous result set.