Click here to Skip to main content
15,175,198 members
Articles / Programming Languages / Visual Basic
Posted 18 Sep 2008


98 bookmarked

Introduction to ADO.NET Sync Services

Rate me:
Please Sign up or sign in to vote.
4.63/5 (28 votes)
18 Sep 2008CPOL8 min read
This article shows how easily we can create occasionally connected WinForms application using ADO.NET Sync services.


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.


At our organization, we have designed an application for Fire Rescue chiefs who are usually on unit and want the ability to access the application using their laptops and even PDAs. They use Verizon 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 more and more important for organizations. The Microsoft Sync framework has been designed to address these issues, and gives a great framework to easily design your applications on occasionally connected architecture. For this article, I will use a VB.NET sample as 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 the setting up step by step. Hopefully, in the process, I'll be able to explain the whole thing in detail.

First, you'll have to download the Sync framework from the Microsoft website. The V1 is available for download from MSDN.

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":

Image 1

What this is going to do is 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 its support for Visual Studio 2008. It provides a nice wizard for the whole set up, making our life a lot 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 setup, 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 "AdventureWorks" database. This is how the second screen looks like:

Image 2

Now that you have given your server connection information, the wizard creates a client connection. This is nothing but a new SQL CE database that you have 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.

Image 3

Select the tables that you would like to cache on the client side. Now, remember, you do not want to select all the tables as it's not practical to cache the whole master database on the client machine, as usually, the client machines in such cases as laptops, tablets, and PDAs may not have enough storage. In the above scenario, I have selected three 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 the default settings.

The first option asks you what data you want to download. new and incremental changes are done only after the first synchronization. Also, the sync framework is going to add two 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 a tombstone table.

Select "OK", and "OK" on the first wizard screen. This will create a local cached database with the tables you selected. On the first wizard screen, you also have options to select the server and the client project location. In this scenario, both will be our current projects. In my next article, I will try to cover a three tier example of using WCF services. And, in an N tier application, you can select your server and client application to be different. You can optionally select to create synchronization components for the client only or the server only. By default, it's the client and the 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 the following screen, allowing you to select tables to be added to your dataset. This will allow easy creation of the grid on the form with a typed dataset.

Image 4

As you can see, I have selected all the three 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 the data sources under the Data menu). Select the Employee table and select the DataGridView, and drag the table on the 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. In order 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 the click of a button.

Image 5

Double click the button, and add the code to sync the databases. Now, it's just three lines of code, and out of those, two lines have been provided right in the wizard. Click LocalDataCache1.sync, and you will notice that on the right bottom 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:


And that's it, your first occasionally connected WinForms 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 gets updated with new data. Now, try changing the data on the client and see if it is reflected on the server. It wont 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 LocalDataCache1.sync, and select View Code. You will see the SyncAgent class; just add the following code:

Partial Public Class LocalDataCache1SyncAgent
    Private Sub OnInitialized()
        Me.Employee.SyncDirection = _
    End Sub
End Class

And now, your application should sync up in both directions. Try changing some data on the client side, click the "Save" button on the tool bar, 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 the 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 do by implementing a partial class for your server sync provider. Below is a 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 ApplyChageFailed, force changes from the client to be over written to the server.

Partial Class LocalDataCache1ServerSyncProvider

    Private Sub LocalDataCache1ServerSyncProvider_ApplyChangeFailed _
            (ByVal sender As Object, _
            ByVal e As Microsoft.Synchronization.Data.ApplyChangeFailedEventArgs) _
            Handles Me.ApplyChangeFailed
        e.Action = Microsoft.Synchronization.Data.ApplyAction.RetryWithForceWrite
    End Sub
End Class

In this case the changes from 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.

Private 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") Then
        e.Action = _
    End If
End Sub

Here, we are checking the date modified on the server and the date modified on the client, and if the date modified on the client is later, we are forcefully updating the server with the client changes; otherwise, we 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 calculations. E.g., in the case of an inventory, you may want to update both the server and the client with the addition of values from the server and client updates.

Points of Interest

This is a real simple example, and in real world, you may have a lot more complex scenario to implement. But, the purpose of this article is to show how easy it is to sync a framework to design occasionally connected Smart Client applications.


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Vishal Shukla
Architect L&T Infotech
India India
Vishal has over 17 years of IT experience with over 15 years on Microsoft technologies from QW Basic to .net 4.5. Vishal works as a Lead Architect for the L&T Infotech. He has given various presentaions at code camps, Teched Tweener and various user group events.
Winner of INETA Community Champions award for 2008 Q3.
Microsoft VB .Net MVP 2009-2011

Comments and Discussions

QuestionWhy Local Data Cache is no more available in VS2013? Pin
Member 1138298024-Jun-15 8:44
MemberMember 1138298024-Jun-15 8:44 
QuestionHow to Sync conditionally Pin
Member 1122200515-Jan-15 23:46
MemberMember 1122200515-Jan-15 23:46 
AnswerRe: How to Sync conditionally Pin
Member 1172559028-May-15 3:42
MemberMember 1172559028-May-15 3:42 
QuestionError: Error 1 'SyncTables' is not a member of 'MazusConsult.LocalDataCache1SyncAgent.ConfigurationSyncTable' 135 13 InventControl Pin
Member 1059651522-Feb-14 11:54
MemberMember 1059651522-Feb-14 11:54 
QuestionSync Local MySql with MySql located on Web Server Pin
__BrokenArrow__5-Mar-13 3:02
Member__BrokenArrow__5-Mar-13 3:02 
AnswerRe: Sync Local MySql with MySql located on Web Server Pin
Vishal Shukla28-Mar-13 10:20
MemberVishal Shukla28-Mar-13 10:20 
GeneralMy vote of 5 Pin
Manjunath Shrikantiah10-Feb-13 23:58
MemberManjunath Shrikantiah10-Feb-13 23:58 
GeneralRe: My vote of 5 Pin
Vishal Shukla28-Mar-13 10:20
MemberVishal Shukla28-Mar-13 10:20 
QuestionAccess DB sync to SQL server using Sync Framework Pin
Vaishu20116-Dec-12 21:14
MemberVaishu20116-Dec-12 21:14 
Questionthis Work whitout Local Data Cache ?? Pin
oscarjgh16-Oct-12 13:29
Memberoscarjgh16-Oct-12 13:29 
AnswerRe: this Work whitout Local Data Cache ?? Pin
Vishal Shukla21-Oct-12 17:49
MemberVishal Shukla21-Oct-12 17:49 
QuestionVS2010 using C# Pin
randydouglas20048-Jul-12 5:42
Memberrandydouglas20048-Jul-12 5:42 
AnswerRe: VS2010 using C# Pin
Vishal Shukla14-Jul-12 1:58
MemberVishal Shukla14-Jul-12 1:58 
GeneralMy vote of 5 Pin
Member 280366522-Feb-12 23:56
MemberMember 280366522-Feb-12 23:56 
GeneralRe: My vote of 5 Pin
Vishal Shukla29-Mar-12 2:04
MemberVishal Shukla29-Mar-12 2:04 
GeneralSql express as client DB Pin
Member 76778072-Mar-11 1:10
MemberMember 76778072-Mar-11 1:10 
GeneralRe: Sql express as client DB Pin
Vishal Shukla2-Mar-11 18:58
MemberVishal Shukla2-Mar-11 18:58 
GeneralDoesn't Work Pin
Frankkkkkk4-Nov-10 8:46
MemberFrankkkkkk4-Nov-10 8:46 
GeneralRe: Doesn't Work Pin
Vishal Shukla4-Nov-10 21:59
MemberVishal Shukla4-Nov-10 21:59 
GeneralCode is Not Compitable to VS 2010 Pin
Lavanyaz18-Oct-10 10:26
MemberLavanyaz18-Oct-10 10:26 
GeneralRe: Code is Not Compitable to VS 2010 Pin
Vishal Shukla18-Oct-10 19:58
MemberVishal Shukla18-Oct-10 19:58 
GeneralVS 2010 not working Pin
Console.Write(30-Jun-10 0:06
MemberConsole.Write(30-Jun-10 0:06 
GeneralRe: VS 2010 not working Pin
Vishal Shukla30-Jun-10 20:11
MemberVishal Shukla30-Jun-10 20:11 
GeneralThank you pal Pin
MeraMBA.com18-Apr-10 22:17
MemberMeraMBA.com18-Apr-10 22:17 
GeneralRe: Thank you pal Pin
Vishal Shukla19-Apr-10 3:46
MemberVishal Shukla19-Apr-10 3:46 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.