I've been following the Synchronization Services for a few months now, and I really liked what I saw.

One of the best places to get a head start in this technology is Rafik Robeal's site, SyncGuru. This site contains a lot of Sync Services demos, and provides a really good explanations and How-To's  for using the Sync Services advanced capabilities, such as N-Tier architecture, Conflicts Detection, and using Oracle as a backend database.

A few days ago I was invited by Oracle in Israel to present in an event called OracleWeek (Note: Hebrew site), and I wanted to demonstrate the most advanced capabilities of Sync Services. I wanted to be able to demonstrate:

- Oracle as a backend database (naturally...)

- Using N-Tier architecture (because most of the modern apps are built this way)

- Conflicts detection (because, after all, this is what really matters)

But I wanted all this in a single application.

So, after much work and some obstacles, I created the demo and presented it in the event. The feedback was quite positive,so I decided to upload the demo here, so everyone can see how this can be done.

So, here I am, proudly presenting the SNOC (take a look at the title, you'll understand...). The attached ZIP file contains the full SNOC demo, which demonstrates all the features mentioned above.

Demo Description

The demo simulates a typical scenario of a central database (Oracle Express) and two client applications. The communication between the client and the server is done via WCF using basic SOAP/HTTP protocol.

The demo allows the user to insert or update records (no support for deletes, sorry. Lack of time...), save them locally (into the SQL CE), and synchronize them with the server database. When a conflict is detected (the demo uses conflict detection on the client, not the server), an appropriate message is displayed, and the user can choose to overwrite the local values or abort the sync process.

This demo does by no means cover all the Sync Services goodies. Issues like deletes, transaction management, batch management, statistics, progress indicator and more are not demonstrated, but I believe it can still help to get a good idea of how this should work.

In addition, the code is far from perfect. It is not optimized, contains no minimal error checking and can definitely be built better. Its only purpose is to demonstrate the Sync Services features, and not coding best practices.

Software Prerequisites

Visual Studio 2008 RTM (Actually, this may work also in VS2005 with SQL CE and Sync Services installed, but I didn't try)

Oracle Express 10g (can be downloaded here)

Folders Description

There are 4 folders in the ZIP file:

OracleSyncDemo2 - Contains the master solution file and the source code of the client apps.

WebServiceFolder - Contains the project of the server side.

Demo2Clients - Contains two folders, each one containing a single exe and a config file. These exe's are exactly the same, and they simulate the two clients.

ClientDBBiDi - Contains the two SQL CE files, file for every client , pre-populated with the "customers" table.

Installation Instructions

1. Copy all the contents of the ZIP file to a folder in your hard drive.

2. Create a new user in Oracle. Call it whatever you want (I called mine "offlinesync").

3. In the Oracle admin site, log in as the new user.

3. Load the file offlinedemo.sql, found under "OracleSyncDemo2\OracleSyncDemo2\Setup", in the Oracle admin site. This file creates a "customers" table, and add some triggers to it in order to track the changes occurred to it.

4. In the web.config file, found under "WebServiceFolder\SyncWS", modify the "OracleExpress" connection string to point to your oracle database and user.

5. Open both the config files in the Demo2Clients folder. Note that:

- They have a different "ClientID" value (under appSettings). This is to differentiate them visually. This value is displayed in the window title.

- There is a single connection string which points to the local database location. Change the location to the folder in which you copied the two client databases.

- There is a service reference which points to http://localhost:1256/Service1.asmx. This shouldn't be a problem as the server project is set to use a fixed port.

OK, time to take off...

Usage Instructions

1. Open the OracleSyncDemo2.sln file found under "OracleSyncDemo2" folder. This should open Visual Studio 2008.

2. Note there are two projects in the solution. Make sure they are both successfully loaded.

3. Run the solution. Note that the solution is preconfigured to execute only the server side project. If everything is OK, you should see an IE window opens, pointing to the web service in the server project.

4. Execute both the executables in the Demo2Clients folder. If everything is all right, you should see something like that:

5. Enter some data in the first row of "Client 1", move the cursor to the second row and click "Save Client Data". You should see the following:

Right now we inserted a new row to the local SQL CE of Client 1. You can create a new connection in Server Explorer pointing to this file to see it changes in live.

6. Click "Synchronize" in Client 1. The first time may take a few seconds. you should see a message box stating that 1 record was uploaded successfully. Note: You may see that the total changes updated is more than 1. I'm not sure why this happens and I need to dig into it more. It will happen on the first synchronization only.

This action uploaded the new row to the backend database. Again, it might be a good idea to create a new connection in Server Explorer which will point to the Oracle to make sure everything is fine.

7. Click "Synchronize" in Client 2. Again, this might take a few seconods, afterwards you should see the record you inserted in Client 1 appears in Client 2.

8. Let's try a more complex sync. Add a new row in Client 2 and, in addition,  modify the first row in Client 2. Click "Save Client Data", and you should see the following message:

9. Now, click "Synchronize" in Client 2 (you should see a message saying that 2 rows were uploaded) and in Client 1 (2 rows were downloaded). The windows should look like that:

10. OK, let's conflict! Change the customer name in row 2 in Client 1, and click "Save Client Data". Do the same, with different customer name, in Client 2. You should see this:

11. Now, click "Synchronize" on Client 1. Remember: After synchronizing, the row in the Oracle is different than the one in Client 2.

12. Change the Sync Direction in Client 2 to "DownloadOnly". This is required since the conflict detection in this demo occurs in the client, and not the server.

13. Click "Synchronize" in Client 2. You should see the following message:

You can choose whether to go on with the synchronization and overwrite the local values or abort it.

OK, that's all folks. I hope this demo did a good job in demonstrating how to use Sync Services with Oracle, using N-Tier architecture and conflicts detection. I didn't talk about the code at all because:

1. This post is already too long

2. The code is basically a merge of the samples posted in SyncGuru.

If you do want me to explain the code in later posts, please leave a comment.

If you find all this interesting, please download the demo and start synchronizing!