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 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.

Answer 2

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