Learning ADO.NET 3.5 Cookbook:(3) Executing Multiple Commands on a Single Connection

 中文介绍：在一个连接下执行多重SQL命令

Problem

You need to execute multiple commands using a single connection.

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();

            // Iterate over the reader with the SalesOrderHeader records
            while (drHeader.Read())
            {
                Console.WriteLine("SalesOrderID = {0}\tTotalDue = {1}",
                    drHeader["SalesOrderID"], drHeader["TotalDue"]);

                // Create a DataReader with detail for the sales order
                SqlCommand cmdDetail = connection.CreateCommand();
                cmdDetail.CommandText = "SELECT ProductID, OrderQty FROM " +
                    "Sales.SalesOrderDetail WHERE SalesOrderID=" +
                    drHeader["SalesOrderID"];

                // Iterate over the reader with the SalesOrderDetail records
                using (SqlDataReader drDetail = cmdDetail.ExecuteReader())
                {
                    while (drDetail.Read())
                        Console.WriteLine("\tProductID = {0}\tOrderQty = {1}",
                        drDetail["ProductID"], drDetail["OrderQty"]);
                    drDetail.Dispose();
                }
                Console.WriteLine();
            }
 
            connection.Close();

            Console.WriteLine("Press any key to continue.");
            Console.ReadKey();
        }
    }
}