SQLDependency stops receiving notifications after 1 notification

Question

Good afternoon everyone.

I am working on a project that uses WinForms VB.NET and MSSQL I have a requirement to use SQLDependency to monitor a table for new inserts. I have encountered a problem while testing it out. Inserting once after starting the application and I do receive the notification that something has been inserted, but after that the OnChange event just doesn't get called anymore.

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

If you need any more info I will update my question appropriately

Answer 1

I found out what the problem was with the big help of Dan Guzman.

After the OnChange event fires you need to remove the event handler and then subscribe to the event again, unlike what I was doing and that is stoping the SqlDependency and starting it again.

In my case I have done that as follows

OnChange Event

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

Subscribing event

    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

Thanks a lot have a nice day.

Edit:

I moved the SqlDependency.Start() in the form load function, and the stop in the form closing function