ADO .net Sync service on VS using Sql Server 2005

I am going to furnish you a set of steps to follow in order to configure the SQL Server 2005 in VS for ADO. Net synchronization.

If you would have read my previous blog on  What is Synchronization and How they work, it will be a piece of cake for you to follow these steps.

I am going a head to describe the steps thinking that you have basic knowledge on ado.net synchronization.

 

1. Create a new project in Visual Studio, preferably Windows application
2. Add a new item called “Local Data Cache” ( This item will be displayed in the dialog only if you have installed the SQL Server Compact Edition 1.0 or higher)
3. The filename will have a extension of .sync
4. Once the new item is added you will have a screen to configure items.

a)     You can select the server connection, if you have any of those in the dropdown or click the New button to create a new Sql server 2005 connection

b)     Same way you can select the client connection or click the New button to create a New SQL Compact Edition ie, only Compact edition of sql server can be used as client and not SQL express

c)      Click the Add button below the Cached Tables to add the table which you want to be synchronized with the Server

d)     Click the “Show Code Example” link in the right most bottom of the screen and copy the code to the clipboard( a button will be there to do the operation). I will tell you were to use the this code.

e)     Click OK, so that at the first time the data will be downloaded from the server to the client database.

f)       It will also try to create a dataset, just click cancel.

g)     For synchronization the schema of the table in the server and client will be modified and the script for this will be place in the current solution in a folder called “SQL Scripts” and “SQL Undo scripts”. This scripts does nothing but add two columns to your table for keeping track of the changes happened in offline and to sync with the Server tables. Column name “CreationDate” and “LastEditDate”

 

 

5. Now you can open your compact database .sdf and verify the table which will have all the data that are in the server with two additional columns
6. Go to the view code of the .sync file and specify the Sync direction for your local database cache item. You will have different enum for Sync Direction. Since here we are going to synchronize both the client and server, specified as Bidirectional

Ex. this.DataElement.SyncDirection = Microsoft.Synchronization.Data.SyncDirection.Bidirectional;

 

7. Now just create a form to update you client database table with values which are not updated in the server.
8. Put a button in the form and paste the code you have in the clipboard in the click events.
9. Now do some updates in the screen and see if the data is updated in the client and not in the server.
10. Then click the button(sync button) which will execute the two line code and update the server and same can be done vice versa by updating the data in the server table and doing a sync, which will update in you local database.

 

I hope you got a glimpse on how to work on ado.net sync. I will explain in my next article on how to do the same with SQL Server 2008