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

Hi siewchoong,

I remember yours another similar thread here. 

Have you considered using SqlCacheDependency along with AggregateCacheDependency to associate multiple dependencies? 

Consider this C# snippet:
SqlCacheDependency 
   dep1 = new SqlCacheDependency ("MyDb", "Users"),
   dep2 = new SqlCacheDependency ("MyDb", "UserRoles"),
   dep3 = new SqlCacheDependency ("MyDb", "Policies");

AggregateCacheDependency aggDep = new AggregateCacheDependency ();
aggDep.Add (dep1, dep2, dep3);

Cache.Insert ("SomeKey", someData, aggDep);

There are dependencies on tables Users, UserRoles and Policies, all tied together by an aggregate cache dependency. Anything changes in one of these tables—the cached item is invalidated, and it’s time to bring in and cache fresh data.

For VB sample code, please view the here.

---

Stephanie Lv

TechNet Community Support