[ASP.NET] 好用的非同步資料庫執行

2010-07-14

5480
0
ASP.NET
2010-07-19

摘要:[ASP.NET] 好用的非同步資料庫執行

有沒有辦法在一次的Connection Open就可以同時執行多個Command，

正常情況下ADO.NET去執行SQL的Command時都是循序的處理，

一個Connection在同一時間只可執行一個 Command，

使用非同步的資料庫執行就可以在同一間內執行多個不同的Command，

所以寫了一個簡單範例測試，

主要就是登入時會去判斷這個登入的帳號密碼是否正確，

並且同時寫入是誰登入以及他的IP，

我在資料庫中建立一個簡單的帳號Table如下：

還有一個簡易的登入Log，如下：

在頁面方面我是用ASP.NET 2.0以上才內建的Login控制項，畫面和HTML如下

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
    <title>非同步執行DB</title>
</head>
<body>
    <form id="form1" runat="server">
    <div align="center">
    <asp:Login ID="Login1" runat="server" BackColor="#FFFBD6" BorderColor="#FFDFAD"
                BorderPadding="4" BorderStyle="Solid" BorderWidth="1px" Font-Names="Verdana"
                Font-Size="0.8em" ForeColor="#333333" Width="200px"
            TextLayout="TextOnTop">
                <TextBoxStyle Font-Size="0.8em" />
                <LoginButtonStyle BackColor="White" BorderColor="#CC9966" BorderStyle="Solid"
                    BorderWidth="1px" Font-Names="Verdana" Font-Size="0.8em"
                    ForeColor="#990000" />
                <LayoutTemplate>
                    <table border="0" cellpadding="4" cellspacing="0"
                        style="border-collapse:collapse;">
                        <tr>
                            <td>
                                <table border="0" cellpadding="0" style="width:200px;">
                                    <tr>
                                        <td align="center"
                                            style="color:White;background-color:#990000;font-size:0.9em;font-weight:bold;">
                                            登入</td>
                                    </tr>
                                    <tr>
                                        <td>
                                             </td>
                                    </tr>
                                    <tr>
                                        <td align="left">
                                            <div style="float:left; width:80px; display:block;">
                                                <asp:Label ID="UserNameLabel" runat="server" AssociatedControlID="UserName">使用者名稱:</asp:Label>
                                            </div>
                                            <div style="float:left;">
                                            <asp:TextBox ID="UserName" runat="server" Font-Size="0.8em"></asp:TextBox>
                                            <asp:RequiredFieldValidator ID="UserNameRequired" runat="server"
                                                ControlToValidate="UserName" ErrorMessage="必須提供使用者名稱。" ToolTip="必須提供使用者名稱。"
                                                ValidationGroup="Login1">*</asp:RequiredFieldValidator>
                                            </div>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td align="left">
                                            <div style="float:left; width:80px; display:block;">
                                                <asp:Label ID="PasswordLabel" runat="server" AssociatedControlID="Password">密碼:</asp:Label>
                                            </div>
                                            <div style="float:left;">
                                                <asp:TextBox ID="Password" runat="server" Font-Size="0.8em" TextMode="Password"></asp:TextBox>
                                                <asp:RequiredFieldValidator ID="PasswordRequired" runat="server"
                                                ControlToValidate="Password" ErrorMessage="必須提供密碼。" ToolTip="必須提供密碼。"
                                                ValidationGroup="Login1">*</asp:RequiredFieldValidator>
                                            </div>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td>
                                            <asp:CheckBox ID="RememberMe" runat="server" Text="記憶密碼供下次使用。" />
                                        </td>
                                    </tr>
                                    <tr>
                                        <td align="center" style="color:Red;">
                                            <asp:Literal ID="FailureText" runat="server" EnableViewState="False"></asp:Literal>
                                        </td>
                                    </tr>
                                    <tr>
                                        <td align="right">
                                            <asp:Button ID="LoginButton" runat="server" BackColor="White"
                                                BorderColor="#CC9966" BorderStyle="Solid" BorderWidth="1px" CommandName="Login"
                                                Font-Names="Verdana" Font-Size="0.8em" ForeColor="#990000" Text="登入"
                                                ValidationGroup="Login1" />
                                        </td>
                                    </tr>
                                </table>
                            </td>
                        </tr>
                    </table>
                </LayoutTemplate>
                <InstructionTextStyle Font-Italic="True" ForeColor="Black" />
                <TitleTextStyle BackColor="#990000" Font-Bold="True" Font-Size="0.9em"
                    ForeColor="White" />
            </asp:Login>
    </div>
    </form>
</body>
</html>

首先我們要在Web.config建立一個非同步的ConnectionString，

其實很簡單只是在我們平常的ConnectionString加上Asynchronous Processing=true，

因為會用到MARS(Multiple Active Result Sets)，

所以需要在Web.config中的ConnectionString加上MultipleActiveResultSets=true

接著使用IAsyncResult 介面(詳細)來處理，

Partial Class test_Login

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

Me.Login1.ToolTip = "請輸入您的帳號密碼"

End If

End Sub

Protected Sub Login1_Authenticate(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.AuthenticateEventArgs) Handles Login1.Authenticate

Dim cn As New SqlConnection '設定Connect物件

cn.ConnectionString = ConfigurationManager.ConnectionStrings("testAsy_conndb").ConnectionString

Dim cmd1 As New SqlCommand '設定Command物件

Dim cmd2 As New SqlCommand '設定Command物件

Dim dr1 As SqlDataReader '設定DataReader物件

Dim AsyncRt1, AsyncRt2 As IAsyncResult

Dim WtHandle1, WtHandle2 As System.Threading.WaitHandle

Dim WtHandle(1) As System.Threading.WaitHandle '包含WtHandle(0),WtHandle(1)

cmd1.CommandText = "Select * From [Account] Where id=@id and pwd=@pwd"

cmd1.CommandType = CommandType.Text

cmd1.Connection = cn

cmd1.Parameters.Add("@id", SqlDbType.NVarChar).Value = Me.Login1.UserName.ToString

cmd1.Parameters.Add("@pwd", SqlDbType.NVarChar).Value = Me.Login1.Password.ToString

cmd2.CommandText = "INSERT INTO [LoginLog] ([id],[pwd],[login_date],[ip])"

cmd2.CommandText += " VALUES (@id ,@pwd ,getdate() ,@ip)"

cmd2.CommandType = CommandType.Text

cmd2.Connection = cn

cmd2.Parameters.Add("@id", SqlDbType.NVarChar).Value = Me.Login1.UserName.ToString

cmd2.Parameters.Add("@pwd", SqlDbType.NVarChar).Value = Me.Login1.Password.ToString

cmd2.Parameters.Add("@ip", SqlDbType.NVarChar).Value = getIP()

cn.Open()

AsyncRt1 = cmd1.BeginExecuteReader

AsyncRt2 = cmd2.BeginExecuteReader

WtHandle1 = AsyncRt1.AsyncWaitHandle

WtHandle2 = AsyncRt2.AsyncWaitHandle

'接下來這邊就可以指定那個指令的先後順序

WtHandle(0) = WtHandle1

WtHandle(1) = WtHandle2

System.Threading.WaitHandle.WaitAll(WtHandle)

dr1 = cmd1.EndExecuteReader(AsyncRt1)

cmd2.EndExecuteReader(AsyncRt2)

If dr1.HasRows Then

FormsAuthentication.RedirectFromLoginPage(Login1.UserName, Login1.RememberMeSet)

End If

dr1.Close()

cmd1.Dispose()

cmd2.Dispose()

cn.Close()

cn.Dispose()

End Sub

Public Function getIP() As String

Dim ip As String

ip = Request.ServerVariables("HTTP_X_FORWARDED_FOR")

If ip = String.Empty Then

ip = Request.ServerVariables("REMOTE_ADDR")

End If

Return ip

End Function

End Class

當你輸入帳號密碼後，便會同時驗證以及寫入資料

以下是寫入的資料和登入成功的畫面

回首頁