SqlCommand的非同步行程

我們在前一篇提到MARS的功能，讓我們在進一步來談談ASP.NET裡SqlCommand類別的非同步功能。在MARS我們說明在一個資料連線裡同時存取多個結果集。而非同步功能能讓使用者同時對資料庫送出數個各不相關的命令。如果兩者結合，那更是強大。

首先使用MARS我們必須先在連線字串裡啟用「MultipleActiveResultSets=True」，再來，我們要啟用ADO.NET的非同步功能，我們也必須在連線字串裡加入「Asynchronous Processing=true」。

ADO.NET-Asyn.aspx

以下我們將介紹三種非同步的方式。

<form id="form1" runat="server">
    <div>
        <asp:Button ID="Poll" runat="server" Text="輪詢(Poll)" /> 
        <asp:Button ID="bntWait" runat="server" Text="等待-單一表(WaitHandle)" />
        <br />
        <asp:Button ID="bntHandles" runat="server" Text="等待-兩資料表(WaitHandle) + MARS" />
        <br />
        <br />
        <asp:GridView ID="gvOrders" runat="server" Caption="Orders">
        </asp:GridView>
        

        <asp:GridView ID="gvOrderDetails" runat="server" Caption="Order Details">
        </asp:GridView>
    </div>
    </form>

ADO.NET-Asyn.aspx.vb

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

Partial Class ADONET_Asyn
    Inherits System.Web.UI.Page

    '使用MARS
    Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("NorthwindConnectionString").ConnectionString)
    Dim strSQL As String = "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"

    Protected Sub Poll_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Poll.Click
        Dim cmd As New SqlCommand
        cmd.CommandText = strSQL
        cmd.CommandType = CommandType.Text
        cmd.Connection = conn

        conn.Open()

        '非同步結果
        Dim asyncResult As IAsyncResult
        '執行非同步
        asyncResult = cmd.BeginExecuteReader

        '非同步是否完成，不然停止1秒
        While Not asyncResult.IsCompleted
            System.Threading.Thread.Sleep(1000)
        End While

        Dim dr As SqlDataReader
        '取回非同步資料
        dr = cmd.EndExecuteReader(asyncResult)

        gvOrders.DataSource = dr
        gvOrders.DataBind()

        conn.Close()
    End Sub

    Protected Sub bntWait_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bntWait.Click
        Dim cmd As New SqlCommand
        cmd.CommandText = strSQL
        cmd.CommandType = CommandType.Text
        cmd.Connection = conn 
        conn.Open()

        Dim asyncResult As IAsyncResult
        '等待碼
        Dim WHandle As System.Threading.WaitHandle
        Dim dr As SqlDataReader

        asyncResult = cmd.BeginExecuteReader
        '取得非同步等待碼
        WHandle = asyncResult.AsyncWaitHandle

        'WaitOne，等待完成
        If WHandle.WaitOne = True Then
            dr = cmd.EndExecuteReader(asyncResult)
            gvOrders.DataSource = dr
            gvOrders.DataBind()
            conn.Close()
        End If
    End Sub

    Protected Sub bntHandles_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles bntHandles.Click
        Dim cmd As New SqlCommand
        cmd.CommandText = strSQL
        cmd.CommandType = CommandType.Text
        cmd.Connection = conn 'MARS

        Dim odcmd As New SqlCommand
        odcmd.CommandText = "select * from customers where companyName = 'Alfreds Futterkiste'"
        odcmd.CommandType = CommandType.Text
        odcmd.Connection = conn 'MARS

        conn.Open() 'MARS: cmd, odcmd

        '非同步處理
        Dim orAsyncResult As IAsyncResult
        Dim odAsyncResult As IAsyncResult
        '等待碼陣列
        Dim WHandles(1) As System.Threading.WaitHandle
        Dim orDr As SqlDataReader
        Dim odDr As SqlDataReader

        '取得非同步結果
        orAsyncResult = cmd.BeginExecuteReader
        odAsyncResult = odcmd.BeginExecuteReader

        '取得等待碼(WaitHandle)
        WHandles(0) = orAsyncResult.AsyncWaitHandle
        WHandles(1) = odAsyncResult.AsyncWaitHandle

        '第一種：使用WaitAll
        ''等待全部「等待碼」完成
        'System.Threading.WaitHandle.WaitAll(WHandles)

        'orDr = cmd.EndExecuteReader(orAsyncResult)
        'odDr = odcmd.EndExecuteReader(odAsyncResult)

        'gvOrders.DataSource = orDr
        'gvOrders.DataBind()

        'gvOrderDetails.DataSource = odDr
        'gvOrderDetails.DataBind()

        '第二種：使用WaitAny

        For i As Integer = 0 To 1
            Dim WHIndex As Integer
            WHIndex = System.Threading.WaitHandle.WaitAny(WHandles)

            Select Case WHIndex
                Case 0
                    orDr = cmd.EndExecuteReader(orAsyncResult)
                    gvOrders.DataSource = orDr
                    gvOrders.DataBind()
                Case 1
                    odDr = odcmd.EndExecuteReader(odAsyncResult)
                    gvOrderDetails.DataSource = odDr
                    gvOrderDetails.DataBind()
            End Select
        Next

        conn.Close()

    End Sub
End Class

其實用心看，程式不難，而且與我們之前寫的ADO.NET程式還有點像。

輪詢(Poll)

我們先由輪詢(Poll)來看，由BeginExecuteReader來啟動非同步的動作，BeginExecuteReader會傳回一個IAsyncResult，然後判斷非同步是否完成(asyncResult.IsCompleted)，如已經完成，使用EndExecuteReader(asyncResult)取回DataReader資料。

等待(WaitHandle)

在等待方法裡，過程差不多，只是多了一個「等待碼(asyncResult.AsyncWaitHandle)，而且改由這個等待碼來判斷非同步是否完成，再接下去完成後面的動作。而WaitHandle.WaitOne()會等待單一非同步行程完成或逾時，WaitAll()會等待所有非同步過程結束，WaitAny()需要以陣列管理一個以上的等待碼，而且你必須為每個想要處理的等待碼個別叫用。

在第三個按鈕裡我們看出MARS加上非同步的實用性，你可以同時發出「多個」Query而不用擔心。