Walkthrough: Creating an Occasionally Connected Application by Using the Data Source Configuration Wizard

Starting in Visual Studio 2008 SP1, in addition to the Configure Data Synchronization dialog box, you can also use the Data Source Configuration Wizard to configure synchronization. This walkthrough provides step-by-step directions for developing an occasionally connected application by incorporating Synchronization Services when you run the Data Source Configuration Wizard.

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 that is 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). You initially create this synchronization by either adding a local database cache to an application by using the Add New Item dialog box, or by running the Data Source Configuration Wizard. In this walkthrough, you will do the latter.

During this walkthrough, you will perform the following tasks:

• Create a new Windows Forms application.

• Run the Data Source Configuration Wizard to create and configure a typed dataset that has local database caching.

• Create a Windows Form to display the data and confirm synchronization.

• 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 OCSWalkthrough2.

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

   The OCSWalkthrough2 project is created and added to Solution Explorer.

Creating a Data Source That Includes a Local Database Cache

Because this walkthrough demonstrates incorporating Synchronization Services into an application by using the Data Source Configuration Wizard, the next step is to start the wizard. This step starts the wizard and creates a dataset that is based on the Customers tables in the Northwind sample database. When you choose the database table to add to the dataset, you will select the option to enable local data caching.

To create a data source that includes a local database cache

1. On the Data menu, click Show Data Sources.

2. In the Data Sources window, click Add New Data Source to start the Data Source Configuration Wizard.

3. Leave the default value of Database selected on the Choose a Data Source Type page, and then click Next.

4. On the Choose Your Data Connection page, perform one of the following steps:

   • If a data connection to the SQL Server version of the Northwind sample database is available in the drop-down list, select it.

     -or-

   • Click New Connection to open the Choose Data Source or Add/Modify Connection dialog box and create a new connection to the SQL Server version of the Northwind database. For more information, see Add/Modify Connection Dialog Box (General).

5. If the database requires a password, select the option to include sensitive data, and then click Next.

6. Click Next on the Save the Connection String to the Application Configuration File page.

7. Expand the Tables node on the Choose your Database Objects page.

8. Select the Customers table.

9. Select Enable local database caching.

10. Click Next on the Choose Your Database Objects page.

11. Select the Customers table on the Choose Tables to Cache page.

    Note

    Leave the default value of Incremental for the Synchronization Mode. Incremental mode synchronizes only changes that were made on the server since the last synchronization. Snapshot mode replaces the whole locally cached table with the current table on the server. The whole table is at first loaded into the local database cache regardless of this selection, so that you always start with a complete set of current data.

12. Click Finish.

13. If the synchronization mode for one of the locally cached tables is set to New and incremental changes after first synchronization, updates are required on the SQL Server database, and the Generate SQL Scripts dialog box opens. By default, both options in the dialog box are selected:

    • Update server for incremental changes. Select this option to automatically update the server to enable incremental synchronization immediately after closing this dialog box.

    • Save SQL scripts in the project for later use. Select this option to add the generated SQL scripts to the project so that they can be executed on the SQL Server database later. Undo scripts are also generated and added to the project.

14. Click OK.

Enabling Synchronization in the Application

After you finish running the Data Source Configuration Wizard in the previous steps, data synchronization is configured. Your application now contains the following:

• A local version of the Northwind database (Northwind.sdf).

• A data synchronization settings file (NorthwindCache.sync).

• A typed dataset, NorthwindDataSet.xsd, that is generated by the Data Source Configuration Wizard. It contains a Customers table that populates its data from the local database.

  An initial synchronization is run and the Customers table in the local database cache is synchronized with the server database. In other words, the Customers table in the local SQL Server Compact 3.5 database (Northwind.sdf) is populated with the data that is currently in the Customers table in the database on the server.

• 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 have 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

Adding Synchronization Functionality to the Application

After you configure data synchronization, you must still add synchronization functionality to your application. More specifically, you have to add code that initiates the synchronization process. You will add controls to the form to display the data and start 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 that is 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 to Form1.

2. Drag a Button control from the Toolbox to 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 that declares the syncStats variable is explained later in this document.

   ' 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 on the form.

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 end of the SynchronizeButton_Click event handler, underneath 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 that contains information about synchronized records appears.

5. Verify that the modifications to the remote database are synchronized to the local database and displayed on the form.

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

Change History