How to: Create Views of Data on a Client
This topic shows you how to use Sync Framework to combine two or more server tables into a single table in the client 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.
Example
The following code examples show how to synchronize the Customer and CustomerContact tables in the Sync Framework sample database. During the initial synchronization, data from the two tables is selected by using the incremental inserts command, downloaded to the client, and then inserted into the CustomerInfo table. During the subsequent synchronization, qualifying changes are downloaded to the client.
Key Parts of the API
This section provides code examples that point out the key parts of the API to use when you are combining tables. The following code examples specify SyncTable and SyncAdapter objects that both use the name CustomerInfo. Specifying the name in both places enables the SyncAgent and providers to translate selects from the Customer and CustomerContact tables into inserts for the CustomerInfo table.
The following code example includes the query for the SelectIncrementalInsertsCommand property. The query uses a join to ensure that only customers who have contact information are downloaded to the client during the initial synchronization. For subsequent synchronizations, inserts to the CustomerContact table are downloaded; inserts to the Customer table are downloaded only if a row for the customer is also inserted into the CustomerContact table. Notice that the select list does not include all columns. As with all the queries that are used in synchronization, the logic of each query depends on the requirements of the application. For example, you could download all customers, even if they do not have contact information.
The following code example includes the query for the SelectIncrementalDeletesCommand property. The query uses two SELECT statements and a UNION to select deletes for the Customer and CustomerContact tables. The first SELECT statement joins with the CustomerContact table to retrieve the PhoneType column. This column is part of the composite key for the CustomerInfo table. The logic is as follows:
If a customer was deleted, all the rows for that customer are deleted at the client.
If contact information was deleted, only that row is deleted at the client.
If a customer row and contact information for that customer were both deleted, at least one extra delete is downloaded. This will not cause any errors when changes are applied at the client database.
The following code example creates a composite primary key for the CustomerInfo table. This key matches the key for the CustomerContact table. Sync Framework can infer the schema from the tables at the server, but the key must be specified in this case. You can also manually create the schema, as described in How to: Initialize the Client Database and Work with Table Schema.
Complete Code Example
The following complete code example includes the code examples that are described earlier and additional code to perform synchronization. The example requires the Utility class that is available in Utility Class for Database Provider How-to Topics. Notice that the insert into the Customer table in the Utility class is not downloaded on the subsequent synchronization because there is no corresponding row in the CustomerContact table.