Manipular datos (MARS)

2011-05-20

Antes de la introducción de Varios conjuntos de resultados activos (MARS), los programadores tenían que utilizar varias conexiones o cursores en el servidor para solucionar determinadas situaciones. Además, cuando se utilizaban varias conexiones en una situación transaccional, era necesario enlazar las conexiones (con sp_getbindtoken y sp_bindsession). En las siguientes situaciones se muestra cómo utilizar una conexión habilitada para MARS en lugar de varias conexiones.

Uso de varias comandos con MARS

La siguiente aplicación de consola demuestra cómo utilizar dos objetos SqlDataReader con dos objetos SqlCommand y un solo objeto SqlConnection teniendo MARS habilitado.

Ejemplo

En el ejemplo se abre una única conexión a la base de datos AdventureWorks. Al utilizar un objeto SqlCommand, se crea un objeto SqlDataReader. A medida que se utiliza el lector, se abre un segundo SqlDataReader, que utiliza datos del primer SqlDataReader como entrada a la cláusula WHERE del segundo lector.

Nota
En el siguiente ejemplo se utiliza la base de datos de ejemplo AdventureWorks que se incluye con SQL Server 2005.La cadena de conexión proporcionada en el código de ejemplo asume que la base de datos está instalada y disponible en el equipo local.Modifique la cadena de conexión según sea necesario para su entorno.

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";
  }
}

Lectura y actualización de datos con MARS

MARS permite que se utilice una conexión para las operaciones de lectura y de lenguaje de manipulación de datos (DML) con más de una operación pendiente. Esta característica elimina la necesidad de que las aplicaciones solucionen los errores de falta de disponibilidad de las conexiones. Además, MARS puede reemplazar al uso de cursores en el servidor, que generalmente consume más recursos. Finalmente, como es posible realizar varias operaciones con una sola conexión, pueden compartir el mismo contexto de transacción, lo que elimina la necesidad de utilizar los procedimientos almacenados del sistema sp_getbindtoken y sp_bindsession.

Ejemplo

La siguiente aplicación de consola demuestra cómo utilizar dos objetos SqlDataReader con tres objetos SqlCommand y un solo objeto SqlConnection teniendo MARS habilitado. El primer objeto de comando recupera una lista de proveedores cuya clasificación crediticia es 5. El segundo objeto de comando utiliza el id. de proveedor proporcionado a partir de un SqlDataReader para cargar el segundo SqlDataReader con todos los productos de ese proveedor en particular. El segundo SqlDataReader visita cada registro de producto. Para determinar cuál debe ser la nueva OnOrderQty se realiza un cálculo. Luego, el tercer objeto de comando se utiliza para actualizar la tabla ProductVendor con el nuevo valor. Todo este proceso tiene lugar en una única transacción, que al final se revierte.

Nota
En el siguiente ejemplo se utiliza la base de datos de ejemplo AdventureWorks que se incluye con SQL Server 2005.La cadena de conexión proporcionada en el código de ejemplo asume que la base de datos está instalada y disponible en el equipo local.Modifique la cadena de conexión según sea necesario para su entorno.

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";
  }
}

Vea también

Otros recursos

Varios conjuntos de resultados activos (MARS)

Compartir a través de

Manipular datos (MARS)

Uso de varias comandos con MARS

Ejemplo

Lectura y actualización de datos con MARS

Ejemplo

Vea también

Otros recursos

Recursos adicionales