none
Windows application and Query Not:how to get only the data changed RRS feed

  • Question

  • Hi,

    this question is between ADO.NET 2.0 and query notification(service broker).

     

    I have a win32 application that displays a message to the user every time "something" change on the DB (no refresh of datagrid view)

     

    I want to display to the user ONLY the data that has been inserted of modified but everytime I get the all resultset.

    Sub Form_Load()

    SqlDependency.Stop(GetConnectionString())

    SqlDependency.Start(GetConnectionString())

     

    Dim command as SqlCommand = New SqlCommand(GetSQL(), connection)

     

    If dataToWatch Is Nothing Then

    dataToWatch = New DataSet()

    connection.Open()

    Dim cc As SqlDataReader = command.ExecuteReader()

    connection.Close()

    End sub

     

    Sub CreateDependency()

    command.Notification = Nothing

    SqlDependency = New SqlDependency(command)

    AddHandler sQLDependency.OnChange, New OnChangeEventHandler(AddressOf SQLDependency_Change)

     

    Using adapter As New SqlDataAdapter(command)

    adapter.Fill(dataToWatch, "internaldt")

    End Using

    End sub

     

    Sub SqlDependency_Change(ByVal sender As Object, ByVal e As SqlNotificationEventArgs)

    ' *** A SQLDependency is good for one call only ***

    Try

    RemoveHandler SQLDependency.OnChange, New OnChangeEventHandler(AddressOf SqlDependency_Change)

    If e.Info.ToString.Equals("Insert") Then

    connection.Open()

    command.Notification = Nothing

    Using dr As SqlDataReader = command.ExecuteReader()

     

    dataToWatch.Tables(0).Load(dr, LoadOption.Upsert)

     

    End Using

    Me.NotifyIcon1.BalloonTipText = Me.NotifyIcon1.BalloonTipText & "A new id : " & dataToWatch.Tables(0).Rows(dataToWatch.Tables(0).Rows.Count - 1).Item(2) & Microsoft.VisualBasic.vbCrLf

    Me.NotifyIcon1.ShowBalloonTip(30)

    End If

    Catch ex As Exception

    MsgBox(ex.ToString)

    Finally

    If connection.State = ConnectionState.Open Then

    connection.Close()

    End If

    CreateDependency()

    End Try

     

    End Sub

     

    I would like that in the callback methodTongue TiedqlDependency_Change I retrieve only the data that has been changed?

    It is possible?

    What are the objects that I have to use?

    Thank you

    Marina B.

     

    Wednesday, December 12, 2007 1:55 PM