locked
is there a way to tell if there has been a change before I sync? RRS feed

  • Question

  • I want to be able to give the user more control when the  sync occurs. Is there a way to tell if there has been a db change before I sync?

     

    I'm looking for a method that would look like this

     

    syncAgent.CheckforAnyUpdates

     instead of

    synchAgent.synchronize() then displaying the stats.

     

     

    • Moved by Hengzhe Li Friday, April 22, 2011 2:58 AM (From:SyncFx - Microsoft Sync Framework Database Providers [ReadOnly])
    Wednesday, June 18, 2008 8:46 PM

Answers

  • I fixed the code and it works now. I needed to create the tablemeta/synctablemetadata in the for loop.

        Public Function IsDataChanged() As Boolean

            Dim ClientProvider As SqlCeClientSyncProvider
            Dim SyncAgent As LocalDataCacheSyncAgent = New LocalDataCacheSyncAgent()


            ClientProvider = CType(SyncAgent.LocalProvider, SqlCeClientSyncProvider)
            Dim groupMeta As SyncGroupMetadata = New SyncGroupMetadata()

            ' build out groupmeta data
            For Each syncTableObj In SyncAgent.Configuration.SyncTables
                Dim tableMeta As SyncTableMetadata = New SyncTableMetadata()
                tableMeta.TableName = syncTableObj.TableName
                tableMeta.LastReceivedAnchor = ClientProvider.GetTableReceivedAnchor(syncTableObj.TableName)
                groupMeta.TablesMetadata.Add(tableMeta)
            Next

            Dim session = New SyncSession()
            session.ClientId = ClientProvider.ClientId

            Dim sp1 = CType(SyncAgent.RemoteProvider, Server.DbServerSyncProvider)
            Dim changes As SyncContext = sp1.GetChanges(groupMeta, session)

            If changes.DataSet.Tables.Count > 0 Then
                Return True
            Else
                Return False
            End If

        End Function
    Monday, December 1, 2008 12:33 PM

All replies

  • what you can do is to call the serverProvider.GetChanges(). and to see if the retuned syncContext has the dataset that contains data in it.

     

    thanks

    Yunwen

     

    Thursday, June 19, 2008 1:30 AM
    Moderator
  • No that won''t work. I'm using the local database cache that you add into a project.

     

    Friday, June 20, 2008 4:11 PM
  • I'm not 100% clear why you are not able to implement Yunwen's recommendation.  Let’s assume that you have tied the initiation of your sync operation to a button.  In the on click event for that button you can call the GetChanges() event on the server provider in order to determine of any changes have occurred and then fire the Synchronize() method accordingly.  Can you provide more details around your scenario?  Specifically, why do you want to check for change prior to synchronization?  Is the intent to improve perf or is this a functional requirement?

     

    Regards,

     

    Sean Kelley

    Microsoft

    Program Manager

     

    Saturday, June 21, 2008 12:02 AM
    Moderator
  • We have a functional requirement to check before synching. The user could be in the middle of a study that was based on the old data. They may want to complete the study before the data is updated.

     

    to my exisiting VB.net project I added a local database cache by right clicking on the project and selecting local database chache from the data item. From there I set my data sources and selected the tables I wanted to sync.

    at this point I have a localdatacachsyncagent.

     

    There are no methods/properties for serverProvider.GetChanges in my localdatacachsyncagent

    off the syncagent there is

     configuraton, localprovider, remoteprovider, sessionstate, synchronize and syncstatisics

     

    I was looking at this but I'm not sure if it's heading in the right direction.

     

    Dim sp As Microsoft.Synchronization.Data.ServerSyncProvider

    sp = CType(syncAgent.RemoteProvider, Microsoft.Synchronization.Data.ServerSyncProvider)

    Dim c = sp.GetChanges(   ? , ? ) 

     

    I don't know where to get the paramenters for GetChanges.

     

    If you could tell me where or how to set serverprovider.getchanges I would appreciate it.

     

     

    Monday, June 23, 2008 3:13 PM
  •  

    Hi,

     

        Are you asking about SQL Compact side changes to be uploaded, or SQL Server side changes. If it is SQL Compact side changes, you can do the following, (though, might look hacky). Fire the below query on each of the tracked tables:

     

    select * from <table name> Tbl where

    (

        (

            (Tbl.__sysInsertTxBsn IS NOT NULL)

            AND

              (

                (Tbl.__sysInsertTxBsn NOT IN

                    (select __sysTxBsn from __sysTxCommitSequence)  AND Tbl.__sysInsertTxBsn > LCSN

      )

      OR

      (exists

          (select __sysTxBsn from __sysTxCommitSequence where Tbl.__sysInsertTxBsn = __sysTxBsn  AND __sysTxCsn > LCSN)

     )

    )

        )

        OR

        (

            (Tbl.__sysChangeTxBsn IS NOT NULL)

            AND

            (

                (Tbl.__sysChangeTxBsn NOT IN

                    (select __sysTxBsn from __sysTxCommitSequence)  AND Tbl.__sysChangeTxBsn > LCSN

                )

                OR

                (exists

          (select __sysTxBsn from __sysTxCommitSequencewhere Tbl.__sysChangeTxBsn = __sysTxBsn AND __sysTxCsn > LCSN)

                )

            )

        )

    )

     

    These are tracking columns, which have some data used for determining rows to be synched now. LSN is the previous sync water mark, which will be reverse of SentAnchor stored in the __sysOCSSubscriptions table. (For ex, if 01000000 is stored, this is actually 0x01 or 1. If 01300000 is stored, it will be 0x301 etc...) 

     

    Also, you need to do similar querying on the tombstone table to see if any deletions are there to be sent.

     

    Thanks

    Udaya

    Tuesday, June 24, 2008 4:34 PM
  • Interesting but I'm trying to determine if the db on the server has changed.

    If this helps I'm using the latest SQLServer 2008 beta and latest VS beta. The tracking tables for sql server are held internal to the system not in my db.

     

    Tuesday, June 24, 2008 6:32 PM
  • Here is the code snippet that can be used to detect changes before doing a real sync.

     

    // Assume the syncAgent object is sa.

     

    DbServerSyncProvider sp1 = (DbServerSyncProvider)sa.RemoteProvider;

    SqlCeClientSyncProvider cp = (SqlCeClientSyncProvider)sa.LocalProvider;

    SyncGroupMetadata groupMetadata = new SyncGroupMetadata();

    SyncTableMetadata tableMetadata = new SyncTableMetadata();

     

    string tableName = sa.Configuration.SyncTables[0].TableName;

    tableMetadata.TableName = tableName;

    tableMetadata.LastReceivedAnchor = cp.GetTableReceivedAnchor(tableName);

    groupMetadata.TablesMetadata.Add(tableMetadata);

    SyncSession session = new SyncSession();

    session.ClientId = cp.ClientId;

    SyncContext changes = sp1.GetChanges(groupMetadata, session);

     

    then you can check the datatables, rows in the changes.DataSet to see the changes that will be send to this client.

     

    Hope this helps.

     

    thanks

    Yunwen

    Thursday, June 26, 2008 3:48 AM
    Moderator
  • I think this is the right direction. I converted to VB and I'm still working through it. I'll post when I'm done. I think that the changes.dataset.tables.count would be > 0 if there is something to be synched. I'll also need to

    add all the tables to groupmetadata (loop through all the tables in sa.configuration.synctables).

     

     

     

     

     

    Thursday, June 26, 2008 7:56 PM
  •  

    yes, if you have mutiple tables, you will need to do so.

     

    please share out your experience, feekback etc on this once you got it done.

     

    thanks

    Yunwen

    Thursday, June 26, 2008 10:38 PM
    Moderator
  • Hi UdayaBG

    I'd like to be able to pull records out of a table on the device to allow the user to view/edit only the records that haven't been synchronized yet.
    From the sounds of it, your query above should be exactly what I'm looking for. The problem that I'm having however is that it's telling me that it doesn't recognize the LCSN column. Where is this 'water mark' / column located and how do I get it?

    I'd really appreciate your help.

    Thanks,

    Paul
    Wednesday, October 29, 2008 11:46 AM
  • Copy paste from Udaya's post: I think LCSN is the LSN described below.

     

    These are tracking columns, which have some data used for determining rows to be synched now. LSN is the previous sync water mark, which will be reverse of SentAnchor stored in the __sysOCSSubscriptions table. (For ex, if 01000000 is stored, this is actually 0x01 or 1. If 01300000 is stored, it will be 0x301 etc...) 

     

    Friday, October 31, 2008 6:38 AM
  • I'm back to this puzzle. I tried this code and I don't get anything back but 0 for changes.DataSet.Tables.Count
    my sync code seems to work fine so what am I missing in order to determine if there is a change on the db server side before I sync? Can you try this code out (in vb.net) and see what you get?

     Public Function IsDataChanged() As Boolean

            Dim ClientProvider As SqlCeClientSyncProvider
            Dim SyncAgent As LocalDataCacheSyncAgent = New LocalDataCacheSyncAgent()

            ClientProvider = CType(SyncAgent.LocalProvider, SqlCeClientSyncProvider)
          
            Dim groupMeta As SyncGroupMetadata = New SyncGroupMetadata()
            Dim tableMeta As SyncTableMetadata = New SyncTableMetadata()

            For Each syncTableObj In SyncAgent.Configuration.SyncTables
                tableMeta.TableName = syncTableObj.TableName
                tableMeta.LastReceivedAnchor = ClientProvider.GetTableReceivedAnchor(syncTableObj.TableName)
                groupMeta.TablesMetadata.Add(tableMeta)
            Next

            Dim session = New SyncSession()
            session.ClientId = ClientProvider.ClientId

            Dim sp1 = CType(SyncAgent.RemoteProvider, Server.DbServerSyncProvider)
            Dim changes As SyncContext = sp1.GetChanges(groupMeta, session)

            If changes.DataSet.Tables.Count > 0 Then
                Return True
            Else
                Return False
            End If

      End Function

    Tuesday, November 25, 2008 10:50 PM
  • I fixed the code and it works now. I needed to create the tablemeta/synctablemetadata in the for loop.

        Public Function IsDataChanged() As Boolean

            Dim ClientProvider As SqlCeClientSyncProvider
            Dim SyncAgent As LocalDataCacheSyncAgent = New LocalDataCacheSyncAgent()


            ClientProvider = CType(SyncAgent.LocalProvider, SqlCeClientSyncProvider)
            Dim groupMeta As SyncGroupMetadata = New SyncGroupMetadata()

            ' build out groupmeta data
            For Each syncTableObj In SyncAgent.Configuration.SyncTables
                Dim tableMeta As SyncTableMetadata = New SyncTableMetadata()
                tableMeta.TableName = syncTableObj.TableName
                tableMeta.LastReceivedAnchor = ClientProvider.GetTableReceivedAnchor(syncTableObj.TableName)
                groupMeta.TablesMetadata.Add(tableMeta)
            Next

            Dim session = New SyncSession()
            session.ClientId = ClientProvider.ClientId

            Dim sp1 = CType(SyncAgent.RemoteProvider, Server.DbServerSyncProvider)
            Dim changes As SyncContext = sp1.GetChanges(groupMeta, session)

            If changes.DataSet.Tables.Count > 0 Then
                Return True
            Else
                Return False
            End If

        End Function
    Monday, December 1, 2008 12:33 PM
  • Hi UdayaBG,

    I am getting error for "LSN" what is this and where it will be ?

    Thanks  in advance
    Thursday, June 11, 2009 6:42 AM