Update SQL CE 3.5 SP1 Change Tracking Metadata

Why

SqlCeClientSyncProvider does not populate change tracking information. This is reasonable as when it get data from server, there is no change. However, if you want to use SqlCeClientSyncProvider for peer-to-peer scenario, you need to populate the change tracking information, so the changes can be marked and pick up by other nodes. This is applicable for two step upload/check in process as well. Sometimes you may also want to clear the change tracking information, so they will not be sync back to server.

SQL Server Compact Edition 3.5 SP1 change trakcing metadata

Sync Framework Database Providers (known as Sync Services for ADO.NET) store change tracking information in following tables and columns for SQL CE 3.5 SP1:

• Each table contains these two columns that indicate changed or inserted records: __sysChangeTxBsn, __sysInsertTxBsn.
• Table __sysOcsDeletedRows stored deleted records. It has these columns: __sysTName, __sysRK, __sysDeleteTxBsn, __sysInsertTxCsn, __sysDeletedTime.
• Table __sysSyncArticles contains anchors. It has these columns: TableName, SentAnchor, ReceivedAnchor, ClientId.
• Table __sysSyncSubscriptions contains ClientId, ServerId, MachineId.

TxBsn stands for Transaction Begin Counter. TxCsn stands for Transaction Commit Counter. See details: SQL CHANGE TRACKING LAYER.

Change anchors

Anchors can be changed by these two methods:

• SqlCeClientSyncProvider.SetTableSentAnchor
• SqlCeClientSyncProvider.SetTableReceivedAnchor

This is the code to decode SentAnchor:

reader.GetBytes(0, 0L, buffer, 0, 8);
long num2 = BitConverter.ToInt64(buffer, 0);

ReceivedAnchor is returned by SelectNewAnchorCommand from server, and serialized in following way:

object _rawNewAnchor = newAnchor;
using (MemoryStream serializationStream2 = new MemoryStream())
{
  new BinaryFormatter().Serialize(serializationStream2, _rawNewAnchor);
  SyncAnchor newAnchor = (new SyncAnchor());
  newAnchor.Anchor = serializationStream.ToArray();
//...
}

newAnchor.Anchor is the value of __sysSyncArticles.ReceivedAnchor.

Change System data

Tables and columns with “__" prefix store system data that cannot be update by open APIs. Internal APIs only are exposed to friendly assemblies developed by Microsoft. But we can all them via reflection.

private PropertyInfo engineFlagsProperty;
private SqlCeTransaction transation;
private SeTransactionFlags engineFlags;
private int refCntSystemTx;

private void EnterSystemAPI()
{
    if (this.transation != null)
    {
        this.engineFlags = (SeTransactionFlags)this.engineFlagsProperty.GetValue(this.transation, null);
        engineFlagsProperty = typeof(SqlCeTransaction).GetProperty("EngineFlags", BindingFlags.NonPublic | BindingFlags.Instance);
        this.engineFlagsProperty.SetValue(this.transation, SeTransactionFlags.GENERATEROWGUID | SeTransactionFlags.GENERATEIDENTITY | SeTransactionFlags.SYSTEM, null);
    }

    this.refCntSystemTx++;
}       

private void LeaveSystemAPI()
{
    this.refCntSystemTx--;
    if (this.refCntSystemTx <= 0)
    {
        if (this.transation != null)
        {
            this.engineFlagsProperty.SetValue(this.transation, this.engineFlags, null);
        }

        this.refCntSystemTx = 0;
    }
}

[Flags]
internal enum SeTransactionFlags
{
    COMPRESSEDLVSTREAM = 0x40,
    DISABLETRIGGERS = 0x20,
    GENERATEIDENTITY = 2,
    GENERATEROWGUID = 4,
    NOFLAGS = 0,
    REPLACECOLUMN = 0x10,
    SYSTEM = 1,
    TRACK = 8,
    VALIDFLAGS = 0x7f
}

Using the code above, you will able to update the system data in this way:

try
{
    EnterSystemAPI();
   //run update system data command
}
finally
{
    LeaveSystemAPI();
}