none
Using SFx 2.1 metadata to determine if new insert on SDF client has been sync'd RRS feed

  • Question

  • When I insert a new record into a table in my SDF I want to allow the user to be able to delete it only until the new record has been sync'd ... after it has been sync'd I do not want the record to be deletable.

    How do I query the metadata for a KeyField value to see if the insert has been sync'd?

    I can create my own fields for this purpose ... but I would think I can get it from metadata.

    I need to know if it 1) is an insert and 2) has been sync'd.

    EDIT: Is it as simple as: a reference does not exist in the _tracking table until it is sync'd for the first time?


    • Edited by P H T Thursday, May 10, 2012 12:43 PM
    Thursday, May 10, 2012 12:31 PM

All replies

  • the record of what has been synched is  stored by the destination not the source... meaning, if you upload from an SDF to a SQL Server, its the knowledge in the SQL Server that is updated to reflect the upload. if you download to an SDF from SQL Server, its the SDF that records what has been synched.

    rather than play around with the tracking tables or the SDF system tables, just create a table that records either the date or the timestamp of the last sync...then have a field in your tables to record the insert date/timestamp that you can compare with the last sync date/timestamp.

    Thursday, May 10, 2012 1:08 PM
    Moderator
  • Thanks JuneT,

    I have the infrastructure in place to do exactly what you say ... but ... after asking the question I realized that adding a record to the SDF does not immediately add a record to the _tracking table (obvious since no triggers in SqlCe).  The record must be added when a sync occurs.  So it appears that I can determine if it has been sync'd by a simple query to see if it's Key exists in the _tracking table.

    Any pit falls with this very simple solution?

    Thursday, May 10, 2012 1:18 PM
  • in reality, the only way to check if it has been synched is to check the destination... during a sync, a record may encounter an error or conflict during application.

    one thing you should consider checking the key against the tracking table is that you're querying a multi-row tracking table (you may have a thousand records in the tracking table) vs querying a single row table that simply records the last sync.

    Thursday, May 10, 2012 1:24 PM
    Moderator
  • I get you on the benefits of your proposed solution ... Thanks.

    But it sounds like for my particular application, querying the _tracking table may be acceptable ... i.e. allowing deletes on inserts only before they propagates beyond the SDF.

    If a new SDF insert record encounters an error/conflict during application into SQL then it sounds like the SDF _tracking table will be updated anyway thus being "optimistic" in that the SDF user will not be allowed to delete it ... because the record has in some form (possibly incomplete) now been propagated beyond the SDF.

    Also, any suggestions on where I can readup on how/when the metadata gets updated?

    Thursday, May 10, 2012 1:49 PM
  • I use the following to get the lastSyncMark paramter:

    SELECT scope_tomb_mark FROM [SCOPE_INFO] WHERE [SYNC_SCOPE_NAME]='YOUR_SCOPE_NAME'

    Then to detect inserts or updates that have not been synced:

    StringBuilder sb = new StringBuilder(); sb.Append("SELECT COUNT(*) FROM (SELECT UT0.* FROM ["); sb.Append(tableName); sb.Append("] AS UT0 WHERE UT0.__sysChangeTxBsn IS NOT NULL AND UT0.__sysChangeTxBsn > "); sb.Append(lastSyncMark); sb.Append(") AS UT LEFT OUTER JOIN (SELECT TXCS0.* FROM __sysTxCommitSequence AS TXCS0) AS TXCS ON UT.__sysChangeTxBsn "); sb.Append("= TXCS.__sysTxBsn OR TXCS.__sysTxBsn IS NULL WHERE (TXCS.__sysTxBsn IS NOT NULL AND TXCS.__sysTxCsn > "); sb.Append(lastSyncMark); sb.Append(") OR (TXCS.__sysTxBsn IS NULL AND UT.__sysChangeTxBsn > "); sb.Append(lastSyncMark); sb.Append(")");

    This seems to work. Although I haven't been able to confirm if it works in all scenarios.  I would suggest that it may not work if you are doing peer to peer, but at the moment I am only doing client server. The query above I got from looking at the sync trace file. Using the scope_tomb_mark as the lastSyncMark is a guess, but it corresponds to the parameter value I see in the sync trace file.

    Maybe JuneT can comment if this is complete nonsense or not!! :-)

    Ian

    Friday, May 18, 2012 4:37 PM
  • Currently I am shying away from any solution that relies on determining if a specific record has been sync'd because I can't guarantee that in the future all my non-maintenance tables will be in a single scope and that the scopes will always sync in order of PK scope first then FK scope next ... meaning at some point I may have a child records scope syncing before the parent record scope syncs.

    I think JuneT's comments on comparing a record's timestamp to a sync date will be better for a broader range of scenarios since it is abstracted from the sync knowledge ... but the sync date will be when a sync was attempted (not successfully finished) so that as soon as a user tries to sync they will not be permitted to delete any records (just in case any child records were already propagated to the sync community in a prior sync attempt or earlier in the batched scope).

    Having said that ... I do see value in determining if a particular record has been sync'd ... but the business problem I am currently trying to solve does not need to have that knowledge.

    Monday, May 21, 2012 1:29 PM
  • if i want to rely on the sync knowledge to tell me if there's some changes that need to be synched, i just normally call a normal sync, then on the ChangesSelected event, simply abort the sync by raising an exception...
    Monday, May 21, 2012 3:02 PM
    Moderator