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

    lundi 30 janvier 2012 01:11

Toutes les réponses

  • 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


    mardi 31 janvier 2012 06:13