Getting Started with Microsoft Synchronization Services for ADO.NET

Introduction

Data synchronization for occasionally connected applications is no easy task. Smart Client applications represent a highly responsive, rich user interface, but they can lose something when it comes to managing the flow of information across a distributed system. Data synchronization is the key to allowing data to flow between clients that are connected to the Internet/network on an intermittent basis. Microsoft is planning a February 2008 release of Visual Studio 2008; it will include a new synchronization framework with support for ADO.NET. This article will introduce you to this framework using the beta version of Microsoft Synchronization Services for ADO.NET, which works to synchronize an ADO.NET-compatible server database with a SQL Server Compact Edition (version 3.5) client database. The sync framework attempts to simplify a complex problem into manageable pieces that can vary depending on the intricacy of your synchronization scenario. This article will walk you through the steps to creating your first synchronizable application. The example will show you how to configure your application to synchronize with a SQL Server 2005 database via a web service abstraction layer. It will also explain the required synchronization data points as well as how to introduce your own data parameters into the sync framework.

In this article, you will create a sync example to “manage” the favorite items of customers. The items list will be maintained at a server level (presumably by some item-inventory administrator). Therefore, updates will be pulled down from the server to the client, but the client will never push up any additions or updates to the item list. The customers and their favorite items will be managed at a client level. You only want the customers for which a particular user is responsible to be pulled down from and updated to the server (for reasons such as security, network efficiency, size on the client computer, and so forth). The form in my example does not represent a finished management application, but instead allows you to see the difference between what is available in the client computer’s local database and the server database.

Prerequisites and Setup

In this sample, I will use Visual Studio 2005, C#.NET. Microsoft SQL Server 2005 acts as the backend database server. You must have the Microsoft Synchronization Services for ADO.NET. You can download it here and install it. The package includes SQL Server Compact Edition 3.5 (which serves as the client databases) as well as the synchronization libraries for ADO.NET.

The Server Database

For this application, you will define four tables in your backend database. They are User, Customer, Item, and CustomerFavoriteItem. The database model is as follows:

Figure 1: Sync example server database model

Notice that each table has four common fields: UpdateTimestamp, InsertTimestamp, UpdateID, and InsertID. These fields are special sync services fields you must declare to track information that the clients will use to determine what should be updated in the local database. These fields are outlined here:

Table 1: Sync services table fields

For each table, you also must have a “tombstone” table. A tombstone table is a placeholder for deleted entries so that when the client syncs to the server database, it knows which records to delete in the local database. You will create a delete trigger on each table so that it will insert deleted rows into the table’s tombstone. The tombstone table generally consists of the main fields of the original table (minus the four sync services fields) as well as two tombstone-specific sync services fields. These fields are DeleteId and DeleteTimestamp. You will name your tombstone tables the same name as the original table with “_Tombstone” appended to the end (for example, Customer_Tombstone). The fields are outlined here:

Table 2: Sync services tombstone table fields

Create the delete trigger on each of the four tables. If you are using SQL Server, you can use the “Delete Trigger Script.sql” script that’s included with the source code to create the triggers.

Note: If you use the same naming conventions for the sync services special fields and the tombstone tables as is outlined here, sync services will work by default. Otherwise, you will have to do some additional configuration in your sync layer.

The Server Web Service

Your web service will be responsible for retrieving information from your database to push to the clients as well as submitting information from the clients back to the server database. To get started, create a new C# ASP.NET Web Service project in Visual Studio:

Figure 2: New C# ASP.NET Web Service project in Visual Studio 2005

I called my web service “SyncServicesSample_WebService.” You will first need to add references to the two sync services libraries that deal with server-side sync logic. They are Microsoft.Synchronization.Data.dll and Microsoft.Synchronization.Data.Server.dll, and they can be found at “C:Program FilesMicrosoft Synchronization ServicesADO.NETv1.0.” Secondly, remove the HelloWorld default web method provided by Visual Studio. I also renamed my Service class, asmx file, and CS file to SyncServicesSampleService.

Next, open web.config and add a connection string pointing to the database you just set up in SQL Server:

<connectionStrings>
   <add name="SampleServerDB" connectionString="
        Data Source=localhost;
        Initial Catalog=SyncServicesSample_ServerDB;
        Integrated Security=True"/>
</connectionStrings>

This is also a good time to make sure your web service is configured for Windows integrated security. To do so, make sure the authentication mode is set to Windows and the identity impersonation is set to true. The authentication section of your web.config should like this:

<authentication mode="Windows" />
<identity impersonate="true"/>

Finally, you are ready to start coding the meat of the web service. The web service is going to serve as a wrapper class for the Microsoft.Synchronization.Data.Server.DbServerSyncProvider class, which is responsible for updating and retrieving data from the server database./p>

private DbServerSyncProvider serverSyncProvider;

To implement a server sync provider web service, you must implement four methods that sync services uses to do the work of the synchronization. Your methods will simply pass the call on to your serverSyncProvider object. The methods are as follows:

Listing 1: Server sync provider web service methods

[WebMethod]
public SyncServerInfo GetServerInfo(SyncSession session)
{
   return serverSyncProvider.GetServerInfo(session);
}

[WebMethod]
public SyncSchema GetSchema(Collection<string> tableNames,
   SyncSession session)
{
   return serverSyncProvider.GetSchema(tableNames, session);
}

[WebMethod]
public SyncContext GetChanges(SyncGroupMetadata groupMetadata,
   SyncSession syncSession)
{
   return serverSyncProvider.GetChanges(groupMetadata,
                                        syncSession);
}

[WebMethod]
public SyncContext ApplyChanges(SyncGroupMetadata groupMetadata,
   DataSet dataSet, SyncSession syncSession)
{
   return serverSyncProvider.ApplyChanges(groupMetadata, dataSet,
      syncSession);
}

In the constructor, you will initialize the serverSyncProvider variable and set its connection property to the location of your database. I also like to add an ApplyChangeFailed event handler. Visual Studio will generate the handler for you, and you can add any logging/failure logic you would like. I like to put a breakpoint on the handler while debugging so I can tell what is going wrong. Here is the code:

Listing 2: Server sync provider initial constructor code

serverSyncProvider = new DbServerSyncProvider();

serverSyncProvider.ApplyChangeFailed += new
   EventHandler<ApplyChangeFailedEventArgs>
   (serverSyncProvider_ApplyChangeFailed);

SqlConnection serverConn = new
   SqlConnection(ConfigurationManager
.ConnectionStrings["SampleServerDB"].ConnectionString);
serverSyncProvider.Connection = serverConn;

For the next portion, I use stored procedures, but you also could use inline code. The serverSyncProvider needs an anchor command, which will return the timestamp of the last inserted or updated row that has a timestamp column. The anchor allows sync services to determine which rows need to be updated in the client. The stored procedure is simple and looks like the following:

Listing 3: New anchor stored procedure

CREATE PROCEDURE dbo.sp_NewAnchor
(
   @sync_new_received_anchor timestamp output
)
AS

SELECT @sync_new_received_anchor = @@DBTS
GO

In the constructor of your web service, you need to instruct the serverSyncProvider how to access the anchor using SqlCommand:

Listing 4: New anchor command hook-up code

#region SelectNewAnchorCommand

SqlCommand selectNewAnchorCommand = new SqlCommand();
string newAnchorVariable = "@" +
   SyncSession.SyncNewReceivedAnchor;
selectNewAnchorCommand.CommandText =
   "sp_NewAnchor";
selectNewAnchorCommand.CommandType =
   CommandType.StoredProcedure;

selectNewAnchorCommand.Parameters.Add(newAnchorVariable,
   SqlDbType.Timestamp);
selectNewAnchorCommand.Parameters[newAnchorVariable]
   .Direction = ParameterDirection.Output;

selectNewAnchorCommand.Connection = serverConn;

serverSyncProvider.SelectNewAnchorCommand =
   selectNewAnchorCommand;

#endregion

The remainder of the constructor will be dedicated to constructing a SyncAdapter for each table that will need to be synchronized. The SyncAdapter tells sync services how to retrieve/update information from and to the server database. There are four types of tables that determine what needs to be defined in the SyncAdapter. The first is a download-only table, which can only be downloaded from the server by the client. The second is an upload-only table, which (exactly like it sounds) only allows the information to flow from the client to the server. The third is a bi-directional table, which will retrieve and send updates in both directions. Finally, a snapshot table refreshes data pulled from the server each time the database is synchronized. You have no snapshot tables in your example. Please see the documentation for sync services for more information on this type of sync.

The first table you will deal with is the Item table. You do not want the client to be able to modify the rows in this table, but you want any changes made at the server level to propagate down to the client databases. Thus, the Item table is a download-only table. Download-only tables require three commands that instruct the SyncAdapter how to select updated records from the server. These three commands are members of the SyncAdapter class, and they are SelectIncrementalInsertsCommand, SelectIncrementalUpdatesCommand, and SelectIncrementalDeletesCommand. These commands are also required in the bi-directional tables and will look very similar for all of the tables.

Again, I use stored procedures to encapsulate the commands. The SelectIncrementalInsertsCommand stored procedure for the Item table looks like this:

Listing 5: Item incremental updates stored procedure

CREATE PROCEDURE dbo.sp_Item_Incr_Updates
(
   @sync_last_received_anchor timestamp,
   @sync_new_received_anchor timestamp,
   @sync_client_id uniqueidentifier
)
AS
SELECT ItemID, ItemName
FROM Item
WHERE (
UpdateTimestamp > @sync_last_received_anchor
AND UpdateTimestamp <= @sync_new_received_anchor
AND UpdateId <> @sync_client_id
AND NOT (InsertTimestamp > @sync_last_received_anchor
AND InsertId <> @sync_client_id))
GO

As you can see, this command is responsible for selecting all of the fields of each row that has been updated since the anchor the client recorded when it received updates the last time and where the last client to update the record was not itself. Notice that for download-only tables, the last part will never be true because the UpdateId/InsertId will always be ‘00000000-0000-0000-0000-000000000000’ as we instructed in the database setup.

The SelectIncrementalUpdatesCommand is similar except that it pulls out inserted rows. It looks like this:

Listing 6: Item incremental inserts stored procedure

CREATE PROCEDURE dbo.sp_Item_Incr_Inserts
(
   @sync_last_received_anchor timestamp,
   @sync_new_received_anchor timestamp,
   @sync_client_id uniqueidentifier
)
AS
SELECT ItemID, ItemName
FROM Item
WHERE (
   InsertTimestamp > @sync_last_received_anchor
   AND InsertTimestamp <= @sync_new_received_anchor
   AND InsertId <> @sync_client_id)
GO

Finally, the SelectIncrementalDeletesCommand uses the tombstone table you created above:

Listing 7: Item incremental deletes stored procedure

CREATE PROCEDURE dbo.sp_Item_Incr_Deletes
(
   @sync_initialized bit,
   @sync_last_received_anchor timestamp,
   @sync_new_received_anchor timestamp,
   @sync_client_id uniqueidentifier
)
AS
SELECT ItemID, ItemName
FROM Item_Tombstone
WHERE (
   @sync_initialized = 1
   AND DeleteTimestamp > @sync_last_received_anchor
   AND DeleteTimestamp <= @sync_new_received_anchor
   AND DeleteId <> @sync_client_id)
GO

To tie it all together, you will instruct the serverSyncProvider to use these three stored procedures. Place the following code in your web service constructor:

Listing 8: Item sync adapter code

#region ItemSyncAdapter

//Create the SyncAdapter
SyncAdapter itemSyncAdapter = new SyncAdapter("Item");

#region SelectIncrementalUpdatesCommand

//Select updates from the server
SqlCommand itemIncrUpdates  = new SqlCommand();
itemIncrUpdates.CommandText = "sp_Item_Incr_Updates";
itemIncrUpdates.CommandType = CommandType.StoredProcedure;

itemIncrUpdates.Parameters.Add("@" +
   SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
itemIncrUpdates.Parameters.Add("@" +
   SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
itemIncrUpdates.Parameters.Add("@" + SyncSession.SyncClientId,
   SqlDbType.UniqueIdentifier);
itemIncrUpdates.Connection = serverConn;

itemSyncAdapter.SelectIncrementalUpdatesCommand =
   itemIncrUpdates;

#endregion

#region SelectIncrementalInsertsCommand

//Select inserts from the server
SqlCommand itemIncrInserts  = new SqlCommand();
itemIncrInserts.CommandText = "sp_Item_Incr_Inserts";
itemIncrInserts.CommandType = CommandType.StoredProcedure;

itemIncrInserts.Parameters.Add("@" +
   SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);

itemIncrInserts.Parameters.Add("@" +
   SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
itemIncrInserts.Parameters.Add("@" + SyncSession.SyncClientId,
   SqlDbType.UniqueIdentifier);

itemIncrInserts.Connection = serverConn;
itemSyncAdapter.SelectIncrementalInsertsCommand =
   itemIncrInserts;

#endregion

#region SelectIncrementalDeletesCommand
//Select deletes from the server
SqlCommand itemIncrDeletes = new SqlCommand();
itemIncrDeletes.CommandText = "sp_Item_Incr_Deletes";
itemIncrDeletes.CommandType = CommandType.StoredProcedure;

itemIncrDeletes.Parameters.Add("@" + SyncSession.SyncInitialized,
   SqlDbType.Bit);
itemIncrDeletes.Parameters.Add("@" +
   SyncSession.SyncLastReceivedAnchor, SqlDbType.Timestamp);
itemIncrDeletes.Parameters.Add("@" +
   SyncSession.SyncNewReceivedAnchor, SqlDbType.Timestamp);
itemIncrDeletes.Parameters.Add("@" + SyncSession.SyncClientId,
   SqlDbType.UniqueIdentifier);
itemIncrDeletes.Connection = serverConn;

itemSyncAdapter.SelectIncrementalDeletesCommand = itemIncrDeletes;

#endregion

//Add the SyncAdapter to the server synchronization provider
serverSyncProvider.SyncAdapters.Add(itemSyncAdapter);

#endregion

The code in Listing 8 just creates a SyncAdapter for the table, configures the incremental commands, and adds the SyncAdapter to the serverSyncProvider. A similar pattern will follow for the other tables as well. I will just discuss portions of the next tables that differ from this one.

Next, do a bi-directional table. For a bi-directional table, you will need to configure UpdateCommand, InsertCommand, and DeleteCommand, in addition to the three commands listed above for the download-only table. Dtart with the new commands. These commands are responsible for inserting information from the clients into the server database. They are very straightforward, so I will just outline the stored procedures here:

Listing 9: Customer sync upload stored procedures

/*    Update Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Updates
(
   @CustomerID UniqueIdentifier,
   @UserID UniqueIdentifier,
   @LastName VarChar(50),
   @FirstName VarChar(30),
   @sync_force_write bit,
   @sync_last_received_anchor timestamp,
   @sync_client_id uniqueidentifier,
   @sync_row_count int
)
AS
UPDATE Customer SET
CustomerID = @CustomerID,
UserID     = @UserID,
LastName   = @LastName,
FirstName  = @FirstName,
UpdateId   = @sync_client_id
WHERE CustomerID = @CustomerID AND
(@sync_force_write = 1
   OR (UpdateTimestamp <= @sync_last_received_anchor
   OR UpdateId = @sync_client_id))
SET @sync_row_count = @@rowcount
GO

/*    Insert Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Inserts
@CustomerID UniqueIdentifier,
@UserID UniqueIdentifier,
@LastName  VarChar(50),
@FirstName VarChar(30),
@sync_client_id uniqueidentifier,
@sync_row_count int
AS
INSERT INTO Customer (CustomerID, UserID, LastName, FirstName,
                      InsertId, UpdateId)
VALUES (@CustomerID, @UserID, @LastName, @FirstName,
        @sync_client_id, @sync_client_id)
SET @sync_row_count = @@rowcount
GO

/*    Delete Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Deletes
(
   @CustomerID UniqueIdentifier,
   @sync_force_write bit,
   @sync_last_received_anchor timestamp,
   @sync_client_id uniqueidentifier,
   @sync_row_count int
)
AS
DELETE FROM Customer
WHERE (CustomerID = @CustomerID)
AND (@sync_force_write = 1
OR (UpdateTimestamp <= @sync_last_received_anchor
OR UpdateId = @sync_client_id))
SET @sync_row_count = @@rowcount
IF (@sync_row_count > 0)  BEGIN
UPDATE Customer_Tombstone
SET DeleteId = @sync_client_id
WHERE (CustomerID = @CustomerID)
END
GO

The three stored procedures you saw in the download-only section would normally be the same for bi-directional as described above. For the example, however, because you only want clients to download information relevant to the specific user, you are going to do something a bit special. You will add a UserID parameter so that the service will only return the appropriate records. This will demonstrate how to pass custom parameters along with the default values passed by sync services. You will see how to configure this extra parameter later in the client section of this article. For clarity, here are the three incremental stored procedures for this table (you can compare them to the original ones above):

Listing 10: Customer sync download stored procedures

/*    Update Incr Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Incr_Updates
(
   @UserID uniqueidentifier,
   @sync_last_received_anchor timestamp,
   @sync_new_received_anchor timestamp,
   @sync_client_id uniqueidentifier
)
AS
SELECT CustomerID, UserID, LastName, FirstName
FROM Customer
WHERE (UserID = @UserID AND
   UpdateTimestamp > @sync_last_received_anchor
   AND UpdateTimestamp <= @sync_new_received_anchor
   AND UpdateId <> @sync_client_id
   AND NOT (InsertTimestamp > @sync_last_received_anchor
   AND InsertId <> @sync_client_id))
GO

/*    Insert Incr Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Incr_Inserts
(
   @UserID uniqueidentifier,
   @sync_last_received_anchor timestamp,
   @sync_new_received_anchor timestamp,
   @sync_client_id uniqueidentifier
)
AS
SELECT CustomerID, UserID, LastName, FirstName
FROM Customer
WHERE (UserID = @UserID AND
InsertTimestamp > @sync_last_received_anchor
AND InsertTimestamp <= @sync_new_received_anchor
AND InsertId <> @sync_client_id)
GO

/*    Delete Incr Sproc    */
CREATE PROCEDURE dbo.sp_Customer_Incr_Deletes
(
   @UserID uniqueidentifier,
   @sync_initialized bit,
   @sync_last_received_anchor timestamp,
   @sync_new_received_anchor timestamp,
   @sync_client_id uniqueidentifier
)
AS
SELECT CustomerID, UserID, LastName, FirstName
FROM Customer_Tombstone
WHERE (UserID = @UserID AND
   @sync_initialized = 1
   AND DeleteTimestamp > @sync_last_received_anchor
   AND DeleteTimestamp <= @sync_new_received_anchor
   AND DeleteId <> @sync_client_id)
GO

Note: The only differences are the addition of the @UserID parameter and the check to determine whether this row “belongs to” the user attempting to synchronize.

The code to link up this table in the web service constructor is similar to the Item table and can be found in the source code provided.

The CustomerFavoriteItem table is a bi-directional table. Follow the same steps as above for this table. Again, this table is slightly different in that you have to join to the Customer table to determine the UserID before you can do the compare to make sure the client needs to retrieve the row. If you want to see this logic, please look at the sample database or scripts provided.

Finally, the User table is an upload-only table that will allow the client to update its own ClientID when it first generates it. For the User table, I will leave it to you to create (or look up in the provided source code) the necessary commands (and the hookup code in the web service constructor) for an upload-only table, which are: UpdateCommand, InsertCommand, DeleteCommand, and SelectIncrementalInsertsCommand. As a tip, when creating the stored procedures, remember to include brackets around the table name User because User also is a built-in keyword.

Your web service is complete. Compile and get ready to start developing the client application for your sync services example.

The Client

The first thing you will do in this section is to create a Windows Forms application that will serve as the client front end for your synchronization services scenario. I called mine SyncServicesSample_Client.

The next order of business is adding logic to the program to check for an existing ClientID for the application, and, if one doesn’t exist, create it. There are many ways you could go about this. For brevity sake, if you would like to see my approach, please check the source code.

Once you have a method of generating a ClientID, you can start creating the sync layer for the client. The client side of sync services is driven by Microsoft.Synchronization.SyncAgent. The SyncAgent holds the reference to the ServerSyncProvider (that you have already created) and the ClientSyncProvider (that you are about to create). It also describes the SyncTables and their sync direction, which you also already know. The client requires references to Microsoft.Synchronization.Data.dll and Microsoft.Synchronization.Data.SqlServerCe.dll; they can be found in the same location as mentioned above. You also will need a reference to the System.Data.SqlServerCe.dll, which is located at “C:Program FilesMicrosoft SQL Server Compact Editionv3.5Desktop.” You also will add a connection string to the app.config. SQL Server Compact Edition uses .sdf database files. The connection string will look like this:

<connectionStrings>
   <add name="SampleClientDB"
        connectionString="Data Source='SampleClientDB.sdf';
        Password=pass@word1"/>
</connectionStrings>

Now, you will define the ClientSyncProvider. Create a new class. I will name mine SampleClientSyncProvider. First, add the necessary using directives:

using Microsoft.Synchronization.Data.SqlServerCe;
using System.Configuration;
using System.Data;
using System.Data.SqlServerCe;

Your class will extend the Microsoft.Synchronization.Data.SqlServerCe.SqlCeClientSyncProvider class like so:

class SampleClientSyncProvider : SqlCeClientSyncProvider

Like the ServerSyncProvider, you need to tell the ClientSyncProvider in the constructor how to talk to the client database. We will use our value from the app.config. The only other thing we will do in the constructor is to hook up to “schema events.” These will allow us to define settings, such as the rowguid property, which are not copied down when a client database is created from a server database. The complete constructor looks like the following:

Listing 11: Client sync provider constructor code

public SampleClientSyncProvider()
{
   this.ConnectionString =
      ConfigurationManager.ConnectionStrings["SampleClientDB"]
      .ConnectionString;

   this.CreatingSchema += new
      EventHandler<Microsoft.Synchronization.Data
      .CreatingSchemaEventArgs>
      (SampleClientSyncProvider_CreatingSchema);
   this.SchemaCreated += new
      EventHandler<Microsoft.Synchronization.Data
      .SchemaCreatedEventArgs>
      (SampleClientSyncProvider_SchemaCreated);
}

You will add code to the CreatingSchema and SchemaCreated event handlers for the bi-directional sync tables to ensure that proper primary keys are generated when rows are inserted. This is accomplished like so:

Listing 12: Client sync provider event handler code

void SampleClientSyncProvider_CreatingSchema(object sender,
   Microsoft.Synchronization.Data.CreatingSchemaEventArgs e)
{
   switch (e.Table.TableName)
   {
      case "Customer":
         e.Schema.Tables["Customer"].Columns["CustomerID"]
            .RowGuid = true;
         break;

      case "CustomerFavoriteItem":
         e.Schema.Tables["CustomerFavoriteItem"]
            .Columns["CustomerFavItemID"].RowGuid = true;
         break;
   }
}

void SampleClientSyncProvider_SchemaCreated(object sender,
   Microsoft.Synchronization.Data.SchemaCreatedEventArgs e)
{
   MakeSchemaChangesOnClient(e.Connection, e.Transaction,
      e.Table.TableName);
}

void MakeSchemaChangesOnClient(IDbConnection clientConn,
   IDbTransaction clientTran, string tableName)
{
   SqlCeCommand alterTable = new SqlCeCommand();
   alterTable.Connection  = (SqlCeConnection)clientConn;
   alterTable.Transaction = (SqlCeTransaction)clientTran;
   alterTable.CommandText = String.Empty;

   switch (tableName)
   {
      case "Customer":
         alterTable.CommandText =
         "ALTER TABLE [Customer] " +
         "ADD CONSTRAINT DF_CustomerID " +
         "DEFAULT NEWID() FOR CustomerID";
         alterTable.ExecuteNonQuery();
      break;

      case "CustomerFavoriteItem":
         alterTable.CommandText =
         "ALTER TABLE [CustomerFavoriteItem] " +
         "ADD CONSTRAINT DF_CustomerFavItemID " +
         "DEFAULT NEWID() FOR CustomerFavItemID";
         alterTable.ExecuteNonQuery();
      break;
   }
}

As you can see from the code, you are just instructing SQL Server CE to set the RowGuid property on the primary keys of the Customer and CustomerFavoriteItem tables and to set the default value of those fields to newid(). The ClientSyncProvider is now finished.

It is time to tie it all together with the SyncAgent. Create a new class (for example, SampleSyncAgent). This class will extend the SyncAgent class and requires the following using directives:

using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;

As I mentioned earlier, the SyncAgent will hold the references to the ClientSyncProvider (LocalProvider) and the ServerSyncProvider (RemoteProvider). The LocalProvider is easy:

this.LocalProvider = new SampleClientSyncProvider();

Because you implemented the ServerSyncProvider as a web service, it will require a bit more work. I must warn you, this part gets a bit tricky. You need to add a web reference to your web service just as you would with any project that consumes a web service. Visual Studio will generate a Reference.cs file that will attempt to create types for the web service-exposed objects instead of using the ones defined in the sync services libraries. For this reason, you will have to manually edit the references file to include the actual sync services types. First, locate the references file. It will be located in your project’s folder in the Web References folder. Open the file for editing (in Notepad or VS, whichever you prefer).

The first modification is to add the Microsoft.Synchronization.Data directive like so:

namespace SyncServicesSample_Client.SyncServicesSampleService {
   using  System.Diagnostics;
   using  System.Web.Services;
   using  System.ComponentModel;
   using  System.Web.Services.Protocols;
   using  System;
   using  System.Xml.Serialization;
   using  System.Data;
   using  Microsoft.Synchronization.Data;

Scroll down or search for the SyncSession class definition. The signature looks like this:

public partial class SyncSession

This is where the redundant definitions begin. Comment out or delete all the code starting from here all the way down to and including the SyncServerInfo class definition. Save the references file. That is all there is to it. If you want to see exactly what to comment out, take a look at the References.cs file in the sample code provided.

Now, you can go back to finish up your SyncAgent constructor. The sync services framework provides a ServerSyncProviderProxy wrapper class; it accepts a web service with the methods you have defined in your ServerSyncProvider web service. The code looks like this:

Listing 13: Remote provider hook-up code

SyncServicesSampleService.SyncServicesSampleService
   serverSyncWebService = new
   SyncServicesSampleService.SyncServicesSampleService ();
this.RemoteProvider = new
   ServerSyncProviderProxy(serverSyncWebService);

Defining the SyncTables is pretty clear-cut. You will define the table, set its creation option, describe the sync direction, and add the sync table to the SyncAgent. You can see the differences among the tables you created by inspecting the code below:

Listing 14: Sync tables code

#region User Table

SyncTable userSyncTable = new SyncTable("User");
userSyncTable.CreationOption =
   TableCreationOption.DropExistingOrCreateNewTable;
userSyncTable.SyncDirection = SyncDirection.UploadOnly;

this.Configuration.SyncTables.Add(userSyncTable);

#endregion

#region Item Table

SyncTable itemSyncTable = new SyncTable("Item");
itemSyncTable.CreationOption =
   TableCreationOption.DropExistingOrCreateNewTable;
itemSyncTable.SyncDirection = SyncDirection.DownloadOnly;

this.Configuration.SyncTables.Add(itemSyncTable);

#endregion

#region Customer Table

SyncTable customerSyncTable = new SyncTable("Customer");
customerSyncTable.CreationOption =
   TableCreationOption.UploadExistingOrCreateNewTable;
customerSyncTable.SyncDirection = SyncDirection.Bidirectional;

this.Configuration.SyncTables.Add(customerSyncTable);

#endregion

#region CustomerFavoriteItem Table
SyncTable customerfavoriteitemSyncTable = new
   SyncTable("CustomerFavoriteItem");
customerfavoriteitemSyncTable.CreationOption =
   TableCreationOption.UploadExistingOrCreateNewTable;
customerfavoriteitemSyncTable.SyncDirection =
   SyncDirection.Bidirectional;

this.Configuration.SyncTables.Add(customerfavoriteitemSyncTable);

#endregion

The final piece of the SyncAgent constructor is to add the @UserID custom parameter that you use to determine which rows to update from the server. This is the method you can use to add any custom parameters you will need to the sync layer. The syntax is as follows:

this.Configuration.SyncParameters.Add("@UserID", Program.ClientID);

Still with me? You are almost there. Now, it is time to create the database and add the synchronization logic. Back in Program.cs, you will add a check to see whether the client database file exists. If it does not, you will create the file and call the synchronization method to pull down the tables and data from the server database. You’ll want to add some data to the tables if you want to see anything brought down at this point. Otherwise, you can add it later when you’re playing with the synchronization to see all the information get shuffled around. Add this method to your Program class and call it from Main before the form is created:

Listing 15: Create client database code

public static void CreateClientDatabase()
{
    string clientConn = ConfigurationManager
      .ConnectionStrings["SampleClientDB"].ConnectionString;

   using (SqlCeConnection sqlconn = new SqlCeConnection(clientConn))
   {
      if (!File.Exists(sqlconn.Database))
      {
         SqlCeEngine sqlCeEngine = new SqlCeEngine(clientConn);
         sqlCeEngine.CreateDatabase();

         //if first time creating, run synchronization
         Synchronize();
         //add this client's UserID to the User table
         AddClientID();
      }
   }
}

Notice that I have an AddClientID method that will insert this client’s UserID into the client database so that any customers added by this user will have a UserID to map back to. Depending on how you implemented the generation of the clientID, code up this method accordingly. Remember that your User table is an upload-only table, so the next time the client syncs with the server, the new UserID will get pushed up to the server database.

You now must define the Synchronize method used in the code above. All this method does is call the built-in synchronize method and then display the results to the user. I have used a message box for simplicity:

Listing 16: Synchronize code

public static void Synchronize()
{
   SampleSyncAgent syncAgent = new SampleSyncAgent();
   SyncStatistics syncStats = syncAgent.Synchronize();

   StringBuilder sb = new StringBuilder();
   sb.AppendLine("Download Changes Applied: " +
      syncStats.DownloadChangesApplied);
   sb.AppendLine("Download Changes Failed: " +
      syncStats.DownloadChangesFailed);
   sb.AppendLine("Total Changes Downloaded: " +
      syncStats.TotalChangesDownloaded);
   sb.AppendLine("Total Changes Uploaded: " +
      syncStats.TotalChangesUploaded);
   sb.AppendLine("Upload Changes Applied: " +
      syncStats.UploadChangesApplied);
   sb.AppendLine("Upload Changes Failed: " +
      syncStats.UploadChangesFailed);

   MessageBox.Show(sb.ToString(), "Sync Results");
}

The SyncAgent‘s Synchronize method returns several useful statistics. Above, I just take those statistics and display them, but you could use them to determine the logical steps for your program to take after synchronization.

If you are anxious at this point (and I am sure you are), you could run your program and watch it create the client database; it will be located in the bin directory where your program is compiled. The final steps for finishing up this sample are to add user interface components so that your user can modify information for their customers. Mine looks like this, but obviously it could take many forms:

Figure 3: Sync example client form

Because in my example the server and client are on the same machine, my rudimentary form can allow the user to see what is on the server and what is in the client database. As you can see from the screenshot, this client only has two customers although there are several customers for multiple users in the server database. You can play around with the different tables and notice what changes get pushed back and forth between the databases. Remember, if you update a record directly in the server database, make sure you change the UpdateId to ‘00000000-0000-0000-0000-000000000000’ to see the changes filter down to the clients.

Conclusion

Creating synchronization logic for an offline, occasionally connected application can be difficult and complex. Microsoft Synchronization Services for ADO.NET attempts to support this effort by using the lightweight SQL Server Compact Edition to cache data locally for a client. As I am sure you have seen, even when using a pre-existing sync framework, the logic and work necessary can be extensive. The steps outlined in this article will help you start becoming familiar with sync services and provide a foundation for laying out your sync scenario.

Download the Code

You can download the code that accompanies this article here.

About the Author

Rachel Baker (MCPD, MCTS) is a developer specializing in Windows-based solutions at Crowe Chizek in Oak Brook, Illinois. Reach her at rwireman@crowechizek.com.