(shameless plug at the beginning. Who am I? A Microsoft Student Partner from Austria on his quest to keep up with an ever expanding universe of must-know technologies and working on taking the next step as an intern at Microsoft… A friendly hello to any Microsoft team members reading here, check out the about page. Sorry, for that – life’s all about marketing – now back to the content…)

In this part of the tutorial (part 1, part 2, part 3) we will take a look at a fully functional client application that uses the sync framework to synchronize to the server database. It requires the client application to directly connect to the database. In the next part we will see how to connect via a service layer.

The solution


Any sync framework solution will need at least a sync agent and two sync providers. The sync agent takes care of setting up the providers with the sync agent and configures the options. The providers are database providers. The client is a special SQLce version and the Server provider is the standard DbProvider. When the application wishes to synchronize its data, it calls the Synchronize method on the SyncAgent and it will take care of the rest.

Our goal is to synchronize data changes between server and client regardless of who makes the changes. This functionality is invoked manually from the client.


// Providers
ActionClientSyncProvider _clientSyncProv = ActionClientSyncProvider.Setup();
ActionServerSyncProvider _serverSyncProv = ActionServerSyncProvider.Setup();
// Sync Agent
this.LocalProvider = _clientSyncProv;
this.RemoteProvider = _serverSyncProv;
// Tables
SyncTable _syncTableAction;
SyncTable _syncTableProject;
SyncTable _syncTableUser;
SyncGroup _syncGroupMain = new SyncGroup("AllSync");
_syncTableAction = CreateStandardSyncTable("Action", _syncGroupMain);
_syncTableProject = CreateStandardSyncTable("Project", _syncGroupMain);
_syncTableUser = CreateStandardSyncTable("User",_syncGroupMain);
  new[] { _syncTableAction, _syncTableProject, _syncTableUser }); 

Start by creating an instance on ActionClientSyncProvider and ActionServerSyncProvider which have both been setup (details on those later). Both are attached to the respective provider properties on the sync agent.

Next up we specify which tables we want to synchronize. We create a sync table for each table and attach them to the main sync group (to ensure foreign key constraint are handled correctly) using the CreateStandardSyncTable method:

SyncTable _syncTable;
_syncTable = new SyncTable(tableName);
_syncTable.CreationOption = 
_syncTable.SyncTableTransferOption = 
_syncTable.SyncGroup = _syncGroupMain;
return _syncTable;

We specify the CreationOption property as DropExistingOrCreateNewTable from the TableCreationOption enumeration.

The available options are:

CreateNewTableOrFail: creates the table on the client and throws an exception if it exists.

DropExistingOrCreateNewTable: creates the table but drops any existing tables with the same name.

TruncateExistingOrCreateNewTable: creates the table if it doesn’t exist. If it exists is removes all rows on the client table.

UploadExistingOrCreateNewTable: creates the table if it doesn’t exist. If it exists it will upload all rows from the table.

UseExistingTableOrFail: Throws an exception it the client table does not exist.

The SyncTableTransferOption specifies in which direction data can flow. There are four options: UploadOnly, DownloadOnly and Bidirectional are similar. They download a schema and then submit changed data in one or both directions. The fourth option is Snapshot which downloads the set from the server on every synchronization and replaces the client data.

The last call in the SyncAgent’s Setup method is a call to the server provider to set up it’s SyncAdapters using the SyncTables. SyncTable objects are not directly related to SyncAdapter objects but in this case the SyncAdapters use tables that share the same name as the sync tables. For this reason they are passed along:

Setting up SyncAdapters

public void SetupSyncTableAdapters(SyncTable[] syncTables)
    foreach (SyncTable _syncTable in syncTables)
              SyncUtil.CreateStandardSyncAdatper(this, _syncTable));

The SyncUtil.CreateStandardSyncAdapter method handles the details. They are similar for all my tables therefore extracted into a utility method.



public static SyncAdapter CreateStandardSyncAdatper
  (ActionServerSyncProvider prov, SyncTable syncTable)
    SyncAdapter _syncAdapterAction = 
      SyncUtil.CreateStandardBuilder(prov, syncTable.TableName).ToSyncAdapter();
    _syncAdapterAction.TableName = syncTable.TableName;
    return _syncAdapterAction;
public static SqlSyncAdapterBuilder CreateStandardBuilder
  (ActionServerSyncProvider prov, string tableName)
    SqlSyncAdapterBuilder _builder = 
       new SqlSyncAdapterBuilder(prov.Connection as SqlConnection); 
    _builder.TableName = tableName;
    _builder.TombstoneTableName = _builder.TableName + "_Archive";
    _builder.SyncTableTransferOption = SyncTableTransferOption.Bidirectional;
    _builder.CreationTrackingColumn = "InsertTimestamp";
    _builder.UpdateTrackingColumn = "UpdateTimestamp";
    _builder.DeletionTrackingColumn = "DeleteTimestamp";
    _builder.CreationOriginatorIdColumn = "InsertID";
    _builder.UpdateOriginatorIdColumn = "UpdateID";
    _builder.DeletionOriginatorIdColumn = "DeleteID";
    return _builder;

The SyncAdapter is created using a builder class. The class (SqlSyncAdapterBuilder) requires the connection to the database.

Be aware: The connection property on the provider is inherited from the base class (DbServerSyncProvider) and is of type IDbConnection. But the SqlSyncAdapterBuilder expects a SqlConnection. We are inferring that the property will return a SqlConnection but I consider this bad style and usually this would be a warning light for a design problem. But since this is only an example I’ll just continue.

The builder properties specify the names of the columns, of the table and the option for transferring which needs to correspond to the SyncTables.

The client provider

The client provider basically just inherits from the SqlCeClientSyncProvider and provides a connection string to the local SQLce database file. Well not quite, but that’s a part of the next part of the tutorial.