Build Occasionally Connected Systems Using Sync Services for ADO.NET v1.0

oday, more and more organizations are mobilizing their workforce. Workers are equipped with phones, PDAs, and notebook computers while they are on the road. Often, they need to access information stored on their corporate servers?things like product information, orders, and so on. One way to obtain this information would be to connect directly to the corporate servers through VPN connections or via web services. However, doing so requires them to stay connected, which is not always possible. A better approach is to build an Occasionally Connected System (OCS) where the data required is cached on the user’s device. When the user has network connectivity, the data can then be synchronized with the backend database. This approach has many advantages, the main one being the elimination of the need to always stay connected and the ability to access important data locally.

With Visual Studio 2008, Microsoft has recently released the first version of Sync Services for ADO.NET, which enables the synchronization of databases between the different devices and computers. This article will walk you through the basics of Sync Services and how it works. You’ll also learn how to use Visual Studio 2008 to build applications using Sync Services.

Synchronization Services Architecture
Figure 1 shows the Sync Services architecture and the various objects that make it possible.

Figure 1. Sync Services Architecture: And the various objects that make it possible.
  • SyncTable: The SyncTable object represents the client tables involved in synchronization. For each table you are synchronizing, a SyncTable object is used.
  • SyncAgent: The SyncAgent object orchestrates the entire synchronization process.
  • SqlCeClientSyncProvider: The SqlCeClientSyncProvider object abstracts a SQL Compact database and shields SyncAgent from the implementations of the client database.
  • DbServerSyncProvider: The DbServerSyncProvider object abstracts a database server and shields SyncAgent from the implementations of the server database.
  • SyncAdapter: The SyncAdapter object represents a set of data commands that is used to retrieve and apply changes at the database server. For each table you are synchronizing, a SyncAdapter object is used.
  • ServerSyncProviderProxy: The ServerSyncProviderProxy object is used in N-tiered scenarios. Instead of SyncAgent communicating directly with the DbServerSyncProvider object, the SyncAgent object uses the ServerSyncProviderProxy object to relay calls to the DbServerSyncProvider deployed on another server.

In a two-tier environment, all the above objects run on the same process or node. In an N-tier environment, the DbServerSyncProvider and SyncAdapter objects are deployed on a separate server from the client.

Sync Services uses database triggers and additional columns in tables to track changes in each database. During synchronization, this information is is used to synchronize both client and server.

Using Sync Services with Visual Studio 2008
Prior to Visual Studio 2008, using Sync Services involved writing large amounts of code and lots of database preparation. Fortunately, Visual Studio 2008 ships with the Sync Designer for Sync Services, which lets you use Sync services without writing much code.

First, create a Windows Forms application using Visual Studio 2008 and name the project SyncServices.

Add a new item to the project by right-clicking on the project name in Solution Explorer and then selecting Add?>New Item…. Select the Local Database Cache template and name it LocalAuthors.sync.

After you add the item to the project, the Configure Data Synchronization wizard will appear.

First, select the connection to the SQL Server database that you want to use. For this article, you’ll use the pubs sample database installed on the default instance of the SQL Server 2005 Express database (assuming you already have the database). Once you’ve selected the database, the pubs.sdf database is automatically selected for you. The pubs.sdf database is the SQL Compact 3.5 database that you will use locally on the client. It’s a cached copy of the pubs table on the database server.

Next, click the Add button to select the tables you want to use in your application. The Configure Tables for Offline Use dialog will now appear (see Figure 2).


Figure 2. Configure Tables for Offline Use: This dialog allows you to select the tables you want to use in your application.
 
Figure 3. The Configure Data Synchronization Wizard: Note the options shown on the right of the dialog window.

For simplicity, select the authors table. Note the options shown on the right of the dialog window. These options show the various options in which synchronization will occur when the data on the server and client changes. Click OK. Then, back in the Configure Data Synchronization dialog, click OK again (see Figure 3).

The Generate SQL Scripts dialog will now appear. Click OK. A set of SQL scripts will now be generated and executed to modify the databases for synchronization. Next, the Data Source Configuration Wizard dialog will appear.

Expand the Tables item and check the authors table (see Figure 4). This will create a strongly typed Dataset that you can use in your application later on. Click Finish.


Figure 4. The Data Source Configuration Wizard: Check the table you want to use.
 
Figure 5. Solution Explorer: New items added to the project.

If you look at the Solution Explorer now, you will see that a number of items have been added to your project (see Figure 5).

These items are:

    Figure 6. Database Changes: Observe in Server Explorer the databases created/modified.
  • Various Library References: Microsoft.Synchronization.Data, Microsoft.Synchronization.Data.Server, and Microsoft.Synchronization.Data.SqlServerCe.
  • Two SQL Scripts: LocalAuthors.dbo.authors.sql modifies the pubs database to track changes, while the LocalAuthors.dbo.authors.undo.sql script undoes the changes.
  • LocalAuthors.Sync and LocalAuthors.Designer.cs: These files contain the code to perform the actual synchronizations.
  • pubsDataSet.xsd: This strongly typed Dataset is created for use in the application.

At this point, you can see the created or modified databases in Server Explorer (Figure 6).

The newly created pubs.sdf database contains all the fields that you selected earlier. In addition, it has two tracking columns: LastEditDate and CreationDate. For the server database, notice that the authors table also has two additional columns: LastEditDate and CreationDate. It also has three triggers added: authors_DeletionTrigger, authors_InsertTrigger, and authors_UpdateTrigger.

One additional table is added to the server: authors_Tombstone. This is for tracking deletions in the authors table.

Now, bind the pubs.sdf database to some controls on the Windows form. When you run the form, you’ll make some changes to the data, and then synchronize those changes with the server.

Syncing the Database
Select Data?>Show Data Sources and drag-and-drop the authors table onto Form1 (see Figure 7).Drag-and-drop a Button control onto the form, as well.


Figure 7. Form1: The results of the synchronization process.
 
Figure 8. The Synchronize() Method: The image shows the results of the synchronization process.

Double-click on the Synchronize button and code it as follows:

        private void btnSynchronize_Click(object sender, EventArgs e)        {            //---sync with the database---            LocalAuthorsSyncAgent syncAgent = new LocalAuthorsSyncAgent();            Microsoft.Synchronization.Data.SyncStatistics syncStats =                 syncAgent.Synchronize();            //---reload the form to refresh the data---            this.Form1_Load(null, null);        }

What you are doing here is creating an instance of the LocalAuthorsSyncAgent class (defined in the LocalAuthors.Designer.cs file) and then calling its Synchronize() method to perform the synchronization. The Synchronize() method returns a result of type Microsoft.Synchronization.Data.SyncStatistics. You can set a breakpoint here to examine the results returned by the Synchronize() method (see Figure 8).

Right-click on the LocalAuthors.sync file and select View Code. Add the code shown below in bold type:

namespace SyncServices {      public partial class LocalAuthorsSyncAgent {        partial void OnInitialized(){            this.authors.SyncDirection =                 Microsoft.Synchronization.Data.SyncDirection.Bidirectional;        }    }}

Here, you are setting the synchronization direction to Bidirectional. Possible values are: Bidrectional, DownloadOnly (default), Snapshot, and UploadOnly. By setting the synchronization direction to Bidirectional, all modifications to the databases (server and client) will be synchronized.

That’s it! Press F5 to test the application. You can make changes to the records in the DataGridView control and then save the changes. The changes are then saved to the local pubs.sdf database. To synchronize the changes with the server, click the Synchronize button (see Figure 9).


Figure 9. Testing the Application: To synchronize the changes with the server, click the Synchronize button.
 
Figure 10. The Updated Record: Right-click on the table name in Server Explorer and select Show Table Data.

Stop the application and you can now examine the server database. Right-click on the table name (authors) in Server Explorer and then select Show Table Data (see Figure 10). You will now be able to see the updated record.

Using Sync Services with Windows Communication Foundation
The previous example shows you how to use Sync Services to generate all the synchronization logic on the client side. In the real world, you might want to use a multi-tier architecture and host part of the synchronization logic on another server.

In this section, you’ll modify the previous application and host the synchronization logic using a WCF service. First, add a new project to the Solution. Select the WCF Service Library template and name it WcfSyncService.

Back in the SyncServices project, double-click on the LocalAuthors.sync file to bring up the Configure Data Synchronization dialog again. In the Server project location dropdown listbox, select the WcfSyncService project (see Figure 11). This creates the synchronization logic in two different projects: the SyncServices project for the client component, and WcfSyncService for the server component. Click OK.


Figure 11. Select the WcfSyncService Project: Regenerating the synchronization logic for the server for the WCF service.
 
Figure 12. Three New Files: The WcfSyncService project now has three additional files added.

Notice that, in Solution Explorer, the WcfSyncService project will now have three additional files (see Figure 12).

Double-clicking on the LocalAuthors.Server.sync file will redirect you to the LocalAuthors.sync file in the SyncServices project. The LocalAuthors.Server.SyncContract.cs file contains the service contract for the WCF service.In the LocalAuthors.Server.SyncContract.cs file, change the class name to Service1, as shown in bold below:

namespace WcfSyncService {    using System;    using System.Data;    using System.Collections.ObjectModel;    using System.ServiceModel;    using Microsoft.Synchronization.Data;            public partial class Service1 : object, ILocalAuthorsSyncContract {                private LocalAuthorsServerSyncProvider _serverSyncProvider;        public Service1()        {            this._serverSyncProvider = new LocalAuthorsServerSyncProvider();        }        ...

In the Service1.cs file, add the partial keyword to the definition of the Service1 class (see Listing 1).

With the changes made to the LocalAuthors.Server.SyncContract.cs and the Service1.cs files, you are essentially combining the two Service1 partial classes into one.

In the LocalAuthors.Server.SyncContract.cs file, locate the endpoint address and copy it into memory:

  

Paste the endpoint address into app.config and delete the element, as shown in Figure 13. This will define an endpoint for your WCF service.


Figure 13. Define an Endpoint: Paste in the endpoint address into and delete the element.
 
Figure 14. Testing the Application: Copying the working URL of the WCF service.

The next step is to set the WcfSyncService project as the startup project. Then press F5 to obtain its working URL. When the WCF Test Client has loaded, right-click on the URL of the service and select Copy Address (see Figure 14).

You can now add a service reference to the SyncServices project so that your application can connect to this service to synchronize its data. Right-click on the References item in the SyncServices project in Solution Explorer and select Add Service Reference.

Next, paste the URL you have copied earlier into the Address textbox and click Go. Use the default name of ServiceReference1 and click OK.

The last step is to right-click on the LocalAuthors.sync file and select View Code. Add the code shown in bold type below:

using Microsoft.Synchronization.Data;namespace SyncServices{    public partial class LocalAuthorsSyncAgent    {        partial void OnInitialized()        {            this.authors.SyncDirection =                Microsoft.Synchronization.Data.SyncDirection.Bidirectional;            this.RemoteProvider = new ServerSyncProviderProxy(                new ServiceReference1.LocalAuthorsSyncContractClient());        }    }}

The additions specify the remote provider of the synchronization services.

That’s it! To test the application, set the SyncServices project as the startup project and press F5. You will now be able to synchronize your databases using a WCF service.

Easy as 1-2-3
You have seen the architecture of Sync Services and the various controls that make Sync Services possible. With Visual Studio 2008, using Sync Services is now much easier as most of the code is generated for you. While this article focuses on using Visual Studio 2008 to generate the code, you might want to examine the code generated in more detail so that you have a better understanding of how the various Sync Services objects work.

Share the Post:
Share on facebook
Share on twitter
Share on linkedin

Overview

The Latest

microsoft careers

Top Careers at Microsoft

Microsoft has gained its position as one of the top companies in the world, and Microsoft careers are flourishing. This multinational company is efficiently developing popular software and computers with other consumer electronics. It is a dream come true for so many people to acquire a high paid, high-prestige job

your company's audio

4 Areas of Your Company Where Your Audio Really Matters

Your company probably relies on audio more than you realize. Whether you’re creating a spoken text message to a colleague or giving a speech, you want your audio to shine. Otherwise, you could cause avoidable friction points and potentially hurt your brand reputation. For example, let’s say you create a

chrome os developer mode

How to Turn on Chrome OS Developer Mode

Google’s Chrome OS is a popular operating system that is widely used on Chromebooks and other devices. While it is designed to be simple and user-friendly, there are times when users may want to access additional features and functionality. One way to do this is by turning on Chrome OS