notifications.vb

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

Option Explicit On
Option Strict Off
Imports System.Data
Imports System.Data.Sql
Imports System.Data.SqlClient
Imports System.IO
'Following for SmptClient
Imports System.Net
Imports System.Net.Mail

'TODO for enabling DbMail and SmptClient automated reorder messages
'1. Change the RefreshOrders procedure’s strToEmail value to the destination e-mail address.
'2. Replace the SendReordersBySMTP procedure’s strHost, strFromEmail, and strPassword  placeholders with the values required by your e-mail provider. 

Public Class frmNotifications
    Private strProductsFile As String = Application.StartupPath + "\ProductsCurrent.xml"
    Private strSuppliersFile As String = Application.StartupPath + "\SuppliersCurrent.xml"
    Private cnNwind As SqlConnection     'Connection for dependency command
    Private cmdProds As SqlCommand   'Dependency command
    Private strDepMsg As String = Nothing
    Private depProds As SqlDependency 'SqlDependency on the Products table
    Private cmdRequest As SqlCommand     'SqlNotificationRequest command
    Private snrProds As SqlNotificationRequest
    Private strSQL As String = Nothing

    Private Sub Form_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Load the DataGridView
        Try
            With NorthwindDataSet.Products
                If File.Exists(strProductsFile) Then
                    'Load the current copy
                    .ReadXml(strProductsFile)
                Else
                    ProductsTableAdapter.Fill(NorthwindDataSet.Products)
                    'Create the current copy
                    .WriteXml(strProductsFile, XmlWriteMode.DiffGram)
                End If
                'Debugging
                Dim intRows As Integer = .Rows.Count
            End With
            With NorthwindDataSet.Suppliers
                If File.Exists(strSuppliersFile) Then
                    'Load the current copy
                    .ReadXml(strSuppliersFile)
                Else
                    SuppliersTableAdapter.Fill(NorthwindDataSet.Suppliers)
                    'Create the current copy
                    .WriteXml(strSuppliersFile, XmlWriteMode.DiffGram)
                End If
                'Debugging
                Dim intRows As Integer = .Rows.Count
            End With
        Catch exc As Exception
            MsgBox(exc.Message, MsgBoxStyle.Exclamation, "DataSet Fill Operation Failed")
        End Try
        Application.DoEvents()
        'Default query
        strSQL = "SELECT UnitsInStock, UnitsOnOrder, ReorderLevel FROM dbo.Products"
        btnSaveChanges.Enabled = False
        txtHelp.Text = "Default query: " + strSQL
        Try
            'Check for pending SqlNotificationRequest messages
            Dim intPollResult As Integer = PollNotifications(True)
            If intPollResult = 1 Then
                'There's a notification pending
                If Not ListSubscriptions(True) Then
                    'Don't add duplicate notification requests
                    AddSqlNotificationRequest()
                End If
            End If
            If intPollResult = -1 Then
                'Exception, no objects
                btnCreateQueues.Text = "&Add SqlNotification Objects"
            Else
                btnCreateQueues.Text = "&Drop SqlNotification Objects"
            End If
        Catch ex As Exception
            'PollNotification's error handler fires
        End Try
    End Sub

    '***********************************
    'Save DataSet Changes Event Handlers
    '***********************************

    Private Sub ProductsBindingSource_CurrentItemChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ProductsBindingSource.CurrentItemChanged
        If NorthwindDataSet.HasChanges Then
            btnSaveChanges.Enabled = True
        Else
            btnSaveChanges.Enabled = False
        End If
    End Sub

    Private Sub ProductsDataGridView_CurrentCellChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ProductsDataGridView.CurrentCellChanged
        ProductsBindingSource.EndEdit()
        If NorthwindDataSet.HasChanges Then
            btnSaveChanges.Enabled = True
        Else
            btnSaveChanges.Enabled = False
        End If
    End Sub

    Private Sub btnSaveChanges_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSaveChanges.Click
        Try
            Me.Cursor = Cursors.WaitCursor
            With NorthwindDataSet
                If .HasChanges Then
                    If ProductsTableAdapter.Update(NorthwindDataSet) > 0 Then
                        .AcceptChanges()
                        'Update the current file
                        NorthwindDataSet.WriteXml(strProductsFile, XmlWriteMode.DiffGram)
                    Else
                        MsgBox("Error updating Products table.", MsgBoxStyle.Information, "Update Error")
                        .RejectChanges()
                    End If
                    btnSaveChanges.Enabled = False
                End If
            End With
        Catch exc As Exception
            Me.Cursor = Cursors.Default
            MsgBox(exc.Message, MsgBoxStyle.Exclamation, "Base Table Update Operation Failed")
        Finally
            Me.Cursor = Cursors.Default
        End Try
    End Sub

    '*******************************************
    'SqlDependency Query Notification Procedures
    '*******************************************

    Private Sub AddOrRemoveNotification(ByVal blnAdd As Boolean)
        If depProds IsNot Nothing Then
            'Remove previous handler
            RemoveHandler depProds.OnChange, AddressOf SqlDependency_OnChange
        End If
        If cmdProds IsNot Nothing Then
            'Remove previous notification
            cmdProds.Notification = Nothing
        End If
        If blnAdd Then
            'Create the notification
            Try
                If cnNwind Is Nothing Then
                    cnNwind = New SqlConnection(My.Settings.NorthwindConnection)
                End If
                cmdProds = New SqlCommand(strSQL, cnNwind)

                'Add an SqlDependency for the connection
                depProds = New SqlDependency(cmdProds)
                'Uncomment below to test timeout
                'cmdProds.Notification.Timeout = 15
                AddHandler depProds.OnChange, New OnChangeEventHandler(AddressOf SqlDependency_OnChange)

                'Execute the command to establish the notification
                cnNwind.Open()
                Dim sdrDep As SqlDataReader = cmdProds.ExecuteReader
                sdrDep.Close()
                cnNwind.Close()

                'Options was Service
                Dim strService As String = "Options: " + cmdProds.Notification.Options
                Dim strTimeout As String = "Timeout: " + cmdProds.Notification.Timeout.ToString + " secs."
                'UserData was ID; UserData isn't available for query notifications; following throws an exception
                If cmdProds.Notification.IsUserDataAvailable Then
                    Dim strID As String = cmdProds.Notification.UserData
                    'Reformat the XML
                    strID = Replace(strID, "><", ">" + vbCrLf + "<")
                    strDepMsg = strService + vbCrLf + strTimeout + vbCrLf + vbCrLf + strID + vbCrLf + vbCrLf
                Else
                    strDepMsg = strService + vbCrLf + strTimeout + vbCrLf + vbCrLf
                End If
            Catch exc As Exception
                MsgBox(exc.Message, MsgBoxStyle.Exclamation, "Create Dependency Operation Failed")
            End Try
        End If
    End Sub

    Private Sub SqlDependency_OnChange(ByVal sender As Object, ByVal args As SqlNotificationEventArgs)
        'Open a message box with query notification details
        'This event-handler runs on its own thread
        Dim blnDataChanged As Boolean
        Dim strMsg As String = Nothing
        strMsg += "Notification query: " + strSQL + vbCrLf
        strMsg += strDepMsg
        strMsg += "Type: " + args.Type.ToString + vbCrLf
        strMsg += "Source: "
        Select Case args.Source
            Case SqlNotificationSource.Data
                blnDataChanged = True
                strMsg += "Data has changed."
            Case SqlNotificationSource.Database
                strMsg += "Database state has changed."
            Case SqlNotificationSource.Environment
                strMsg += "Incompatible run-time environment."
            Case SqlNotificationSource.Execution
                strMsg += "Runtime error occurred."
            Case SqlNotificationSource.Object
                strMsg += "Products table was modified."
            Case SqlNotificationSource.Statement
                strMsg += "Invalid notification query."
            Case SqlNotificationSource.System
                strMsg += "System error."
            Case SqlNotificationSource.Timeout
                strMsg += "Subscription timeout expired."
            Case Else
                strMsg += "Unknown source."
        End Select
        strMsg += vbCrLf + "Info: "
        Select Case args.Info
            Case SqlNotificationInfo.Alter
                strMsg += "Products table was altered."
            Case SqlNotificationInfo.Delete
                strMsg += "Row(s) were deleted."
            Case SqlNotificationInfo.Drop
                strMsg += "Products table was dropped."
            Case SqlNotificationInfo.Error
                strMsg += "Internal server error."
            Case SqlNotificationInfo.Insert
                strMsg += "Row(s) were inserted."
            Case SqlNotificationInfo.Invalid
                strMsg += "Invalid notification query."
            Case SqlNotificationInfo.Isolation
                strMsg += "Invalid transaction isolation mode."
            Case SqlNotificationInfo.Options
                strMsg += "Inappropriate SET options."
            Case SqlNotificationInfo.Query
                strMsg += "Invalid notification SELECT statement."
            Case SqlNotificationInfo.Restart
                strMsg += "Server has been restarted."
            Case SqlNotificationInfo.Truncate
                strMsg += "Products table was truncated."
            Case SqlNotificationInfo.Update
                strMsg += "Row(s) were updated."
            Case Else
                strMsg += "Unknown problem."