possible of multiple sqldependancy in vb.net?

Question

Basically my code is based on here http://www.dreamincode.net/forums/topic/185244-using-sqldependency-to-monitor-sql-database-changes/

Current situation is i'm having 2 table wish to monitor so i simple duplicate another similar code with first sqldependancy, but it's failed and seem like the latest sqldependancy will replace the previous sqldependancy function.

here is the code of mine

 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

finally i called GetNames,GetMedID on load form, it worked fine,just GetMedID is functioning and GetNames does not firing event when onchanged.

Answer 1

I think you are right, I ran into the same issue. A second call to SqlDependency.Start(connectionString), even after a New SqlDependency(cmd), replaced the existing, initial default Service Broker service and queue.

Service Broker creates a default Service and Queue on each Start using the a GUID as part of the Service and Queue names: Service{GUID} and Queue{GUID} - but there seems to only be one defualt Service/Queue pair available

You can verify this by putting a break-point immediately after the first Start and immediately after the second Start. Go to SQL Server, go to your dBase and look at the Service Broker/Services and Service Broker/Queues folders. You'll need to right-click on the Services and Queues folders and select refresh after the second break-point

Answer 2

I think the main problem here is that you are calling .Stop and then .Start within each of your data access methods, thus cancelling and restarting the dependency each time you access the methods.

You just need to call .Start once when your application starts, and similarly .Stop when it ends.

For example, in a web application the best place for this is the Global.asax Application_Start and Application_End events.