Introduction to ADO.NET Sync Services

Introduction

This article will demonstrate how easily you can design occasionally-connected applications using ADO .Net Sync services. It's going to be a very basic example in VB .Net showing how easily you can use Sync services within your application. I have taken the template from Code Project and will try to upload it there as well. I am also uploading my presentation in case anyone needs it.

Background

At our organization we have designed an application for the Fire Rescue chiefs who are usually on unit and want the ability to access the application using their laptops and even PDAs. They use Venison cards for broadband access and it works well for the most part, but once in a while they lose the connection and the application dies. The ability to support mobile and remote workers is becoming increasingly important for organizations. The Microsoft Sync framework has been designed to address these issues and gives a great framework to easily design your applications for occasionally-connected architectures. For this article I will use VB .Net as a sample since there are very few VB .Net examples on the net.

Using the Code

I have uploaded the complete source code for the project, at the same time I will go through setting it up step-by-step. Hopefully I'll be able to explain the process in detail.

First you'll have to download the Sync framework from the Microsoft site. The V1 is available for download at:

http://msdn.microsoft.com/en-us/sync/default.aspx

Once the framework has been installed, start Visual Studio 2008 and create a new Windows Forms application.

Now let's add a new item to the project "Local Data Cache"; see:

 
What this is going to do is to create a local SQL CE database that will be used by the client application, and we will eventually write the code to sync up this database with our master database for selected tables. The best part of the Sync framework is it's support for Visual Studio 2008. It provides a nice wizard for the whole setup making our life much easier.

As soon as you click the "Add" button the next screen pops up, which will be used to set up your local cached database. On the screen select "Server Connection". Now if you have previously used the Windows Forms application you may already have a SQL connection set up like in my case, otherwise click the new button and create the connection string for your master SQL Server. For this example I will use our favorite "Adventure Works" database. This is how the second screen looks:

Now that you have given your server connection information, the wizard creates a client connection. This is nothing but the new SQL CE database that you just created. Also now that the wizard is aware of the server database it will allow you to add the tables to be cached. Click the "Add" button on the left bottom of the screen. Once you hit the "Add" button the following screen appears:

Select the tables that you would like to cache on the client side. Now remember, you do not want to select all the tables, since it's not practical to cache the whole master database on the client machine; usually the client machines in such cases, such as laptops, tablets and PDAs may not have enough storage. In the preceding scenario I have selected 3 of the tables. On the right side of the screen you will get some more setting options. In most cases you would like to keep them to default settings.

The first option asks you what data you want to download; new and incremental changes only after the first synchronization or the entire table each time. Also the sync framework is going to add 2 new columns to your tables to keep track of the last update and new inserts. You can choose existing columns if you are already tracking it. Also it will create a history table named TABLENAME_Tombstone. This is used to keep track of deleted rows. With SQL Server 2008 you would not need either of them as SQL Server has standard change tracking. So with SQL Server 2008 it will not add any additional columns or tombstone table.

Select "OK", and "OK" on the first wizard screen, this will create the local cached database with the tables you selected. On the first wizard screen you also have options to select the server and client project location. In this scenario both will be our current project. In my next article I will try to cover a 3 tier examples using WCF services. And in N tier applications you can select your server and client application to be different. You can optionally select to create synchronization components for the client only or server only. By default it's client and server.

After hitting ok on this screen the wizard will create a local database (.sdf); in our case it created AdventureWorks.sdf. It will then prompt you with following screen allowing you to select tables to be added to your dataset. This will allow easy creation of a grid on the form with a typed dataset.

As you can notice I have selected all the 3 tables. After clicking Finish it will create a dataset for the project. You can open the dataset and add some more tables from the Server Explorer, but those tables will not be cached on the client machine. They will be available to use in the application but the application will go back to the server each time those tables are accessed. For this demo we'll keep it simple and not add any more tables, as the main purpose of the demo is to see the sync framework in action. Now go back to your form and open your data sources (show data sources under data menu). Select Employee table and select Data grid view, and drag the table on form. This should add the GridView to the form with all the navigation controls. I really like this part of the design; just drag and drop and you are ready to go.

Now comes the main part, to activate the sync process we will add the button to the grid's tab strip. Usually you will have a service running in the background that will check for network availability and if its available it will initiate the sync process. Again for this demo we will try to keep it simple and will call the sync process on click of a button.

 

Double-click the button and add the code to sync the databases. Now it's just 3 lines of code and out of those 2 lines have been provided right in the wizard. Click the LocalDataCache1.sync and you will notice that on the bottom-right corner is "Show Code Example", click that copy the code and cut & paste it in the event handler for the button click.

Now just add code to merge the changes to the client table using;

Me.AdventureWorksDataSet.Employee.Merge(EmployeeTableAdapter.GetData())

And that's it, your first occasionally connected Windows Forms application is ready to run. Browse through the data on the client side. Now make some changes to the data on the server. The data on the client is still old as we have not initiated the sync process; click the sync button and your client is updated with new data. Now try changing data on the client and see if it is reflected on the server; it won't, as by default the sync works unidirectional. But again they have made it real easy to change it to bidirectional. All you have to do is right-click the LocalDataCache1.sync and say view code. You will see the SyncAgent class, just add the following code;

Partial Public Class LocalDataCache1SyncAgentPrivate

    Sub

        OnInitialized(Me.Employee.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional)

    End Sub

End Class

And now your application should sync in both directions. Try changing some data on the client side, click the "save" button on the toolbar and then click the sync button. Data on the server should reflect the changes done on the client side, but wait have we got us in trouble by allowing bidirectional sync? What happens if both the client and server update the same record, how will it work? Again the framework at your rescue, all such conflicts raise an ApplyChangesFailed event for the server sync provider which you can implement by implementing a partial class for your server sync provider. The following is the sample of how to do it.

By default the changes from the server are overwritten on the client; you can change that logic to say that in the case of an ApplyChageFailed, force the changes from the client to be over-written to the server. The code is:

Partial Class LocalDataCache1ServerSyncProviderPrivate

Sub

LocalDataCache1ServerSyncProvider_ApplyChangeFailed _(ByVal sender As Object, _ByVal e

As Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs) _Handles

        Me.ApplyChangeFailede.Action = Microsoft.Synchronization.Data.ApplyAction.RetryWithForceWrite

    End Sub

End Class

In this case the changes from the client are always written to the server. Again this may not be a practical solution as in most case you may want to do some kind of validations before accepting either client or server changes. And the best part is that even that's easy to implement. All you do is take the client changes and server changes and apply your business rule.

Partial Class LocalDataCache1ServerSyncProviderPrivate

Sub LocalDataCache1ServerSyncProvider_ApplyChangeFailed _(ByVal sender As Object, _ByVal e As Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs)
       _Handles(Me.ApplyChangeFailed)
        Dim clientChanges As DataTable = e.Conflict.ClientChange
        Dim serverChanges As DataTable = e.Conflict.ServerChange
      If (clientChanges.Rows(0)("ModifiedDate") > serverChanges.Rows(0)("ModifiedDate")
          Thene.Action = Microsoft.Synchronization.Data.ApplyAction.RetryWithForceWrite()

        End If
    End Sub
End Class

Here we are checking the date modified on the server and date modified on the client, and if the date modified on the client is later then we are forcefully updating the server with client changes otherwise do nothing. You can apply any business logic here and in fact you can use the ApplyingChanges event and modify the data before it is updated. This could be useful in cases where you want to update the values based on some calculation e. g. in case of an inventory you may want to update both the server and client with addition of values from the server and client updates.

Points of Interest

This is a real simple example and in the real world you may have much more complex scenarios to implement, but the purpose of the article is to show how easy it is to use the sync framework to design occasionally-connected smart client applications.