async.vb

wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重推荐,电子书,电子书下载

Imports System.Data
Imports System.Data.SqlClient
Imports System.Threading

Public Class frmAsync
    'If you have multiple SQL Server instances, specify their connection strings
    Friend Shared strCusts As String = "Data Source=localhost;" + _
     "Initial Catalog=Northwind;Integrated Security=SSPI;Async=True"
    Friend Shared strOrders As String = "Data Source=OAKLEAF-W2K3;" + _
     "Initial Catalog=NorthwindCS;Integrated Security=SSPI;Async=True"
    Friend Shared strDetails As String = "Data Source=OAKLEAF-MS2K3;" + _
     "Initial Catalog=NorthwindCS;Integrated Security=SSPI;Async=True"
    Friend Shared blnMultiServer As Boolean

    Friend Shared astrItems(2) As String
    Friend Shared astrListItems(6) As String
    Friend Shared intLoops As Integer
    Friend Shared timHiRes As New clsTimer
    Friend Shared objData As Object
    Friend Shared cnnCusts As SqlConnection
    Friend Shared cnnOrders As SqlConnection
    Friend Shared cnnDetails As SqlConnection
    Friend Shared blnIsCustsDone As Boolean
    Friend Shared blnIsOrdersDone As Boolean
    Friend Shared blnIsDetailsDone As Boolean

    Private Sub btnExecAsync_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecAsync.Click
        'Execute commands asynchronously
        Try
            blnIsCustsDone = False
            blnIsOrdersDone = False
            blnIsDetailsDone = False
            lblTitle.Text = "Asynchronous Command Operations"
            Me.Cursor = Cursors.WaitCursor
            btnExecAsync.Enabled = False
            Application.DoEvents()
            cnnCusts = New SqlConnection(strCusts)
            Dim cmdCusts As SqlCommand = cnnCusts.CreateCommand
            With cmdCusts
                .CommandType = CommandType.Text
                .CommandTimeout = 10
                .CommandText = "SELECT * FROM Customers"
            End With

            If blnMultiServer Then
                cnnOrders = New SqlConnection(strOrders)
            Else
                cnnOrders = New SqlConnection(strCusts)
            End If
            Dim cmdOrders As SqlCommand = cnnOrders.CreateCommand
            With cmdOrders
                .CommandType = CommandType.Text
                .CommandTimeout = 10
                .CommandText = "SELECT * FROM Orders"
            End With

            If blnMultiServer Then
                cnnDetails = New SqlConnection(strDetails)
            Else
                cnnDetails = New SqlConnection(strCusts)
            End If
            Dim cmdDetails As SqlCommand = cnnDetails.CreateCommand
            With cmdDetails
                .CommandType = CommandType.Text
                .CommandTimeout = 10
                .CommandText = "SELECT * FROM [Order Details]"
            End With

            lstOps.Items.Clear()
            'Start the timers
            timHiRes.Start()
            timWait.Interval = 10
            timWait.Start()

            'Open the Customers connection
            cnnCusts.Open()
            Dim intThread As Integer = Thread.CurrentThread.ManagedThreadId
            Dim blnIsPool As Boolean = Thread.CurrentThread.IsThreadPoolThread
            lstOps.Items.Add(Format(timHiRes.ElapsedTime, "0.000") + " - 1 Opened Customers connection (" + intThread.ToString + ")")

            Dim asrCustomersReader As IAsyncResult
            Dim objCmdCustomers As Object = CType(cmdCusts, Object)
            asrCustomersReader = cmdCusts.BeginExecuteReader(New AsyncCallback(AddressOf CustomersHandler), objCmdCustomers, CommandBehavior.CloseConnection)
            lstOps.Items.Add(Format(timHiRes.ElapsedTime, "0.000") + " - 2 BeginExecuteReader: Customers")

            'Open the Orders connection
            cnnOrders.Open()
            lstOps.Items.Add(Format(timHiRes.ElapsedTime, "0.000") + " - 3 Opened Orders connection")

            Dim asrOrdersReader As IAsyncResult
            Dim objCmdOrders As Object = CType(cmdOrders, Object)
            asrOrdersReader = cmdOrders.BeginExecuteReader(New AsyncCallback(AddressOf OrdersHandler), objCmdOrders, CommandBehavior.CloseConnection)

            lstOps.Items.Add(Format(timHiRes.ElapsedTime, "0.000") + " - 4 BeginExecuteReader: Orders")
            'Open the Details connection
            cnnDetails.Open()
            lstOps.Items.Add(Format(timHiRes.ElapsedTime, "0.000") + " - 5 Opened Details connection")

            Dim asrDetailsReader As IAsyncResult
            Dim objCmdDetails As Object = CType(cmdDetails, Object)
            asrDetailsReader = cmdDetails.BeginExecuteReader(New AsyncCallback(AddressOf DetailsHandler), objCmdDetails, CommandBehavior.CloseConnection)
            lstOps.Items.Add(Format(timHiRes.ElapsedTime, "0.000") + " - 6 BeginExecuteReader: Order Details")
        Catch excAsync As Exception
            Me.Cursor = Cursors.Default
            MsgBox(excAsync.Message + excAsync.StackTrace, , "Async Operation Exception")
        Finally
            Me.Cursor = Cursors.Default
            btnExecAsync.Enabled = True
        End Try
    End Sub

    '******************************************
    'IAsyncResult handlers for EndExecuteReader
    '******************************************

    Private Sub CustomersHandler(ByVal iarResult As IAsyncResult)
        Try
            Dim sdrData As SqlDataReader = CType(iarResult.AsyncState, SqlCommand).EndExecuteReader(iarResult)
            With sdrData
                Dim intCtr As Integer
                While .Read
                    For intCtr = 0 To .FieldCount - 1
                        objData = .GetValue(intCtr)
                    Next intCtr
                End While
                .Close()
                .Dispose()
            End With
            Dim blnIsPool As Boolean = Thread.CurrentThread.IsThreadPoolThread
            CustomersDone(Thread.CurrentThread.ManagedThreadId, blnIsPool)
        Catch excHandler As Exception
            MsgBox(excHandler.Message + excHandler.StackTrace, , "Customers Handler Exception")
        End Try
    End Sub

    Friend Shared Sub CustomersDone(ByVal intThread As Integer, ByVal blnIsPool As Boolean)
        Dim strPool As String = ""
        If blnIsPool Then
            strPool = "P"
        End If
        astrItems(0) = Format(timHiRes.ElapsedTime, "0.000") + " - EndExecuteReader: Customers (" + intThread.ToString + strPool + ")"
        blnIsCustsDone = True
    End Sub

    Private Sub OrdersHandler(ByVal iarResult As IAsyncResult)
        Try
            Dim sdrData As SqlDataReader = CType(iarResult.AsyncState, SqlCommand).EndExecuteReader(iarResult)
            With sdrData
                Dim intCtr As Integer
                Dim intLoop As Integer
                While .Read
                    For intLoop = 1 To intLoops
                        'Orders reader delay loop
                        For intCtr = 0 To .FieldCount - 1
                            objData = .GetValue(intCtr)
                        Next intCtr
                    Next intLoop
                End While
                .Close()
                .Dispose()
            End With
            Dim blnIsPool As Boolean = Thread.CurrentThread.IsThreadPoolThread
            OrdersDone(Thread.CurrentThread.ManagedThreadId, blnIsPool)
        Catch excHandler As Exception
            MsgBox(excHandler.Message + excHandler.StackTrace, , "Details Handler Exception")
        End Try
    End Sub

    Friend Shared Sub OrdersDone(ByVal intThread As Integer, ByVal blnIsPool As Boolean)
        Dim strPool As String = ""
        If blnIsPool Then
            strPool = "P"
        End If
        astrItems(1) = Format(timHiRes.ElapsedTime, "0.000") + " - EndExecuteReader: Orders (" + intThread.ToString + strPool + ")"
        blnIsOrdersDone = True
    End Sub

    Private Sub DetailsHandler(ByVal iarResult As IAsyncResult)
        Try
            Dim sdrData As SqlDataReader = CType(iarResult.AsyncState, SqlCommand).EndExecuteReader(iarResult)
            With sdrData
                Dim intCtr As Integer
                While .Read
                    For intCtr = 0 To .FieldCount - 1
                        objData = .GetValue(intCtr)
                    Next intCtr
                End While
                .Close()
                .Dispose()
            End With
            Dim blnIsPool As Boolean = Thread.CurrentThread.IsThreadPoolThread
            DetailsDone(Thread.CurrentThread.ManagedThreadId, blnIsPool)
        Catch excHandler As Exception
            MsgBox(excHandler.Message + excHandler.StackTrace, , "Details Handler Exception")
        End Try
    End Sub

    Friend Shared Sub DetailsDone(ByVal intThread As Integer, ByVal blnIsPool As Boolean)
        Dim strPool As String = ""
        If blnIsPool Then
            strPool = "P"
        End If
        astrItems(2) = Format(timHiRes.ElapsedTime, "0.000") + " - EndExecuteReader: Order Details (" + intThread.ToString + strPool + ")"
        blnIsDetailsDone = True
    End Sub

    '***************************
    'Timer for IAsyncResult data
    '***************************

    Private Sub timWait_Tick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles timWait.Tick
        'Delay for worst-case completion of async commands
        If blnIsCustsDone And blnIsOrdersDone And blnIsDetailsDone Then
            Dim intCtr As Integer
            For intCtr = 0 To 2
                lstOps.Items.Add(astrItems(intCtr))
            Next intCtr
            timWait.Stop()
            Me.Cursor = Cursors.Default
        End If
    End Sub

    '*********************************************************
    'Set Orders SqlDataReader delay and multi-server operation
    '*********************************************************

    Private Sub chkSlowOrders_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkSlowOrders.CheckedChanged
        If chkSlowOrders.Checked Then
            'Add a delay to processing Orders records
            intLoops = 500
        Else
            intLoops = 0
        End If
    End Sub

    Private Sub chkMultiServer_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkMultiServer.CheckedChanged
        If chkMultiServer.Checked Then
            blnMultiServer = True
        Else
            blnMultiServer = False
        End If
    End Sub

    '********************************************
    'Synchronous command execution for comparison
    '********************************************

    Private Sub btnExecSync_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnExecSync.Click
        'Run commands synchronously
        Try
            Me.Cursor = Cursors.WaitCursor
            btnExecSync.Enabled = False
            Application.DoEvents()
            lblTitle.Text = "Synchronous Command Operations"
            cnnCusts = New SqlConnection(Replace(strCusts, ";Async=True", ""))
            Dim cmdCusts As SqlCommand = cnnCusts.CreateCommand
            With cmdCusts
                .CommandType = CommandType.Text
                .CommandTimeout = 10
                .CommandText = "SELECT * FROM Customers"