In the previous part of the tutorial we learnt how to use SQL Server Compact Edition in our application. In this part we are going to learn how to synchronize the data between Compact Edition and a regular verson of SQL Server.

Visual Studio 2008 has built-in Synchronization Services that we’ll be using in our project.

Create a new Windows Forms Application. In Server Explorer create a new database; create a table with just two fields:

• ID
• SampleColumn

Then, go to Project->Add Component->Data->Local Database Cache

Find the newly created database and choose the only table. Click ‘Ok’.  When it will ask you to create a DataSet, just click ‘Cancel’, we won’t use any datasets in this project. As you can see it adds to field to our table, one is responsible for the last created item and the other one for the last update item. Hopefully, it also provides the SQL scripts for undoing these changes.

It’s necessary to make the synchonization bi-directional, so that it allows us to syncrhonize in both directions. Unfortunately it cannot be done in the design mode, so we have to write some code. Right-click on the .sync file -> View Code, it will create a new file containing a partial class. Add this piece of code:

namespace SyncServices1 {
     public partial class LocalDataCache2SyncAgent {
         partial void OnInitialized(){
             SampleTable.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional; // if the table is called SampleTable
         }
     }
 }

Then, let’s make a very simple form. Place two ListBoxes, one will be for the remote data and one for the local. Place a button as well that will trigger a syncrhonization process. Put also a textbox and a button that will be used for inserting data.

Now add some the code that will retrieve the data from the remote database and fill the approprate list, do the same for the local data.

using (SqlConnection con = new SqlConnection("Data Source=(local);Initial Catalog=SyncServices1;Integrated Security=True;"))
 {

     con.Open();
     SqlCommand comSelect = new SqlCommand("SELECT SampleColumn FROM SampleTable", con);


     SqlDataReader reader = comSelect.ExecuteReader();

     listServer.Items.Clear();
     while (reader.Read())
     {
         listServer.Items.Add(reader["SampleColumn"].ToString());
     }

     con.Close();
 }

Then, go to the design view of the *.sync file, hit the ‘code’ button’ and copy the code. Add this code to the button OnClick event. It updates the databases only, so we have to add some code to update the view (form) of our application, the do to section hints at that.

private void btnSync_Click(object sender, EventArgs e)
 {
     LocalDataCache2SyncAgent syncAgent = new LocalDataCache2SyncAgent();
     Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();

     FillServerList();
     FillLocalList();
 }

So, let’s add some sample data to our remote server. Then, compile the project. See that the left box has the data, while the right one is empty, hit the ‘Sync’ button, and they will become synchonized.

Now, add some code that will handle inserting data in our local database.

Let’s add some text and click the ‘Sync’ button, so that the server will also get updated.

Conclusion

Microsoft Synchronization Services for ADO.NET is a great tool when you need to develop occasionally connected applications that require data synchronization. In the tutorial we saw how it's easy to peform such a synchronization.

Resources

Microsoft Sync Framework Developer Center

Microsoft Synchronization Services for ADO.NET Books Online