notifications.vb

来自「wrox出版社的另一套经典的VB2005数据库编程学习书籍,收集了书中源码,郑重」· VB 代码 · 共 939 行 · 第 1/4 页

        End Select
        strMsg += vbCrLf + "Connection State: " + cnNwind.State.ToString
        If blnDataChanged Then
            strMsg += vbCrLf + vbCrLf + "Click Refresh Data to update the Products lookup table."
        End If
        MsgBox(strMsg, MsgBoxStyle.Information, "Query Notification Received")
        If blnDataChanged Then
            'Recreate the notification
            AddOrRemoveNotification(True)
        End If
    End Sub

    '***********************************************************
    'Enable and Select SqlDependency Notification Event Handlers
    '***********************************************************

    Private Sub chkEnableNotifications_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles chkEnableNotifications.CheckedChanged
        'Enable or disable notifications
        If chkEnableNotifications.Checked Then
            btnSubscriptions.Enabled = True
            gbQuery.Enabled = True
            AddOrRemoveNotification(True)
        Else
            gbQuery.Enabled = False
            btnSubscriptions.Enabled = False
            AddOrRemoveNotification(False)
        End If
    End Sub

    Private Sub optAllProds_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles optAllProds.CheckedChanged
        'Default query
        If chkEnableNotifications.Checked And optAllProds.Checked Then
            strSQL = "SELECT UnitsInStock, UnitsOnOrder, ReorderLevel FROM dbo.Products"
            txtHelp.Text = "All products query: " + strSQL + vbCrLf
            AddOrRemoveNotification(True)
        End If
    End Sub

    Private Sub optReorder_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles optReorder.CheckedChanged
        'Detects reorder required or completed
        If chkEnableNotifications.Checked And optReorder.Checked Then
            strSQL = "SELECT UnitsInStock, UnitsOnOrder, ReorderLevel FROM dbo.Products "
            strSQL += "WHERE Discontinued = 0 AND UnitsInStock + UnitsOnOrder - ReorderLevel <= 0"
            txtHelp.Text = "Reorder query: " + strSQL + vbCrLf
            AddOrRemoveNotification(True)
        End If
    End Sub

    Private Sub optInvalid_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles optInvalid.CheckedChanged
        If chkEnableNotifications.Checked And optInvalid.Checked Then
            strSQL = "SELECT TOP 10 UnitsInStock, UnitsOnOrder, ReorderLevel FROM Products"
            txtHelp.Text = "Invalid test query: " + strSQL + vbCrLf
            AddOrRemoveNotification(True)
        End If
    End Sub

    '*******************************************
    'Current subscriptions iteration message box
    '*******************************************

    Private Sub btnSubscriptions_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubscriptions.Click
        ListSubscriptions(False)
    End Sub

    Private Function ListSubscriptions(ByVal blnTestSubs As Boolean) As Boolean
        'Returns True if blnTestSubs is True and an SqlNotificationRequest exists 
        If cnNwind Is Nothing Then
            cnNwind = New SqlConnection(My.Settings.NorthwindConnection)
        End If
        Dim cmdSubs As New SqlCommand("SELECT * FROM sys.dm_qn_subscriptions", cnNwind)
        Try
            cnNwind.Open()
            Dim rdrSubs As SqlDataReader = cmdSubs.ExecuteReader
            Dim intCtr As Integer
            Dim strMsg As String = Nothing
            Dim alSubs As New ArrayList
            With rdrSubs
                While .Read
                    If blnTestSubs Then
                        If .GetInt32(5) = Int32.MaxValue Then
                            btnCreateQueues.Text = "&Drop SqlNotification Objects"
                            Return True
                        End If
                    End If
                    If intCtr < 4 Then
                        strMsg += "Notification Type: "
                        If .GetInt32(5) = Int32.MaxValue Then
                            strMsg += "SqlNotificationRequest" + vbCrLf
                        Else
                            strMsg += "SqlDependency" + vbCrLf
                        End If
                        strMsg += "Subscription ID: " + .GetValue(0).ToString + vbCrLf
                        strMsg += "Database ID: " + .GetValue(1).ToString + vbCrLf
                        strMsg += "Object ID: " + .GetValue(2).ToString + vbCrLf
                        strMsg += "User ID: " + .GetValue(3).ToString + vbCrLf
                        strMsg += "Created: " + .GetValue(4).ToString + vbCrLf
                        strMsg += "Timeout, seconds: " + .GetValue(5).ToString + vbCrLf
                        strMsg += "Status ID: " + .GetValue(6).ToString + vbCrLf + vbCrLf
                    End If
                    alSubs.Add(.GetValue(0))
                    intCtr += 1
                End While
                If blnTestSubs Then
                    'There are no subscriptions
                    btnCreateQueues.Text = "&Add SqlNotification Objects"
                    Return False
                End If
            End With
            If intCtr > 4 Then
                strMsg += (intCtr - 4).ToString + " of " + intCtr.ToString + " subscriptions aren't listed." + vbCrLf + vbCrLf
            End If
            strMsg += "Do you want to delete the subscription(s)? (Requires sysadmin role.)"
            rdrSubs.Close()
            If intCtr > 0 Then
                If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "Query Notifications: Current Subscriptions") = MsgBoxResult.Yes Then
                    Dim intSub As Integer
                    Dim intDelSubs As Integer
                    'Alternatively, set CommandText to KILL QUERY NOTIFICATION SUBSCRIPTION ALL"
                    For intSub = 0 To intCtr - 1
                        Try
                            cmdSubs.CommandText = "KILL QUERY NOTIFICATION SUBSCRIPTION " + alSubs(intSub).ToString
                            intDelSubs += cmdSubs.ExecuteNonQuery()
                        Catch exc As Exception
                            MsgBox(exc.Message, MsgBoxStyle.Exclamation, "Can't Delete Current Subscription")
                        End Try
                    Next
                    If intSub = intCtr Then
                        'All subscriptions removed
                        btnSubscriptions.Enabled = False
                        chkEnableNotifications.Checked = False
                    End If
                End If
            Else
                strMsg = "There are no current query notification subscriptions."
                MsgBox(strMsg, MsgBoxStyle.Information, "Query Notifications: Current Subscriptions")
            End If
            cnNwind.Close()
        Catch exc As Exception
            MsgBox(exc.Message, MsgBoxStyle.Exclamation, "Can't Connect to Northwind Database")
        Finally
            cnNwind.Close()
            cmdSubs.Dispose()
        End Try
    End Function

    '*********************************
    'SqlNotificationRequest Procedures
    '*********************************

    Private Sub btnCreateQueues_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreateQueues.Click
        CreateQueue(False)
    End Sub

    Private Function CreateQueue(ByVal blnSilent As Boolean) As Boolean
        'Create a QUEUE
        Dim strQueueSQL As String = "CREATE QUEUE ProductsQnQueue; "
        'Create a SERVICE with a PostQueryNotification MESSAGE TYPE
        strQueueSQL += "CREATE SERVICE ProductsQnService ON QUEUE ProductsQnQueue" + _
         "([http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]); "
        'Add an optional ROUTE to the local SQL Server instance
        strQueueSQL += "CREATE ROUTE ProductsQnRoute WITH SERVICE_NAME = 'ProductsQnService', ADDRESS = 'LOCAL'; "
        If cnNwind Is Nothing Then
            cnNwind = New SqlConnection(My.Settings.NorthwindConnection)
        End If
        Dim cmdQueues As New SqlCommand(strQueueSQL, cnNwind)
        Dim strMsg As String
        Try
            cnNwind.Open()
            cmdQueues.ExecuteNonQuery()
            cnNwind.Close()
            'Add an SqlNotificationRequest
            AddSqlNotificationRequest()
            btnSubscriptions.Enabled = True
            strMsg = "Created ProductsQnQueue, ProductsQnService, ProductsQnRoute, " + _
             "and SqlNotificationRequest for All Products Updates query." + vbCrLf + vbCrLf
            strMsg += "Click QN Subscriptions to verify added subscription."
            btnCreateQueues.Text = "&Drop SqlNotification Objects"
            If blnSilent Then
                Return True
            Else
                MsgBox(strMsg, MsgBoxStyle.Information, "Created Database Objects and SqlNotificationRequest")
            End If
        Catch exc As Exception
            cnNwind.Close()
            If blnSilent Then
                Return False
            End If
            If exc.Message.Contains("There is already an object") Then
                strMsg = exc.Message + vbCrLf + vbCrLf + "Do you want to drop the queue, service, route, and all subscriptions?"
                If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "Products Queue and Service Exist") = MsgBoxResult.Yes Then
                    Try
                        If cnNwind.State = ConnectionState.Closed Then
                            cnNwind.Open()
                        End If
                        Dim strDropSQL As String = Nothing
                        strDropSQL += "DROP SERVICE ProductsQnService; "
                        strDropSQL += "DROP ROUTE ProductsQnRoute; "
                        strDropSQL += "DROP QUEUE dbo.ProductsQnQueue; "
                        strDropSQL += "KILL QUERY NOTIFICATION SUBSCRIPTION ALL; "
                        cmdQueues.CommandText = strDropSQL
                        cmdQueues.ExecuteNonQuery()
                        cnNwind.Close()
                        btnCreateQueues.Text = "&Add SqlNotification Objects"
                    Catch ex As Exception
                        MsgBox(ex.Message, MsgBoxStyle.Exclamation, "Error Dropping Queue and Service")
                    Finally
                        cnNwind.Close()
                    End Try
                ElseIf Not ListSubscriptions(True) Then
                    'There are no subscriptions
                    strMsg = "Do you want to add a SqlNotificationRequest subscription?"
                    If MsgBox(strMsg, MsgBoxStyle.Question Or MsgBoxStyle.YesNo, "The ProductsQnQueue Has No Subscriptions") = MsgBoxResult.Yes Then
                        AddSqlNotificationRequest()
                        btnSubscriptions.Enabled = True
                    End If
                End If
            Else
                MsgBox(exc.Message, MsgBoxStyle.Exclamation, "Error Adding Products Queue")
            End If
        Finally
            cmdQueues.Dispose()
            cnNwind.Close()
        End Try
    End Function

    Private Sub AddSqlNotificationRequest()
        If cnNwind Is Nothing Then
            cnNwind = New SqlConnection(My.Settings.NorthwindConnection)
        End If
        Dim strRequestSQL As String = "SELECT UnitsInStock, UnitsOnOrder, " + _
         "ReorderLevel FROM dbo.Products"
        cmdRequest = New SqlCommand(strRequestSQL, cnNwind)
        'Create an SqlNotification request with the maximum timeout (Int32.MaxValue)
        'Change Int32.MaxValue to 15 to test Timeout
        snrProds = New SqlNotificationRequest(Guid.NewGuid().ToString, _