ASP.NET中MARS(多重作用結果集)

MARS：Multiple Active Result Sets，多重作用結果集。

MARS能在同一條資料連線(SqlConnection)開啟一個以上的結果集，並讓你同時存取這些結果集。

讓我們回憶一下，我們撰寫資料庫的流程，

1. 連線字串(SqlConnection)
2. SQL定義(SqlCommand)
3. 取回資料(DataReader, DataSet)
4. 處理(...DataBind...)
5. 關閉連線(Close)

不管怎麼寫，大多跳不出這幾個步驟，而如果同一個畫面，同時需要存取幾個DataReader,DataSet，同樣的東西你就要寫兩次，寫三次，這樣是非常沒有效率，這時就非常合適使用MARS技巧。也就是說，步驟1裡的SqlConnection可以同時間給多個SqlCommand來使用，或是說，你需要同時存取同一台Database Server裡的不同資料庫，那也非常合適使用這個MARS技巧。

MARS的設定也異常簡單，只需要在設定連線字串時加入「MultipleActiveResultSets=True」來啟用MARS功能即可。

我們來看一個實例，我們要輸出一個Orders資料表，然後在Orders裡又抓取Orders Details的資料來一起輸出。

無MARS的GridView查詢

以下範例我使用一個下拉式選單，來查詢北風Customers的資料，同時顯示兩個GridView。

GridView.aspx

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

Partial Class GridView
    Inherits System.Web.UI.Page

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        If Not Page.IsPostBack Then
            Dim ddlconn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)

            Dim ddlcmd As New SqlCommand
            ddlcmd.CommandText = "select distinct ContactTitle from customers"
            ddlcmd.CommandType = CommandType.Text
            ddlcmd.Connection = ddlconn
            ddlcmd.Connection.Open()

            Dim ddldr As SqlDataReader
            ddldr = ddlcmd.ExecuteReader

            ddlTitle.DataSource = ddldr
            '顯示文字所對應資料庫的欄位
            ddlTitle.DataTextField = "ContactTitle"
            '欄位值所對應資料庫的欄位
            ddlTitle.DataValueField = "ContactTitle"
            ddlTitle.DataBind()

            ddlcmd.Dispose()
            ddlconn.Dispose()
        End If
    End Sub

    Protected Sub ddlTitle_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlTitle.SelectedIndexChanged
        '使用DataReader
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)

        Dim cmd As New SqlCommand
        cmd.CommandText = "select top 5 * from customers where ContactTitle=@ContactTitle"
        cmd.CommandType = CommandType.Text
        cmd.Connection = conn

        '參數設定
        Dim ContactParam As New SqlParameter
        ContactParam.ParameterName = "@ContactTitle"
        '需得選取的值
        ContactParam.Value = ddlTitle.SelectedValue
        cmd.Parameters.Add(ContactParam)
        cmd.Connection.Open()

        Dim dr As SqlDataReader
        'CommandBehavior.CloseConnection，DataReader自動Close
        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        'DataTable可把DataReader當成資料來源
        'Dim drTodt As New DataTable
        'drTodt.Load(dr)

        '由DataTable傳回DataTableReader物件
        'Dim dtTodr As DataTableReader
        'dtTodr = drTodt.CreateDataReader

        'gvDR.DataSource = drTodt
        'gvDR.DataSource = dtTodr
        gvDR.DataSource = dr
        gvDR.DataBind()

        'drTodt.Dispose()
        'dtTodr.Close 
        cmd.Dispose()
        conn.Dispose()

        '使用DataTable
        Dim dtconn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)

        Dim dtcmd As New SqlCommand
        dtcmd.CommandText = "select top 5 * from customers  where ContactTitle=@dtContactTitle order by customerid desc"
        dtcmd.CommandType = CommandType.Text
        dtcmd.Connection = dtconn

        '參數設定
        Dim dtContactParam As New SqlParameter
        dtContactParam.ParameterName = "@dtContactTitle"
        '取得選取值
        dtContactParam.Value = ddlTitle.SelectedValue
        dtcmd.Parameters.Add(dtContactParam)

        Dim dap As New SqlDataAdapter
        Dim dt As New DataTable
        dap.SelectCommand = dtcmd
        dap.Fill(dt)

        gvDT.DataSource = dt.DefaultView
        gvDT.DataBind()

        dap.Dispose()
        dtcmd.Dispose()
        dtconn.Close()
    End Sub
End Class

你可以看到在SelectedIndexChanged事件裡，我為了要讓兩個GridView顯示不同的資料，所以我必須開關三次SqlConnection（含下拉式控制項的那一次），但其實這是不必要的，接下來我們來看看MARS的使用。

Multiple Active Result Sets

MARS.aspx

<%@ Page Language="VB" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Import Namespace="System.Configuration" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<script runat="server">
    '全域連線物件
    '在連線字串中必須設定「MultipleActiveResultSets=True」

Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)
    
    Protected Sub Page_Load(ByVal sender As Object, _
                    ByVal e As System.EventArgs)

        Dim cmd As New SqlCommand
        Dim OrdersReader As SqlDataReader
       
        cmd.CommandText = _
                " SELECT TOP 5 Customers.CompanyName, Customers.ContactName, " & _
                " Orders.OrderID, Orders.OrderDate, " & _
                " Orders.RequiredDate, Orders.ShippedDate " & _
                " FROM Orders, Customers " & _
                " WHERE Orders.CustomerID = Customers.CustomerID " & _
                " ORDER BY Customers.CompanyName, Customers.ContactName "

        cmd.CommandType = CommandType.Text
        cmd.Connection = conn

        conn.Open()
        OrdersReader = cmd.ExecuteReader()

        gvOrders.DataSource = OrdersReader
        gvOrders.DataBind()

        conn.Close()
    End Sub

    ' RowDataBound事件處理常式
    Protected Sub gvOrders_RowDataBound(ByVal sender As Object, _
                 ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)

        Dim OrderRecord As IDataRecord
        Dim lblOrderDetail As Label

        '從e(DataReader)取得現在連繫的記鍵
        '轉換為IDataRecord interface
        OrderRecord = CType(e.Row.DataItem, IDataRecord)

        '從GridView物件找到Label控制項
        'Label控制項在Order Details裡面
        lblOrderDetail = CType(e.Row.FindControl("lblOrderDetail"), Label)

        If OrderRecord Is Nothing Or lblOrderDetail Is Nothing Then
            Return
        End If

        Dim cmd As New SqlCommand
        Dim OrderDetailReader As SqlDataReader
        cmd.CommandText = _
                "SELECT Products.ProductName, [Order Details].UnitPrice, " & _
                " [Order Details].Quantity, [Order Details].Discount " & _
                " FROM [Order Details], Products " & _
                " WHERE [Order Details].ProductID = Products.ProductID " & _
                " AND [Order Details].OrderID = " + _
                Convert.ToString(OrderRecord("OrderID"))
        cmd.CommandType = CommandType.Text

        '在連線字串中必須設定「MultipleActiveResultSets=True」
        'MARS：Multiple Active Result Sets，多重作用結果集
        '注意這裡，我們使用的是同一條SqlConnection來連線，也就是MARS作用所在
        cmd.Connection = conn

        OrderDetailReader = cmd.ExecuteReader()

        While OrderDetailReader.Read()
            lblOrderDetail.Text += OrderDetailReader(0).ToString() + "
"
        End While

    End Sub
</script>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>MARS範例</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:Label ID="lblCounter" runat="server">
        <br />

　　　　　
        <asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="False" OnRowDataBound="gvOrders_RowDataBound" Width="100%">
            <Columns>
                <asp:BoundField HeaderText="Company Name" DataField="CompanyName"></asp:BoundField>
                <asp:BoundField HeaderText="Contact Name" DataField="ContactName"></asp:BoundField>
                <asp:TemplateField>
                    <HeaderTemplate>
                        Order Detail
                    </HeaderTemplate>
                    <ItemTemplate>
                        <asp:Label ID="lblOrderDetail" runat="server">
                    </ItemTemplate>
                </asp:TemplateField>
                <asp:BoundField HeaderText="Order Date" DataField="orderdate" DataFormatString="{0:d}">
                </asp:BoundField>
                <asp:BoundField HeaderText="Required Date" DataField="requireddate" DataFormatString="{0:d}">
                </asp:BoundField>
                <asp:BoundField HeaderText="Shipped Date" DataField="shippeddate" DataFormatString="{0:d}">
                </asp:BoundField>
            </Columns>
        </asp:GridView>
        <br />
        <br />
    </div>
    </form>
</body>
</html>

範例的重點在71裡RowDataBound事件裡使用同一個SqlConnection物件來連接。MARS在你需要同一Web Form裡需要使用越來越多結果集時，你就可以發現MARS的效果，讓我們的程式更有彈性，更靈活。