MARS能在同一條資料連線(SqlConnection)開啟一個以上的結果集，並讓你同時存取這些結果集。
讓我們回憶一下，我們撰寫資料庫的流程，
- 連線字串(SqlConnection)
- SQL定義(SqlCommand)
- 取回資料(DataReader, DataSet)
- 處理(...DataBind...)
- 關閉連線(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的效果，讓我們的程式更有彈性，更靈活。
請問一下 MultipleActiveResultSets 和 Connection Pool 是一樣的意思嗎
回覆刪除有沒有什麼差異
謝謝