Sync Framework Tutorial – Part #5 – Some client provider schema details

(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 the last part (part 1, part 2, part 3, part 4) we saw how to setup the synchronization between a local SQLce database and a server database.

The SyncAgent was setup with two providers, one for the server which had SyncAdapters to specify which tables on the server side are used, which columns hold the metadata. Another for the client that used a local compact edition database and a number of SyncTable objects to setup 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. But 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 for 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();

_cmdAlterTableDef.Connection = (SqlCeConnection)e.Connection;

_cmdAlterTableDef.Transaction = (SqlCeTransaction)e.Transaction;

_cmdAlterTableDef.CommandText = _alterCmdString;

_cmdAlterTableDef.ExecuteNonQuery();

Summary

So far we have created an application that synchronizes data between a client database and a server database and done 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.