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 an example that uses real data, see the Walkthrough: Creating an Occasionally Connected Application topic.
.gif "Note") 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:
Dim syncAgent As LocalDataCache1SyncAgent = New LocalDataCache1SyncAgent()
Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics = _
syncAgent.Synchronize()
' Add code to refill your application's data source
' with the updated data from the local database.
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.