How to: Initialize the Client Database and Work with Table Schema
This describes how to initialize a SQL Server Compact client database and work with schema in that database. The examples in this topic focus on the following Sync Framework types and events:
For information about how to run sample code, see "Example Applications in the How to Topics" in Programming Common Client and Server Synchronization Tasks.
Understanding Initialization
Initializing the client database typically involves copying user schema and data to the database, and enabling change tracking on the database. (Change tracking is not enabled for snapshot synchronization.) For more information, see Architecture and Classes for Client and Server Synchronization. By default, the user schema and data are copied from the server database by the server synchronization provider and then applied to the client database by the client synchronization provider. Even in upload-only scenarios, by default, the schema is created at the client. You can also create the client database manually and specify that the tables in the database should be retained when synchronization occurs.
By default, the following constraints are not copied to the client: FOREIGN KEY constraints, UNIQUE constraints, and DEFAULT constraints. As shown in the example, you can add these constraints if the application requires them. Additionally, some column types are handled differently at the client than on the server. For more information, see Data Type Mapping and Considerations.
After the schema is initialized in the client database, the initial data for tables that are participating in snapshot, download-only, and bidirectional synchronization is downloaded by using the query that is specified for the SelectIncrementalInsertsCommand property. The client database is also assigned a client ID. This ID is a GUID that is exposed through the ClientId property, and it uniquely identifies the client to the server. After the client database is created, you can package a copy of the database that has the application as part of your deployment. The client ID is regenerated at each client during the first synchronization.
Example
By default, initializing the client database requires no additional code. For example, see the code in How to: Download a Snapshot of Data to a Client. However, the API provides lots of flexibility. The following example demonstrates four ways to initialize and work with table schema in the client database:
The Customer table is initialized by calling a method on the Utility class. This method creates the schema on the client by using SQL. The Utility class is available in Utility Class for Database Provider How-to Topics. A SalesNotes column that is not present at the server is added to the table. This column can be used at the client, but the data will not be synchronized. When a SyncTable is defined for the Customer table, a value of UseExistingTableOrFail is specified for the CreationOption property. This ensures that the table is not overwritten during the first synchronization.
The CustomerContact table is initialized by default. For this table and the next two tables, a value of DropExistingOrCreateNewTable is specified.
The OrderHeader table is initialized by passing a dataset to the #ctor(DataSet) constructor. The dataset is created by calling a method on the Utility class.
The OrderDetail table is initialized by adding the table and its columns to the SyncSchema object that was created by using the OrderHeader dataset. As shown in the code, you have lots of control over how the schema is created at the client. Sync Framework enables you to specify the full range of schema-related properties that ADO.NET exposes.
The code also shows two SqlCeClientSyncProvider events that are associated with schema creation:
CreatingSchema. This event is raised before the schema is created. This event is used to change the schema by using the API.
SchemaCreated. This event is raised after the schema is created. This event is used to change the schema by using SQL.