How to: Configure Data Synchronization in an Application

If your application requires data from a remote database that does not have to be continuously retrieved from the database (or that is not always available), you can use a local database to store the data on the client computer with your application.

For example, consider an inventory application that uses data from several tables in a database. The number of items in stock for any individual part is an important piece of data that continually changes, so the application should always reflect the current values in the database. However, the application also displays a list of valid shipping companies that rarely changes. These valid shipping companies are stored in the Shippers table and do not have to be retrieved every time data is queried from the database. If you store this Shippers table in a local database cache, you can decrease the number of unnecessary roundtrips your application has to make to the remote database. Consider storing data that changes infrequently (or that changes on a known schedule) in a local database cache.

The local database cache uses a SQL Server Compact 3.5 database to store data locally. You can use an existing SQL Server Compact 3.5 database as the local database cache. If you do not yet have a local database, you can set the Configure Data Synchronization dialog box to create a new local database.

After you add a local database to your application and complete the Configure Data Synchronization dialog box, you still have to add code to your application to initiate the synchronization. After you successfully synchronize the data, you must also add code to refill the table in the dataset from the local database.

Adding a Local Database Cache to a Project

You can configure applications to cache data locally by adding a .sync file to your project and configuring it by using the Configure Data Synchronization dialog box. You can add .sync files to projects by using the Add New Item Dialog Box.

The following procedure provides the basic steps involved in configuring data synchronization. For examples that use real data, see the Walkthrough: Creating an Occasionally Connected Application.

To configure data synchronization

1. On the Project menu, click Add New Item.

2. Click the Local Database Cache template.

3. Either provide an alternative name or keep the default name of LocalDataCache1.sync.

4. Click Add.

   The .sync file is added to the project and the Configure Data Synchronization dialog box opens.

5. Set the Server connection to the remote database that you want to connect to.

6. Set the Client connection to the local SQL Server Compact 3.5 database that will store your data locally. If you do not have a local database, you can leave the default setting of DatabaseName.sdf (new) to create a new database in the project. The name of the new database will be based on the name of the database in the Server connection.

   Note

   The OK button is disabled by default and is enabled after adding a table to the Cached Tables area.

7. Click Add to open the Configure Tables for Offline Use dialog box and select and configure the database tables to add to the local database cache.

8. Select the database tables you want to add to the local database cache, and configure each of the following settings:

   • Data to download:

     New and incremental changes after first synchronization

     This setting retrieves records from the server that have been modified since the last time data was synchronized. The first time synchronization is called, the entire table will be downloaded.

     Entire table each time

     This setting drops the local table and replaces it with the version on the server.

   • Compare updates using

     Set this to the column name in the selected table that is used to track when the last update of a record was made. By default, any column that is defined as a datetime or timestamp will appear in this list. If the table does not contain a column that is used to track modified records, you can leave the default setting of LastEditDate (new), which will create the tracking column for you.

   • Compare inserts using

     Set this to the column name in the selected table that is used to track when new records are added to the table. By default, any column that is defined as a datetime or timestamp will appear in this list. If the table does not contain a column that is used to track new records, you can leave the default setting of CreationDate (new), which will create the tracking column for you.

   • Move deleted items to

     Set this to the table on the database server that is used to store deleted records. By default, any table that is named tableName_Deleted or tableName_Tombstone will appear in this list. If the database does not contain a table for storing deleted items, you can leave the default setting of tableName**_Tombstone (new)**, which will create the deleted items table for you.

     Note

     Configure these settings for each table that you are configuring for offline use.

9. By default, the Script Generation options are automatically set to generate and run the server-side scripts that are used to configure the database server. If you do not need these scripts, or if you do not have access to the database server, you can clear either option and manually run the scripts or modify your tables. If no changes are required on the server, no scripts will be generated.

   Note

   These scripts add the tracking columns to the selected tables, create the tables for storing deleted items, and add some triggers that you must have to keep track of the Inserts, Updates, and Deletes on tables that are set up for local caching.

10. Click OK.

11. Click Show Code Example to open the Code Example dialog box, which provides a code snippet that starts the synchronization process. You can copy this snippet to the clipboard and insert into your program.

12. Optionally, set the Advanced options.

    The Advanced options on the Configure Data Synchronization dialog box provide settings that enable you to control whether tables are synchronized separately or in a single transaction. The options also enable code separation for n-tier applications.

    1. Synchronize tables in a single transaction. By default, this is not checked and all tables are synchronized individually. If errors are encountered, only tables with errors have their changes rolled back. If you check this option, all tables are synchronized in a single transaction. If errors are encountered, all changes for all tables are rolled back.

    2. Create synchronization components. By default, synchronization components are generated for both the client and the server. You can set this option to Client only or Server only to meet your application requirements.

    3. Server project location. By default, the synchronization components for the server will be generated in the current project. Set this option to generate synchronization components for the server into any Visual Basic or Visual C# project in the solution.

    4. Client project location. By default, the synchronization components for the client will be generated into the current project. Set this option to generate synchronization components for the client into any Visual Basic or Visual C# project in the solution.

After you complete the Configure Data Synchronization dialog box, you have to add code to your application to initiate the synchronization.

To initiate synchronization

• If you copied the code from the Code Example dialog box in the previous section, you can just paste it into your application. Otherwise, you can insert the following code anywhere in your application where you want to initiate the synchronization process:

  ' Call SyncAgent.Synchronize() to initiate the synchronization process.
  ' Synchronization only updates the local database, not your project's data source.
  Dim syncAgent As LocalDataCache1SyncAgent = New LocalDataCache1SyncAgent()
  Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics = syncAgent.Synchronize()
  
  ' Add code here to refill your application's data source
  ' with the updated data from the local database.
  

  // Call SyncAgent.Synchronize() to initiate the synchronization process.
  // Synchronization only updates the local database, not your project's data source.
  LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent();
  Microsoft.Synchronization.Data.SyncStatistics syncStats =
  syncAgent.Synchronize();
  
  // Add code to refill your application's data source
  // with the updated data from the local database.
  

See Also

Tasks

Walkthrough: Creating an Occasionally Connected Application

Walkthrough: Deploying an Occasionally Connected Client Application with the Local Database

Concepts

Occasionally Connected Applications Overview

Other Resources

Occasionally Connected Applications (Local Database Caching)