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.
Note
The Configure Data Synchronization dialog box provides the ability to configure Microsoft Synchronization Services for ADO.NET for download scenarios only. This means that after you configure data synchronization by using the Configure Data Synchronization dialog box, calling Microsoft.Synchronization.Data.SyncAgent.Synchronize will only update the local database with changes found on the remote database. Changes made to the data on the local database will not be uploaded to the remote database. After you configure data synchronization by using the Configure Data Synchronization dialog box, you can programmatically enable uploads (bi-directional synchronization) during synchronization. For more information, see the How to: Configure a Local and Remote Database for Bidirectional Synchronization.
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.
Note
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Visual Studio Settings.
To configure data synchronization
On the Project menu, click Add New Item.
Click the Local Database Cache template.
Either provide an alternative name or keep the default name of LocalDataCache1.sync.
Click Add.
The .sync file is added to the project and the Configure Data Synchronization dialog box opens.
Set the Server connection to the remote database that you want to connect to.
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.
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.
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.
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.
Click OK.
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.
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.
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.
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.
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.
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.
Note
Synchronizing data updates the local database, not the table in the dataset or any other object in your application. Remember to reload your application data source with the updated data from the local database. For example, call the TableAdapter.Fill method to load your dataset's data table with the updated data from the local database.
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)