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

view sourceprint?
01Imports System.Data
02Imports System.Data.SqlClient
03Imports System.Configuration
04 
05Partial Class GridView
06    Inherits System.Web.UI.Page
07 
08    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
09        If Not Page.IsPostBack Then
10            Dim ddlconn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)
11 
12            Dim ddlcmd As New SqlCommand
13            ddlcmd.CommandText = "select distinct ContactTitle from customers"
14            ddlcmd.CommandType = CommandType.Text
15            ddlcmd.Connection = ddlconn
16            ddlcmd.Connection.Open()
17 
18            Dim ddldr As SqlDataReader
19            ddldr = ddlcmd.ExecuteReader
20 
21            ddlTitle.DataSource = ddldr
22            '顯示文字所對應資料庫的欄位
23            ddlTitle.DataTextField = "ContactTitle"
24            '欄位值所對應資料庫的欄位
25            ddlTitle.DataValueField = "ContactTitle"
26            ddlTitle.DataBind()
27 
28            ddlcmd.Dispose()
29            ddlconn.Dispose()
30        End If
31    End Sub
32 
33    Protected Sub ddlTitle_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlTitle.SelectedIndexChanged
34        '使用DataReader
35        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)
36 
37        Dim cmd As New SqlCommand
38        cmd.CommandText = "select top 5 * from customers where ContactTitle=@ContactTitle"
39        cmd.CommandType = CommandType.Text
40        cmd.Connection = conn
41 
42        '參數設定
43        Dim ContactParam As New SqlParameter
44        ContactParam.ParameterName = "@ContactTitle"
45        '需得選取的值
46        ContactParam.Value = ddlTitle.SelectedValue
47        cmd.Parameters.Add(ContactParam)
48        cmd.Connection.Open()
49 
50        Dim dr As SqlDataReader
51        'CommandBehavior.CloseConnection，DataReader自動Close
52        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
53 
54        'DataTable可把DataReader當成資料來源
55        'Dim drTodt As New DataTable
56        'drTodt.Load(dr)
57 
58        '由DataTable傳回DataTableReader物件
59        'Dim dtTodr As DataTableReader
60        'dtTodr = drTodt.CreateDataReader
61 
62        'gvDR.DataSource = drTodt
63        'gvDR.DataSource = dtTodr
64        gvDR.DataSource = dr
65        gvDR.DataBind()
66 
67        'drTodt.Dispose()
68        'dtTodr.Close
69        cmd.Dispose()
70        conn.Dispose()
71 
72        '使用DataTable
73        Dim dtconn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)
74 
75        Dim dtcmd As New SqlCommand
76        dtcmd.CommandText = "select top 5 * from customers  where ContactTitle=@dtContactTitle order by customerid desc"
77        dtcmd.CommandType = CommandType.Text
78        dtcmd.Connection = dtconn
79 
80        '參數設定
81        Dim dtContactParam As New SqlParameter
82        dtContactParam.ParameterName = "@dtContactTitle"
83        '取得選取值
84        dtContactParam.Value = ddlTitle.SelectedValue
85        dtcmd.Parameters.Add(dtContactParam)
86 
87        Dim dap As New SqlDataAdapter
88        Dim dt As New DataTable
89        dap.SelectCommand = dtcmd
90        dap.Fill(dt)
91 
92        gvDT.DataSource = dt.DefaultView
93        gvDT.DataBind()
94 
95        dap.Dispose()
96        dtcmd.Dispose()
97        dtconn.Close()
98    End Sub
99End Class

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

Multiple Active Result Sets


MARS.aspx

view sourceprint?
1<%@ Page Language="VB" %>
2<%@ Import Namespace="System.Data" %>
3<%@ Import Namespace="System.Data.SqlClient" %>
4<%@ Import Namespace="System.Configuration" %>
5<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
6 
7<script runat="server">
8    '全域連線物件
9    '在連線字串中必須設定「MultipleActiveResultSets=True」
view sourceprint?
01Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)
02     
03    Protected Sub Page_Load(ByVal sender As Object, _
04                    ByVal e As System.EventArgs)
05 
06        Dim cmd As New SqlCommand
07        Dim OrdersReader As SqlDataReader
08        
09        cmd.CommandText = _
10                " SELECT TOP 5 Customers.CompanyName, Customers.ContactName, " & _
11                " Orders.OrderID, Orders.OrderDate, " & _
12                " Orders.RequiredDate, Orders.ShippedDate " & _
13                " FROM Orders, Customers " & _
14                " WHERE Orders.CustomerID = Customers.CustomerID " & _
15                " ORDER BY Customers.CompanyName, Customers.ContactName "
16 
17        cmd.CommandType = CommandType.Text
18        cmd.Connection = conn
19 
20        conn.Open()
21        OrdersReader = cmd.ExecuteReader()
22 
23        gvOrders.DataSource = OrdersReader
24        gvOrders.DataBind()
25 
26        conn.Close()
27    End Sub
28 
29    ' RowDataBound事件處理常式
30    Protected Sub gvOrders_RowDataBound(ByVal sender As Object, _
31                 ByVal e As System.Web.UI.WebControls.GridViewRowEventArgs)
32 
33        Dim OrderRecord As IDataRecord
34        Dim lblOrderDetail As Label
35 
36        '從e(DataReader)取得現在連繫的記鍵
37        '轉換為IDataRecord interface
38        OrderRecord = CType(e.Row.DataItem, IDataRecord)
39 
40        '從GridView物件找到Label控制項
41        'Label控制項在Order Details裡面
42        lblOrderDetail = CType(e.Row.FindControl("lblOrderDetail"), Label)
43 
44        If OrderRecord Is Nothing Or lblOrderDetail Is Nothing Then
45            Return
46        End If
47 
48        Dim cmd As New SqlCommand
49        Dim OrderDetailReader As SqlDataReader
50        cmd.CommandText = _
51                "SELECT Products.ProductName, [Order Details].UnitPrice, " & _
52                " [Order Details].Quantity, [Order Details].Discount " & _
53                " FROM [Order Details], Products " & _
54                " WHERE [Order Details].ProductID = Products.ProductID " & _
55                " AND [Order Details].OrderID = " + _
56                Convert.ToString(OrderRecord("OrderID"))
57        cmd.CommandType = CommandType.Text
58 
59        '在連線字串中必須設定「MultipleActiveResultSets=True」
60        'MARS：Multiple Active Result Sets，多重作用結果集
61        '注意這裡，我們使用的是同一條SqlConnection來連線，也就是MARS作用所在
62        cmd.Connection = conn
63 
64        OrderDetailReader = cmd.ExecuteReader()
65 
66        While OrderDetailReader.Read()
67            lblOrderDetail.Text += OrderDetailReader(0).ToString() + "
68"
69        End While
70 
71    End Sub
72</script>
73 
74<html xmlns="http://www.w3.org/1999/xhtml">
75<head runat="server">
76    <title>MARS範例</title>
77</head>
78<body>
79    <form id="form1" runat="server">
80    <div>
81        <asp:Label ID="lblCounter" runat="server">
82        
view sourceprint?
01　　　　　
02        <asp:GridView ID="gvOrders" runat="server" AutoGenerateColumns="False" OnRowDataBound="gvOrders_RowDataBound" Width="100%">
03            <Columns>
04                <asp:BoundField HeaderText="Company Name" DataField="CompanyName"></asp:BoundField>
05                <asp:BoundField HeaderText="Contact Name" DataField="ContactName"></asp:BoundField>
06                <asp:TemplateField>
07                    <HeaderTemplate>
08                        Order Detail
09                    </HeaderTemplate>
10                    <ItemTemplate>
11                        <asp:Label ID="lblOrderDetail" runat="server">
12                    </ItemTemplate>
13                </asp:TemplateField>
14                <asp:BoundField HeaderText="Order Date" DataField="orderdate" DataFormatString="{0:d}">
15                </asp:BoundField>
16                <asp:BoundField HeaderText="Required Date" DataField="requireddate" DataFormatString="{0:d}">
17                </asp:BoundField>
18                <asp:BoundField HeaderText="Shipped Date" DataField="shippeddate" DataFormatString="{0:d}">
19                </asp:BoundField>
20            </Columns>
21        </asp:GridView>
22         
23 
24         
25 
26    </div>
27    </form>
28</body>
29</html>

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

1 則留言:

  1. 匿名2017年5月5日 下午2:39

    請問一下 MultipleActiveResultSets 和 Connection Pool 是一樣的意思嗎
    有沒有什麼差異
    謝謝

    回覆刪除

感謝您的留言，如果我的文章你喜歡或對你有幫助，按個「讚」或「分享」它，我會很高興的。

訂閱： 張貼留言 (Atom)