操作資料 (MARS)

發行項
05/20/2011

引進 Multiple Active Result Set (MARS) 之前，開發人員必須使用多重連接或伺服器端游標來解析某些案例。此外，當在交易中使用多重連接時，需要繫結連接 (使用 sp_getbindtoken 及 sp_bindsession)。下列案例顯示如何以啟用 MARS 的連接取代多重連接。

搭配使用多個命令與 MARS

下列主控台應用程式示範如何使用具有兩個 SqlCommand 物件的兩個 SqlDataReader 物件，及啟用 MARS 的單一 SqlConnection 物件。

範例

範例會開啟到 AdventureWorks 資料庫的單一連接。使用 SqlCommand 物件，會建立 SqlDataReader。當使用該讀取器時，會開啟第二個 SqlDataReader，使用來自第一個 SqlDataReader 的資料做為第二個讀取器之 WHERE 子句的輸入。

注意事項
下列範例使用包含於 SQL Server 2005 的 AdventureWorks 範例資料庫。範例程式碼中提供的連接字串假設本機電腦已安裝並可使用資料庫。視環境需要修改連接字串。

Option Strict On
Option Explicit On

Imports System
Imports System.Data
Imports System.Data.SqlClient
Module Module1
  Sub Main()
    ' By default, MARS is disabled when connecting
    ' to a MARS-enabled host such as SQL Server 2005.
    ' It must be enabled in the connection string.
    Dim connectionString As String = GetConnectionString()

    Dim vendorID As Integer

    Dim vendorCmd As SqlCommand
    Dim productCmd As SqlCommand
    Dim productReader As SqlDataReader

    Dim vendorSQL As String = & _ 
      "SELECT VendorId, Name FROM Purchasing.Vendor"
    Dim productSQL As String = _
        "SELECT Production.Product.Name FROM Production.Product " & _
        "INNER JOIN Purchasing.ProductVendor " & _
        "ON Production.Product.ProductID = " & _
        "Purchasing.ProductVendor.ProductID " & _
        "WHERE Purchasing.ProductVendor.VendorID = @VendorId"

    Using awConnection As New SqlConnection(connectionString)
      vendorCmd = New SqlCommand(vendorSQL, awConnection)
      productCmd = New SqlCommand(productSQL, awConnection)
      productCmd.Parameters.Add("@VendorId", SqlDbType.Int)

      awConnection.Open()
      Using vendorReader As SqlDataReader = vendorCmd.ExecuteReader()
        While vendorReader.Read()
          Console.WriteLine(vendorReader("Name"))

          vendorID = CInt(vendorReader("VendorId"))

          productCmd.Parameters("@VendorId").Value = vendorID

          ' The following line of code requires
          ' a MARS-enabled connection.
          productReader = productCmd.ExecuteReader()
          Using productReader
            While productReader.Read()
              Console.WriteLine("  " & CStr(productReader("Name")))
            End While
          End Using
        End While
      End Using
    End Using

    Console.WriteLine("Press any key to continue")
    Console.ReadLine()
  End Sub

  Function GetConnectionString() As String
    ' To avoid storing the connection string in your code,
    ' you can retrive it from a configuration file.
    Return "Data Source=(local);Integrated Security=SSPI;" & _
      "Initial Catalog=AdventureWorks; MultipleActiveResultSets=True"
  End Function
End Module

using System;
using System.Data;
using System.Data.SqlClient;

class Class1
{
static void Main()
{
  // By default, MARS is disabled when connecting
  // to a MARS-enabled host such as SQL Server 2005.
  // It must be enabled in the connection string.
  string connectionString = GetConnectionString();

  int vendorID;
  SqlDataReader productReader = null;
  string vendorSQL = 
    "SELECT VendorId, Name FROM Purchasing.Vendor";
  string productSQL = 
    "SELECT Production.Product.Name FROM Production.Product " +
    "INNER JOIN Purchasing.ProductVendor " +
    "ON Production.Product.ProductID = " + 
    "Purchasing.ProductVendor.ProductID " +
    "WHERE Purchasing.ProductVendor.VendorID = @VendorId";

  using (SqlConnection awConnection = 
    new SqlConnection(connectionString))
  {
    SqlCommand vendorCmd = new SqlCommand(vendorSQL, awConnection);
    SqlCommand productCmd = 
      new SqlCommand(productSQL, awConnection);

    productCmd.Parameters.Add("@VendorId", SqlDbType.Int);

    awConnection.Open();
    using (SqlDataReader vendorReader = vendorCmd.ExecuteReader())
    {
      while (vendorReader.Read())
      {
        Console.WriteLine(vendorReader["Name"]);

        vendorID = (int)vendorReader["VendorId"];

        productCmd.Parameters["@VendorId"].Value = vendorID;
        // The following line of code requires
        // a MARS-enabled connection.
        productReader = productCmd.ExecuteReader();
        using (productReader)
        {
          while (productReader.Read())
          {
            Console.WriteLine("  " +
              productReader["Name"].ToString());
          }
        }
      }
  }
      Console.WriteLine("Press any key to continue");
      Console.ReadLine();
    }
  }
  private static string GetConnectionString()
  {
    // To avoid storing the connection string in your code,
    // you can retrive it from a configuration file.
    return "Data Source=(local);Integrated Security=SSPI;" + 
      "Initial Catalog=AdventureWorks;MultipleActiveResultSets=True";
  }
}

使用 MARS 讀取及更新資料

MARS 允許將連接用於讀取作業及資料操作語言 (DML) 作業 (具有多個暫止作業)。使用此功能，應用程式即無需處理連接繁忙錯誤。此外，您可以使用 MARS 代替通常會消耗更多資源的伺服器端游標。最後，因為多個作業可在單一連接上進行操作，所以它們可共用相同的交易內容，無需使用 sp_getbindtoken 及 sp_bindsession 系統預存程序。

範例

下列主控台應用程式示範如何使用具有三個 SqlCommand 物件的兩個 SqlDataReader 物件，及啟用 MARS 的單一 SqlConnection 物件。第一個命令物件會擷取信用評等為 5 的廠商清單。第二個命令物件會使用 SqlDataReader 提供的廠商 ID，以載入第二個 SqlDataReader 及該特定廠商的所有產品。第二個 SqlDataReader 會造訪每個產品記錄。會執行計算以決定新的 OnOrderQty。然後會使用第三個命令物件，以新值更新 ProductVendor 資料表。這整個處理序會在單一交易中發生，並在結束時復原。

注意事項
下列範例使用包含於 SQL Server 2005 的 AdventureWorks 範例資料庫。範例程式碼中提供的連接字串假設本機電腦已安裝並可使用資料庫。視環境需要修改連接字串。

Option Strict On
Option Explicit On

Imports System
Imports System.Data
Imports System.Data.SqlClient

Module Module1

  Sub Main()
    ' By default, MARS is disabled when connecting
    ' to a MARS-enabled host such as SQL Server 2005.
    ' It must be enabled in the connection string.
    Dim connectionString As String = GetConnectionString()

    Dim updateTx As SqlTransaction
    Dim vendorCmd As SqlCommand
    Dim prodVendCmd As SqlCommand
    Dim updateCmd As SqlCommand

    Dim prodVendReader As SqlDataReader

    Dim vendorID As Integer
    Dim productID As Integer
    Dim minOrderQty As Integer
    Dim maxOrderQty As Integer
    Dim onOrderQty As Integer
    Dim recordsUpdated As Integer
    Dim totalRecordsUpdated As Integer

    Dim vendorSQL As String = _
        "SELECT VendorID, Name FROM Purchasing.Vendor " & _
        "WHERE CreditRating = 5"
    Dim prodVendSQL As String = _
        "SELECT ProductID, MaxOrderQty, MinOrderQty, OnOrderQty " & _
        "FROM Purchasing.ProductVendor " & _
        "WHERE VendorID = @VendorID"
    Dim updateSQL As String = _
        "UPDATE Purchasing.ProductVendor " & _ 
        "SET OnOrderQty = @OrderQty " & _
        "WHERE ProductID = @ProductID AND VendorID = @VendorID"

    Using awConnection As New SqlConnection(connectionString)
      awConnection.Open()
      updateTx = awConnection.BeginTransaction()

      vendorCmd = New SqlCommand(vendorSQL, awConnection)
      vendorCmd.Transaction = updateTx

      prodVendCmd = New SqlCommand(prodVendSQL, awConnection)
      prodVendCmd.Transaction = updateTx
      prodVendCmd.Parameters.Add("@VendorId", SqlDbType.Int)

      updateCmd = New SqlCommand(updateSQL, awConnection)
      updateCmd.Transaction = updateTx
      updateCmd.Parameters.Add("@OrderQty", SqlDbType.Int)
      updateCmd.Parameters.Add("@ProductID", SqlDbType.Int)
      updateCmd.Parameters.Add("@VendorID", SqlDbType.Int)

      Using vendorReader As SqlDataReader = vendorCmd.ExecuteReader()
        While vendorReader.Read()
          Console.WriteLine(vendorReader("Name"))

          vendorID = CInt(vendorReader("VendorID"))
          prodVendCmd.Parameters("@VendorID").Value = vendorID
          prodVendReader = prodVendCmd.ExecuteReader()

          Using prodVendReader
            While (prodVendReader.Read)
              productID = CInt(prodVendReader("ProductID"))

              If IsDBNull(prodVendReader("OnOrderQty")) Then
                minOrderQty = CInt(prodVendReader("MinOrderQty"))
                onOrderQty = minOrderQty
              Else
                maxOrderQty = CInt(prodVendReader("MaxOrderQty"))
                onOrderQty = CInt(maxOrderQty / 2)
              End If

              updateCmd.Parameters("@OrderQty").Value = onOrderQty
              updateCmd.Parameters("@ProductID").Value = productID
              updateCmd.Parameters("@VendorID").Value = vendorID

              recordsUpdated = updateCmd.ExecuteNonQuery()
              totalRecordsUpdated += recordsUpdated
            End While
          End Using
        End While
      End Using

      Console.WriteLine("Total Records Updated: " & _ 
        CStr(totalRecordsUpdated))
      updateTx.Rollback()
      Console.WriteLine("Transaction Rolled Back")
    End Using

    Console.WriteLine("Press any key to continue")
    Console.ReadLine()

  End Sub

  Function GetConnectionString() As String
    ' To avoid storing the connection string in your code,
    ' you can retrive it from a configuration file.
    Return "Data Source=(local);Integrated Security=SSPI;" & _
      "Initial Catalog=AdventureWorks;MultipleActiveResultSets=True"
  End Function
End Module

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

class Program
{
static void Main()
{
  // By default, MARS is disabled when connecting
  // to a MARS-enabled host such as SQL Server 2005.
  // It must be enabled in the connection string.
  string connectionString = GetConnectionString();

  SqlTransaction updateTx = null;
  SqlCommand vendorCmd = null;
  SqlCommand prodVendCmd = null;
  SqlCommand updateCmd = null;

  SqlDataReader prodVendReader = null;

  int vendorID = 0;
  int productID = 0;
  int minOrderQty = 0;
  int maxOrderQty = 0;
  int onOrderQty = 0;
  int recordsUpdated = 0;
  int totalRecordsUpdated = 0;

  string vendorSQL =
      "SELECT VendorID, Name FROM Purchasing.Vendor " + 
      "WHERE CreditRating = 5";
  string prodVendSQL =
      "SELECT ProductID, MaxOrderQty, MinOrderQty, OnOrderQty " +
      "FROM Purchasing.ProductVendor " + 
      "WHERE VendorID = @VendorID";
  string updateSQL =
      "UPDATE Purchasing.ProductVendor " + 
      "SET OnOrderQty = @OrderQty " +
      "WHERE ProductID = @ProductID AND VendorID = @VendorID";

  using (SqlConnection awConnection = 
    new SqlConnection(connectionString))
  {
    awConnection.Open();
    updateTx = awConnection.BeginTransaction();

    vendorCmd = new SqlCommand(vendorSQL, awConnection);
    vendorCmd.Transaction = updateTx;

    prodVendCmd = new SqlCommand(prodVendSQL, awConnection);
    prodVendCmd.Transaction = updateTx;
    prodVendCmd.Parameters.Add("@VendorId", SqlDbType.Int);

    updateCmd = new SqlCommand(updateSQL, awConnection);
    updateCmd.Transaction = updateTx;
    updateCmd.Parameters.Add("@OrderQty", SqlDbType.Int);
    updateCmd.Parameters.Add("@ProductID", SqlDbType.Int);
    updateCmd.Parameters.Add("@VendorID", SqlDbType.Int);

    using (SqlDataReader vendorReader = vendorCmd.ExecuteReader())
    {
      while (vendorReader.Read())
      {
        Console.WriteLine(vendorReader["Name"]);

        vendorID = (int) vendorReader["VendorID"];
        prodVendCmd.Parameters["@VendorID"].Value = vendorID;
        prodVendReader = prodVendCmd.ExecuteReader();

        using (prodVendReader)
        {
          while (prodVendReader.Read())
          {
            productID = (int) prodVendReader["ProductID"];

            if (prodVendReader["OnOrderQty"] == DBNull.Value)
            {
              minOrderQty = (int) prodVendReader["MinOrderQty"];
              onOrderQty = minOrderQty;
            }
            else
            {
              maxOrderQty = (int) prodVendReader["MaxOrderQty"];
              onOrderQty = (int)(maxOrderQty / 2);
            }

            updateCmd.Parameters["@OrderQty"].Value = onOrderQty;
            updateCmd.Parameters["@ProductID"].Value = productID;
            updateCmd.Parameters["@VendorID"].Value = vendorID;

            recordsUpdated = updateCmd.ExecuteNonQuery();
            totalRecordsUpdated += recordsUpdated;
          }
        }
      }
    }
    Console.WriteLine("Total Records Updated: " + 
      totalRecordsUpdated.ToString());
    updateTx.Rollback();
    Console.WriteLine("Transaction Rolled Back");
  }

  Console.WriteLine("Press any key to continue");
  Console.ReadLine();
}
private static string GetConnectionString()
{
  // To avoid storing the connection string in your code,
  // you can retrive it from a configuration file.
  return "Data Source=(local);Integrated Security=SSPI;" + 
    "Initial Catalog=AdventureWorks;" + 
    "MultipleActiveResultSets=True";
  }
}

請參閱

其他資源

Multiple Active Result Set (MARS)

共用方式為

操作資料 (MARS)

搭配使用多個命令與 MARS

範例

使用 MARS 讀取及更新資料

範例

請參閱

其他資源

其他資源