SQLDependency仅接收一次通知后停止触发OnChange事件求助

SqlDependency首次触发OnChange后不再响应，核心问题及修复方案如下：

关键问题点 #

1. SqlDependency.Start/Stop滥用：每次调用ActivateDependency都执行停止再启动操作，会销毁所有现有订阅，导致后续通知失效。SqlDependency.Start只需在应用启动时调用一次，退出时调用Stop即可。
2. SQL语句未参数化：直接拼接UserID和SystemCompanyId到SQL，存在注入风险，还可能破坏SqlDependency对查询格式的严格要求。
3. 错误的命令执行方式：使用cmd.ExecuteNonQuery()无法触发SqlDependency的订阅初始化，必须通过ExecuteReader获取结果集完成订阅。
4. 订阅对象生命周期管理不当：原代码中dependency对象在Using块内被销毁，可能导致事件通知无法正常传递。

修改后的完整代码 #

窗体初始化与清理（在Form的Load/Closed事件中处理） #

Private Sub Form_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    ' 仅启动一次SqlDependency
    If Not SqlDependency.Start(AppFramework.Database.Connection.ConnectionString) Then
        MessageBox.Show("SqlDependency启动失败")
    End If
    ActivateDependency()
End Sub

Private Sub Form_Closed(sender As Object, e As EventArgs) Handles MyBase.Closed
    ' 应用退出时停止SqlDependency并关闭连接
    SqlDependency.Stop(AppFramework.Database.Connection.ConnectionString)
    If sqlConnection IsNot Nothing AndAlso sqlConnection.State = ConnectionState.Open Then
        sqlConnection.Close()
    End If
End Sub

修正ActivateDependency方法 #

Private sqlConnection As SqlConnection
Private Sub ActivateDependency()
    Dim objError As New AppFramework.Logging.EventLog
    Try
        ' 复用连接而非每次重建
        If sqlConnection Is Nothing OrElse sqlConnection.State <> ConnectionState.Open Then
            sqlConnection = New SqlConnection(AppFramework.Database.Connection.ConnectionString)
            sqlConnection.Open()
        End If

        ' 使用参数化查询避免注入并符合SqlDependency要求
        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 = @CompanyId", sqlConnection)

            cmd.Parameters.AddWithValue("@UserId", UserID)
            cmd.Parameters.AddWithValue("@CompanyId", SystemCompanyId)

            ' 重置命令通知并创建新订阅
            cmd.Notification = Nothing
            Dim dependency As New SqlDependency(cmd)
            AddHandler dependency.OnChange, AddressOf dependency_OnChange

            ' 执行查询初始化订阅，无需保留结果阅读器
            Using reader = 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

修正OnChange事件处理 #

Private Sub dependency_OnChange(sender As Object, e As SqlNotificationEventArgs)
    Dim objError As New AppFramework.Logging.EventLog
    Try
        ' 移除旧事件处理避免内存泄漏
        Dim dependency = DirectCast(sender, SqlDependency)
        RemoveHandler dependency.OnChange, AddressOf dependency_OnChange

        ' 处理插入通知
        If e.Info = SqlNotificationInfo.Insert Then
            NotificationManager.ShowNotification(NotificationManager.Notifications(0))
            ThreadSafe(AddressOf LoadOrder)
        End If

        ' 非错误/无效状态下重新创建订阅
        If e.Info <> SqlNotificationInfo.Error AndAlso e.Info <> SqlNotificationInfo.Invalid Then
            ThreadSafe(AddressOf 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

额外注意事项 #

• 确保SQL Server已启用Service Broker，执行SQL：ALTER DATABASE [你的数据库名] SET ENABLE_BROKER;
• 查询必须严格符合SqlDependency要求：指定表所有者（如dbo）、不能用*、不能用聚合函数等
• 所有UI操作必须通过ThreadSafe方法切换到UI线程，避免跨线程异常

内容的提问来源于stack exchange，提问作者Hristijan Mizimakovski