Sync Framework Tutorial - Part #5 - Some Client Provider Schema Details

In the last part (part 1, part 2, part 3, part 4) we saw how to set up the synchronization between a local SQLce database and a server database.

The SyncAgent was set up with two providers: one for the server, which had SyncAdapters to specify which tables on the server side are used and which columns hold the metadata, and another for the client, which used a local compact edition database and a number of SyncTable objects to set up the sync.

Upon synchronization, the SyncAgent downloads the schema from the server provider, creates the tables on the client if required, and transfers changes between the databases since the last synchronization.

And it works, but you will soon notice something is missing.

The Client Schema

The server provides a schema so that the client can create an identical set of tables to use on the client. But if you attempt to insert a row on the client, you will notice that the default values are missing. The primary keys are not specified, and foreign key constraints on the client are not available.

This is not required, but it would be nice to have them anyway. However, there is only one way to do it: you have to manually alter the tables after they have been created by the client sync provider.

Adapting the Client Schema After It Has Been Automatically Created

The SqlCeClientSyncProvider provides a number of events, two of which are: CreatingSchema and SchemaCreated.

In the CreatingSchema handler, you should set the primary key for each table using the CreatingSchemaEventArgs:

e.Schema.Tables[_mySchema.TableName].Columns[_mySchema.PK].RowGuid = true;

And you will also want to add foreign key constraints:

e.Schema.Tables[_mySchema.TableName].ForeignKeys.Add(
    _fkName,
    _fkSchema.ParentTable,
    _fkSchema.ParentColumn,
    _fkSchema.ChildTable,
    _fkSchema.ChildColumn
);

The second event handler, SchemaCreated, is used to run custom SQL commands inside the same transaction used by the sync provider for creating the tables, ensuring data integrity:

SqlCeCommand _cmdAlterTableDef = new SqlCeCommand
{
    Connection = (SqlCeConnection)e.Connection,
    Transaction = (SqlCeTransaction)e.Transaction,
    CommandText = _alterCmdString
};
_cmdAlterTableDef.ExecuteNonQuery();

Summary

So far we have created an application that synchronizes data between a client database and a server database and taken a look at some nice-to-have schema changes on the client.

Next up is the distributed scenario where the server provider is called via a WCF proxy. Hopefully, I can post that before Christmas.