Walkthrough: Creating an Occasionally Connected Application

Occasionally connected applications are applications that might not always have access to remote data. Because they do not always have this access, they consume data from a local database located on the client and periodically synchronize the data between the remote database and the local database. The synchronization of data between local and remote databases is facilitated by Microsoft Synchronization Services for ADO.NET (the Microsoft.Synchronization.Data namespace). This synchronization is configured in the Configure Data Synchronization dialog box.

In addition to the Configure Data Synchronization dialog box, you can also use the Data Source Configuration Wizard to configure synchronization. During configuration of a typed dataset, select the option to Enable local database caching on the Choose Your Database Objects page of the wizard. For more information, see Walkthrough: Creating an Occasionally Connected Application by Using the Data Source Configuration Wizard.

This walkthrough provides step-by-step directions for developing an occasionally connected application.

During this walkthrough, you will perform the following tasks:

• Create a new Windows Forms application.

• Add a new Local Database Cache to the project.

• Configure synchronization settings that perform the following tasks:

  • Set up the data connections to the server database.

  • Configure synchronization to create a new local database.

  • Select the tables in the database that you want to synchronize with your application.

• Add a DataGridView control to the form to display data from the local database.

• Add code to initiate the synchronization between databases.

• Add a message box to display information about the number of records synchronized.

Prerequisites

To complete this walkthrough, you need the following:

• Access to the SQL Server version of the Northwind sample database. For more information, see How to: Install Sample Databases.

• SQL Server Compact 3.5 installed on the computer running Visual Studio.

Creating the Windows Forms Application

Because you will be displaying the data on a Windows Form (to verify that synchronization is successful), the first step in this walkthrough is to create a new Windows Forms application.

To create the new Windows Forms application

1. From the File menu, create a new project.

2. Name the project OCSWalkthrough.

   Note

   The Configure Data Synchronization dialog box is supported in Visual Basic and C# projects, so create the new project in one of these languages.

3. Click the Windows Forms Application template and then click OK. For more information, see Creating Windows-Based Applications.

   The OCSWalkthrough project is created and added to Solution Explorer.

Adding a Synchronization File to the Project

The next step in creating an occasionally connected application is to add (or create) a local database on the client. To add a local database that you will be able to synchronize with the remote database, you add a Local Database Cache template to your project. You add Local Database Cache files (.sync files) to projects by using the Add New Item dialog box.

To add a synchronization settings file to a project

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

2. Click the Local Database Cache template and type NorthwindCache.sync in the Name box.

3. Click Add.

   A NorthwindCache.sync file is added to Solution Explorer and the Configure Data Synchronization dialog box appears.

Configuring Data Synchronization

Configuring data synchronization consists of the following tasks:

• Provide a data connection to the remote database. This is the server connection.

• Provide a data connection to the local database or create a new database on the client, as shown in this walkthrough. This is the client connection.

• Select the tables from the remote connection that you want to use in your application. These are called cached tables.

• For each table that you add to the local database, select the columns that are required for reconciling changes and the table in which to store deleted items. Each table requires specific columns that keep track of new and modified records, in addition to a table that keeps track of records that have been deleted.

Setting Up the Data Connections

You will now set the server connection to connect to the remote database (the database external to your application). The client connection can connect to an existing SQL Server Compact 3.5 database, or you can let the Configure Data Synchronization dialog box create a new local database for you. For this walkthrough, you will create a connection to the server database and leave the default client connection to automatically create a new client database (Northwind.sdf) in the project.

To set the data connections for the server and client databases

1. Select a Server connection to the SQL Server version of the Northwind database, or click New to create a new connection to the SQL Server version of the Northwind database. For more information, see How to: Create a Data Connection to the Northwind Database.

2. Leave the default value of Northwind.sdf (new) for the Client connection. This creates a new SQL Server Compact 3.5 database and adds it to your project.

   After you select the server connection, the Configure Data Synchronization dialog box queries the remote database on the server for a list of available tables and enables the Add button after the list is retrieved.

   Note

   Notice that the OK button is not enabled. This is because no tables have been selected for synchronization. The next section explains how to add tables, which enables the OK button.

Selecting and Configuring the Tables You Want to Use Offline

You will now select the tables to add to the client database and synchronize with your application. For each table you add to the client database, select the tracking columns required for reconciling changes and the table to store deleted items. If you leave the default settings, the Configure Data Synchronization dialog box will create the tracking columns and deleted-items table. You will use the default settings in this walkthrough.

To configure tables for local caching

1. Click Add to open the Configure Tables for Offline Use dialog box.

2. Select and check the Customers table.

3. Leave all the default values. Click OK.

   The Customers table is added to the Cached Tables list.

4. In the Configure Data Synchronization dialog box, click OK.

   The tracking columns and the table for deleted items are created on the server. The Northwind.sdf database is created in the project and synchronized for the first time.

5. Select and check the Customers table in the Data Source Configuration Wizard and then click Finish.

   NorthwindDataSet.xsd is added to the project and all dialog boxes close.

Enabling Synchronization in Your Application

After you complete the previous steps, data synchronization is configured. At this point your application contains the following:

• The local Northwind database (Northwind.sdf).

• The data synchronization settings information (NorthwindCache.sync).

• The typed dataset NorthwindDataSet.xsd generated by the Data Source Configuration wizard. It contains a Customers table that populates from the local database.

• A SQLScripts folder that contains the scripts that are used to create the tracking columns, deleted items table, and necessary triggers that track changes on the server.

• A SQLUndoScripts folder that contains scripts to remove the tracking columns, deleted items table, and necessary triggers that were added to the server.

  Note

  SQL scripts are created only if modifications need to be made on the server. If the server already has a deleted-items table and tracking columns, no scripts will be created.

• References to the following Microsoft Synchronization Services for ADO.NET DLLs:

  • Microsoft.Synchronization.Data

  • Microsoft.Synchronization.Data.Server

  • Microsoft.Synchronization.Data.SqlServerCe

After you configure data synchronization, you must still add synchronization functionality to your application. More specifically, you need to add code that initiates the synchronization process.

First, you will add a DataGridView control by dragging the Customers node from the Data Sources window to the form. The DataGridView will display the Customers table from the local database cache (the Northwind.sdf database located in the project) so that you can verify that the data is synchronizing between the local and remote databases. You will also add a button to start the synchronization process.

To create a data-bound form to initiate and verify data synchronization

1. Drag the Customers node from the Data Sources window onto Form1.

2. Drag a Button control from the Toolbox onto Form1. Set its Name property to SynchronizeButton and its Text property to Synchronize Now.

3. Double-click the Synchronize Now button to create a button-click event handler and open the form in the Code Editor.

4. Add code to start the synchronization process and then refill the Customers table in the dataset. The code in the event handler should resemble the following:

   Note

   The line of code declaring the syncStats variable is explained in the next section.

   ' Call the Synchronize method to synchronize
   ' data between local and remote databases.
   Dim syncAgent As NorthwindCacheSyncAgent = New NorthwindCacheSyncAgent()
   Dim syncStats As Microsoft.Synchronization.Data.SyncStatistics =
       syncAgent.Synchronize()
   
   ' After synchronizing the data, refill the
   ' table in the dataset.
   Me.CustomersTableAdapter.Fill(NorthwindDataSet.Customers)
   

   // Call the Synchronize method to synchronize
   // data between local and remote databases.
   NorthwindCacheSyncAgent syncAgent = new NorthwindCacheSyncAgent();
   Microsoft.Synchronization.Data.SyncStatistics syncStats;
   syncStats = syncAgent.Synchronize();
   // After synchronizing the data, refill the
   // table in the dataset.
   this.customersTableAdapter.Fill(this.northwindDataSet.Customers);
   

Testing the Application

To test the application

1. Press F5.

2. With the application running, use Server Explorer/Database Explorer (or another database management tool) to connect to the remote server database and modify some records.

   1. In Server Explorer/Database Explorer, locate the Customers table on the remote database server (not the connection to Northwind.sdf).

   2. Right-click the Customers table and click Show Table Data.

   3. Modify one or more records and commit the change. (Navigate off the modified row.)

3. Go back to the form and click Synchronize Now.

4. Verify that the modifications to the remote database are synchronized to the local database and displayed in the grid.

5. Close the form. (Stop debugging.)

Retrieving Information from a Synchronization

When you call the Synchronize method, it does more than just initiate the synchronization process. The Synchronize method also returns a SyncStatistics object from which you can access information about the synchronization.

To access synchronization statistics

• Open Form1 in the Code Editor and add the following code at the bottom of the SynchronizeButton_Click event handler, below the code added in the previous steps:

          MessageBox.Show("Changes downloaded: " &
  syncStats.TotalChangesDownloaded.ToString)
  

          MessageBox.Show("Changes downloaded: " +
  syncStats.TotalChangesDownloaded.ToString());
  

Testing the Application

To test the application

1. Press F5.

2. With the application running, use Server Explorer/Database Explorer (or another database management tool) to connect to the remote server database and modify some records.

   1. In Server Explorer/Database Explorer, locate the Customers table on the remote database server (not the connection to Northwind.sdf).

   2. Right click the Customers table and click Show Table Data.

   3. Modify one or more records and commit the change. (Navigate off the modified row.)

3. Go back to the form and click Synchronize Now.

4. A message box containing information about synchronized records appears.

5. Verify that the modifications to the remote database are synchronized to the local database and displayed in the grid.

Next Steps

Depending on your application requirements, there are several steps that you may want to perform after you configure a local database cache in an application. For example, you could make the following enhancements to this application:

• Implement bidirectional synchronization so that changes in the local database cache can be uploaded to the server. For more information, see How to: Configure a Local and Remote Database for Bidirectional Synchronization.

• Deploy the client application together with the local database cache. For more information, see Walkthrough: Deploying an Occasionally Connected Client Application with the Local Database.

See Also

Tasks

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

Concepts

Occasionally Connected Applications Overview

SQL Server Compact 3.5 and Visual Studio