vb.net 中可能有多个 sqldependency 吗？

Question

基本上我的代码是基于这里 http://www.dreamincode。 net/forums/topic/185244-using-sqldependency-to-monitor-sql-database-changes/

目前的情况是我有 2 个表希望监控所以我简单地用第一个 sqldependancy 复制另一个类似的代码，但它失败了，并且似乎最新的 sqldependancy 将取代以前的 sqldependancy 函数。

这是我的代码，

 Public Sub GetNames()
        If Not DoesUserHavePermission() Then
            Return
        End If


        lbQueue.Items.Clear()

        ' You must stop the dependency before starting a new one.
        ' You must start the dependency when creating a new one.
        Dim connectionString As String = GetConnectionString()
        SqlDependency.Stop(connectionString)
        SqlDependency.Start(connectionString)


        Using cn As SqlConnection = New SqlConnection(connectionString)

            Using cmd As SqlCommand = cn.CreateCommand()

                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT PatientID FROM dbo.[patient_queue]"

                cmd.Notification = Nothing

                ' creates a new dependency for the SqlCommand
                Dim dep As SqlDependency = New SqlDependency(cmd)
                ' creates an event handler for the notification of data changes in the database
                AddHandler dep.OnChange, AddressOf dep_onchange

                cn.Open()

                Using dr As SqlDataReader = cmd.ExecuteReader()

                    While dr.Read()

                        lbQueue.Items.Add(dr.GetInt32(0))
                        doctor.lbqueue.items.add(dr.GetInt32(0))

                    End While

                End Using

            End Using

        End Using
    End Sub

    Private Sub dep_onchange(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlNotificationEventArgs)

        ' this event is run asynchronously so you will need to invoke to run on the UI thread(if required)
        If Me.InvokeRequired Then

            lbQueue.BeginInvoke(New MethodInvoker(AddressOf GetNames))
            My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Asterisk)

        Else

            GetNames()

        End If

        ' this will remove the event handler since the dependency is only for a single notification
        Dim dep As SqlDependency = DirectCast(sender, SqlDependency)
        RemoveHandler dep.OnChange, AddressOf dep_onchange

    End Sub


    Public Sub GetMedID()
        If Not DoesUserHavePermission() Then
            Return
        End If


        lbMedQueue.Items.Clear()

        ' You must stop the dependency before starting a new one.
        ' You must start the dependency when creating a new one.
        Dim connectionString As String = GetConnectionString()
        SqlDependency.Stop(connectionString)
        SqlDependency.Start(connectionString)


        Using cn As SqlConnection = New SqlConnection(connectionString)

            Using cmd As SqlCommand = cn.CreateCommand()

                cmd.CommandType = CommandType.Text
                cmd.CommandText = "SELECT RecordID FROM dbo.[medicine_queue]"

                cmd.Notification = Nothing

                ' creates a new dependency for the SqlCommand
                Dim dep As SqlDependency = New SqlDependency(cmd)
                ' creates an event handler for the notification of data changes in the database
                AddHandler dep.OnChange, AddressOf dep_onchange2

                cn.Open()

                Using dr As SqlDataReader = cmd.ExecuteReader()

                    While dr.Read()

                        lbMedQueue.Items.Add(dr.GetInt32(0))


                    End While

                End Using

            End Using

        End Using
    End Sub


    Private Sub dep_onchange2(ByVal sender As System.Object, ByVal e As System.Data.SqlClient.SqlNotificationEventArgs)

        ' this event is run asynchronously so you will need to invoke to run on the UI thread(if required)
        If Me.InvokeRequired Then

            lbMedQueue.BeginInvoke(New MethodInvoker(AddressOf GetMedID))
            My.Computer.Audio.PlaySystemSound(Media.SystemSounds.Asterisk)

        Else

            GetMedID()

        End If

        ' this will remove the event handler since the dependency is only for a single notification
        Dim dep As SqlDependency = DirectCast(sender, SqlDependency)
        RemoveHandler dep.OnChange, AddressOf dep_onchange2

    End Sub

最后我在加载表单上调用了 GetNames,GetMedID，它工作正常，只是 GetMedID 正在运行，并且 GetNames 在 onchanged 时不会触发事件。

Answer 1

我认为这里的主要问题是您在每个数据访问方法中调用 .Stop 然后调用 .Start ，从而在每次访问时取消并重新启动依赖项方法。

您只需在应用程序启动时调用 .Start 一次，并在应用程序结束时调用 .Stop 类似。

例如，在 Web 应用程序中，最好的位置是 Global.asax Application_Start 和 Application_End 事件。

Answer 2

我认为你是对的，我遇到了同样的问题。即使在 New SqlDependency(cmd) 之后，对 SqlDependency.Start(connectionString) 的第二次调用也会替换现有的初始默认 Service Broker 服务和队列。

Service Broker 在每次启动时使用 GUID 作为服务和队列名称的一部分创建默认服务和队列：Service{GUID} 和 Queue{GUID} - 但似乎只有一个默认服务/队列对可用，

您可以验证这是通过在第一个 Start 之后和第二个 Start 之后立即放置一个断点来实现的。转到 SQL Server，转到您的 dBase 并查看
Service Broker/服务和 Service Broker/队列文件夹。您需要右键单击“服务”和“队列”文件夹，然后在第二个断点后选择“刷新”