ADO.NET 3.5 Cookbook, 2nd Edition

10.1. Executing Multiple Commands on a Single Connection

Problem

You need to execute multiple commands using a single connection.

Solution

The solution creates a DataReader that contains the SalesOrderID and TotalDue fields for the top three records from the Sales.SalesOrderHeader table in AdventureWorks and iterates over and outputs these records to the console. For each record, a second DataReader retrieves the ProductID and OrderQty fields for the SalesOrderID from the Sales.SalesOrderDetail table—the same connection used for the first DataReader is used to create the second DataReader. The solution iterates over and outputs records from the SalesOrderDetail table in the second DataReader.

The C# code in Program.cs in the project ExecuteMultipleCommandsSingleConnection is shown in Example 10-1.

Example 10-1. File: Program.cs for ExecuteMultipleCommandsSingleConnection solution

using System; using System.Data.SqlClient; namespace ExecuteMultipleCommandsSingleConnection { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;" + "MultipleActiveResultSets=true"; SqlConnection connection = new SqlConnection(sqlConnectString); // create a DataReader with the top 3 sales header records SqlCommand cmdHeader = connection.CreateCommand(); cmdHeader.CommandText = "SELECT TOP 3 SalesOrderID, TotalDue FROM Sales.SalesOrderHeader"; connection.Open(); SqlDataReader drHeader = cmdHeader.ExecuteReader(); ...