Sync Services in SQL Server Compact Edition 3.5 Visual Studio 2008

Sync Services in SQL Server Compact Edition 3.5 Visual Studio 2008

Visual Studio 2008 has a powerful feature Sync Services for SQL Server Compact Edition, which will allow user to sync data from client to server and vice versa, this will make job easy for developer who are developing smart device application or window application with local database.
Modify local database as and when you needed and updating with server database is far easier than earlier merge applications.

Let us build a sample application for demo the Sync Services in SQL CE 3.5

Create a windows application and a new item and select LocalDataCache type, this LocalDataCache is the local database file holds the local copy of your server database

Once you add LocalDataCache file you will see following window on your screen to select particular tables from the server database which you want to keep at client side. Here you need to select the Server connection by clicking new and client databse(.sdf) file is automatically created by VS2008 for you

Here I am taking the Northwind database for this example and I am selecting Employees table for demo


Now click on Add button at below to select specific tables

Once you have selected the tables just click on the Show Code Example and copy the code into clipboard this code we will use for Synching the data.

Now we are ready with the back end part, creating a local database etc.

Now let’s design a form to demonstrate the synching mechanism
Here I am adding 2 DataGridViews one is for local database and second is for server database.

Click on Add Project Data source


Select Database click on Next


Select Client Connection and Click Next

Click on Finish.

Now we will add data source for second datagridview and it will be server database data.

Add Project Data Source


Click on Next


Select Server Data connection and Click next

Select the employee table and click finish.

Now add 3 buttons showed in below screen shot



Double click on Update Local Data and add following code, this code updates the data in the local database

private void btnLocalUpdate_Click(object sender, EventArgs e)
{
employeesTableAdapter.Update(this.northwindDataSet.Employees);
}

Double click on Update Server Data and add following code, this code updates the data in the Server database

private void btnServerUpdate_Click(object sender, EventArgs e)
{
employeesTableAdapter1.Update(this.northwindDataSet1.Employees);
}

Now we are updating Local and Server database individually, now we will write a code that will sync both the databases, now double click on Sync data and add following code

private void btnSync_Click(object sender, EventArgs e)
{
// Call SyncAgent.Synchronize() to initiate the synchronization process.
// Synchronization only updates the local database, not your project’s data source.
LocalDataCache1SyncAgent syncAgent = new LocalDataCache1SyncAgent();
Microsoft.Synchronization.Data.SyncStatistics syncStats = syncAgent.Synchronize();

this.northwindDataSet.Merge(this.employeesTableAdapter.GetData());
// TODO: Reload your project data source from the local database (for example, call the TableAdapter.Fill method).
// TODO: This line of code loads data into the 'northwindDataSet1.Employees' table. You can move, or remove it, as needed.
this.employeesTableAdapter1.Fill(this.northwindDataSet1.Employees);
// TODO: This line of code loads data into the 'northwindDataSet.Employees' table. You can move, or remove it, as needed.
this.employeesTableAdapter.Fill(this.northwindDataSet.Employees);
}

To sync data bio directional that mean updating from client to server and vise versa we need to add a line of code, now open the LocalDataCache1.cs add following line in OnInitialized

this.Employees.SyncDirection= Microsoft.Synchronization.Data.SyncDirection.Bidirectional;

Now we are ready with our example just run the application and modify data in any one the grid and update it and click on Sync Data and see the out put.

Hope this helps.