SQL Server SQLDependency在1个通知后停止接收通知

5f0d552i  于 2023-01-29  发布在  其他
关注(0)|答案(1)|浏览(192)

各位下午好。
我正在处理一个使用WinForms VB.NET和MSSQL的项目,我需要使用SQLDependency来监视表中的新插入。在测试时我遇到了一个问题。启动应用程序后插入一次,我确实收到了插入了一些内容的通知,但在此之后,OnChange事件就不再被调用了。

Private Sub LoadOrder()
        Dim objError As New AppFramework.Logging.EventLog
        Dim objQuoOrders As New QuotationOrders.QuotationOrder
        Try
            objQuoOrders.Companyid = SystemCompanyId
            objQuoOrders.Picker = GsUserName
            objQuoOrders.InvType = "O"

            ThreadSafe(Sub() gcOrders.DataSource = objQuoOrders.GetOrdersByPicker())
        Catch ex As Exception
            MessageBox.Show(ex.Message, "LoadOrder", MessageBoxButtons.OK, MessageBoxIcon.Error)
            objError.LogError(SystemApplicationLogSource, "AceFinancials", ex)
        End Try
    End Sub

    Private Sub ActivateDependency()
        Dim objError As New AppFramework.Logging.EventLog
        Try
            If sqlConnection IsNot Nothing AndAlso sqlConnection.State = ConnectionState.Open Then sqlConnection.Close()

            sqlConnection = New SqlConnection(AppFramework.Database.Connection.ConnectionString)
            sqlConnection.Open()

            SqlDependency.Stop(AppFramework.Database.Connection.ConnectionString)
            SqlDependency.Start(AppFramework.Database.Connection.ConnectionString)
            Using cmd As SqlCommand = New SqlCommand(" Select tb_quotation_notifications.notification_id, tb_quotation_notifications.notification_invid 
                                                       From dbo.tb_quotation_notifications
                                                       Where tb_quotation_notifications.notification_picker_id = " & UserID &
                                                     " And tb_quotation_notifications.company_id = " & SystemCompanyId, sqlConnection)

                Dim dependency As SqlDependency = New SqlDependency(cmd)

                AddHandler dependency.OnChange, AddressOf dependency_OnChange

                cmd.ExecuteNonQuery()
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ActivateDependency", MessageBoxButtons.OK, MessageBoxIcon.Error)
            objError.LogError(SystemApplicationLogSource, "AceFinancials", ex)
        End Try
    End Sub

    Private Sub dependency_OnChange(sender As Object, e As SqlNotificationEventArgs)
        Dim objError As New AppFramework.Logging.EventLog
        Try
            If e.Info = SqlNotificationInfo.Insert Then
                NotificationManager.ShowNotification(NotificationManager.Notifications(0))
                LoadOrder()
                ActivateDependency()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "dependency_OnChange", MessageBoxButtons.OK, MessageBoxIcon.Error)
            objError.LogError(SystemApplicationLogSource, "AceFinancials", ex)
        End Try
    End Sub

    Private Sub ThreadSafe(method As MethodInvoker)
        Dim objError As New AppFramework.Logging.EventLog
        Try
            If (InvokeRequired) Then
                Invoke(method)
            Else
                method()
            End If
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ThreadSafe", MessageBoxButtons.OK, MessageBoxIcon.Error)
            objError.LogError(SystemApplicationLogSource, "AceFinancials", ex)
        End Try
    End Sub

如果您需要更多信息,我将适当更新我的问题

yjghlzjz

yjghlzjz1#

在丹·古兹曼的大力帮助下,我发现了问题所在。
在OnChange事件触发后,您需要删除事件处理程序,然后再次订阅该事件,这与我所做的不同,我所做的是停止SqlDependency并再次启动它。
在我的例子中,我是这样做的
更改时事件

Private Sub dependency_OnChange(sender As Object, e As SqlNotificationEventArgs)
        Dim objError As New AppFramework.Logging.EventLog
        Try
            If e.Info = SqlNotificationInfo.Insert Then
                NotificationManager.ShowNotification(NotificationManager.Notifications(0))
                LoadOrder()
            End If
            Dim dependency As SqlDependency = sender
            RemoveHandler dependency.OnChange, AddressOf dependency_OnChange
            ActivateDependency()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "dependency_OnChange", MessageBoxButtons.OK, MessageBoxIcon.Error)
            objError.LogError(SystemApplicationLogSource, "AceFinancials", ex)
        End Try
    End Sub

订阅事件

Private Sub ActivateDependency()
        Dim objError As New AppFramework.Logging.EventLog
        Try
            If sqlConnection IsNot Nothing AndAlso sqlConnection.State = ConnectionState.Open Then sqlConnection.Close()

            sqlConnection = New SqlConnection(AppFramework.Database.Connection.ConnectionString)
            sqlConnection.Open()

            Using cmd As SqlCommand = New SqlCommand(" Select tb_quotation_notifications.notification_id, tb_quotation_notifications.notification_invid 
                                                       From dbo.tb_quotation_notifications
                                                       Where tb_quotation_notifications.notification_picker_id = " & UserID &
                                                     " And tb_quotation_notifications.company_id = " & SystemCompanyId, sqlConnection)

                Dim dependency As SqlDependency = New SqlDependency(cmd)

                AddHandler dependency.OnChange, AddressOf dependency_OnChange

                Using sqlReader As SqlDataReader = cmd.ExecuteReader

                End Using
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.Message, "ActivateDependency", MessageBoxButtons.OK, MessageBoxIcon.Error)
            objError.LogError(SystemApplicationLogSource, "AceFinancials", ex)
        End Try
    End Sub

多谢了祝你今天愉快。
编辑:
我移动了窗体加载函数中的SqlDependency.Start()和窗体关闭函数中的stop

相关问题