Walkthrough: Executing Synchronization with Second Client

Article
07/07/2014

The second compact database SyncCompactDB2 should already be in sync with the SyncDB server database because SyncCompactDB2 is a snapshot of SyncCompactDB, which was already synchronized with the SyncDB database.

In this walkthrough you will create a console application that syncs the second compact client with the server, add a new record to the server, synchronize the second compact client with the server, and also synchronize the first compact client with the server. For in-depth technical details about provisioning servers/clients and executing the synchronization process, see How to: Execute Database Synchronization (SQL Server).

To execute synchronization

In Solution Explorer, right-click Solution ‘SyncSQLServerAndSQLCompact’, point to Add, and click New Project.
Select Visual C# from Project Types, and select Console Application from Templates.
Type ExecuteSecondCompactSync for project name.
Click OK to close the New Project dialog box.
In Solution Explorer window, right-click ExecuteSecondCompactSync, and click Add Reference.
Select Microsoft.Synchronization, Microsoft.Synchronization.Data, Microsoft.Synchronization.Data.SqlServer, Microsoft.Synchronization.Data.SqlServerCe and click OK to close the Add Reference dialog box.
Repeat previous two steps to add a reference to System.Data.SqlServerCe assembly.

Add the following using statements to the beginning of the Program.cs file after the existing using statements.

using System.Data.SqlClient;
using System.Data.SqlServerCe;

using Microsoft.Synchronization; 
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization.Data.SqlServerCe;

Add the following statement to the Main method to create a SQL connection to the second compact database.
Add the following statement to the Main method to create an SQL connection to the server database.

Important

In the above statement, replace the server name with your server’s instance name, if you are not using the default instance. For example: if your SQL Server instance is called MYSQLINSTANCE, replace (local) with .\MYSQLINSTANCE.
Add the following code to the Main method to create a sync orchestrator, which initiates and controls synchronization sessions. The sync orchestrator contains two sync providers that will participate in a synchronization session. In our scenario, you will need to use a provider object for the server database and a provider object for the second SQL Server compact client database. The high level steps of creating an orchestrator for this scenario are:
1. Create an instance of the SyncOrchestrator class. The SyncOrchestrator class initiates and controls synchronization sessions.
2. Set the local provider of the sync orchestrator object to a SqlCeSyncProvider object associated with the SyncCompactDB2 client database. The SqlCeSyncProvider class encapsulates a synchronization provider for SQL Server Compact that communicates with the client and shields the synchronization orchestrator from the specific implementation of the client database.
3. Set the remote provider of the sync orchestrator to a SqlSyncProvider object associated with the SyncDB server database. The SqlSyncProvider class represents a synchronization provider that communicates with a SQL Server database and shields other Sync Framework components from the specific implementation of the database.
4. Set the sync direction of sync orchestrator object to UploadAndDownload so that the client can download/upload changes from/to the server.
Add the following code to subscribe for any errors that occur when applying changes to the client. The ApplyChangeFailed event is raised when a row could not be applied at a client. You will be defining the handler for the error event later in this walkthrough.
```
// subscribe for errors that occur when applying changes to the client
((SqlCeSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(Program_ApplyChangeFailed);
```
Add the following code to the Main method to execute the synchronization between SQL Server and SQL Server Compact. This code invokes the Synchronize method on the SyncOrchestrator object to start the synchronization process between the SyncDB server database and SyncCompactDB compact database.
Add the following statements to the Main method to display statistics returned by the Synchronize method. The SyncOperationStatistics object returned by this method contains statistics about the synchronization session that was executed.
Add the following event handler method to the Program class after the Main method to handle the ApplyChangeFailed event. The DbApplyChangeFailedEventArgs parameter provides information about the error or conflict that caused the failure. In a handler for the event, you can respond to the event in several ways, including specifying whether the synchronization provider should try to apply the row again. The Error property of the object contains metadata about any exceptions that occurred during synchronization. The following sample code displays this error and the type of conflict (of type DbConflictType) occurred during synchronization.
```
static void Program_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
{
    // display conflict type
    Console.WriteLine(e.Conflict.Type);

    // display error message 
    Console.WriteLine(e.Error);
}
```
In Solution Explorer, right-click ExecuteSecondCompactSync, and click Build.
In Solution Explorer, right-click ExecuteSecondCompactSync again, and click Set as Startup Project.

Warning

If you do not perform this step and you press Ctrl+F5 again, the ProvisionSecondCompactClient application is executed again, and you get an error message about the scope that already exists in the client database.
Press Ctrl+F5 to execute the program. You should see output similar to the following:
```
Start Time: 6/14/2010 8:03:31 PM
Total Changes Uploaded: 0
Total Changes Downloaded: 0
Complete Time: 6/14/2010 8:03:34 PM

Press any key to continue . . .
```
You see that no records were downloaded because the client is already in sync with the server.
Press ENTER to close the command prompt window.
In the SQL Server Management Studio, expand select (local) SQL Server, expand Databases, expand SyncDB, and expand Tables.
Right-click dbo.Products, and click Edit Top 200 Rows.
Add a record at the end with 5 as ID, Wireless Keyboard for Name, and 30 for ListPrice. Make sure the record is saved by pressing TAB.
Now, in Visual Studio, press Ctrl+F5 to execute the client program again. You should see output similar to the following output.
```
Start Time: 6/14/2010 8:06:21 PM
Total Changes Uploaded: 0
Total Changes Downloaded: 1
Complete Time: 6/14/2010 8:06:23 PM

Press any key to continue . . .
```
The one record you added to the server should be downloaded to the client now. You can verify this by using the following steps.
In SQL Server Management Studio, select SQL Server Compact [My Computer\...\SyncCompactDB2].
Click New Query from the toolbar.
Type and execute (by pressing F5) the following SQL command to confirm that records are indeed downloaded to the compact client.
```
select * from products
```
The first compact client is not in sync yet. To confirm, do the following:
1. In the Object Explorer window, select SyncCompactDB (without 2 at the end).
2. Click New Query from the toolbar.
3. Type and execute the following command.
```
select * from products
```
  You will not see the latest record you added to the server yet. The second client is in sync, but the first client is not.
In Visual Studio, in Solution Explorer, right-click ExecuteCompactSync, and then click Set as Startup Project.
Now, in Visual Studio, press Ctrl+F5 to execute the client program again. You should see output similar to the following output.
```
Start Time: 6/14/2010 8:10:54 PM
Total Changes Uploaded: 0
Total Changes Downloaded: 1
Complete Time: 6/14/2010 8:10:56 PM

Press any key to continue . . .
```
The one record you added to the server should be downloaded to the first client now. You can verify this by using the following steps: In the SQL Server Management Studio, execute the select command you had run earlier against the first compact database (SyncCompactDB).
You can play around to become familiar with the Sync Framework technology by adding/updating/deleting records from the server/client. For example, if you delete a record from the server, the corresponding record in the client database should be deleted next time you sync client with the server.
Keep Visual Studio and SQL Server Management Studio open if you want to perform optional walkthroughs related to parameterized scope.

Complete Code Example

using System;
using System.Data.SqlClient;
using System.Data.SqlServerCe;

using Microsoft.Synchronization;
using Microsoft.Synchronization.Data;
using Microsoft.Synchronization.Data.SqlServer;
using Microsoft.Synchronization.Data.SqlServerCe;

namespace ExecuteSecondCompactSync
{
    class Program
    {
        static void Main(string[] args)
        {
            //create a connection to the second compact database
            SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\SyncSQLServerAndSQLCompact\SyncCompactDB2.sdf'");

            //create connection to the server database
            SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");

            // create a sync orchestrator
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            // set the local provider to a CE sync provider associated with the
            // ProductsScope in the Sync Compact DB 2 database
            syncOrchestrator.LocalProvider = new SqlCeSyncProvider("ProductsScope", clientConn);

            // set the remote provider to a server sync provider associated with the
            // ProductsScope in the Sync DB server database
            syncOrchestrator.RemoteProvider = new SqlSyncProvider("ProductsScope", serverConn);

            // set the diretion to Upload and Download
            syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

            // subscribe for errors that occur when applying changes to the client
            ((SqlCeSyncProvider)syncOrchestrator.LocalProvider).ApplyChangeFailed += new EventHandler<DbApplyChangeFailedEventArgs>(Program_ApplyChangeFailed);

            // execute the synchronization process
            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

            //print sync statistics
            Console.WriteLine("Start Time: " + syncStats.SyncStartTime);
            Console.WriteLine("Total Changes Uploaded: " + syncStats.UploadChangesTotal);
            Console.WriteLine("Total Changes Downloaded: " + syncStats.DownloadChangesTotal);
            Console.WriteLine("Complete Time: " + syncStats.SyncEndTime);
            Console.WriteLine(String.Empty);
        }

        static void Program_ApplyChangeFailed(object sender, DbApplyChangeFailedEventArgs e)
        {
            // display conflict type
            Console.WriteLine(e.Conflict.Type);

            // display error message 
            Console.WriteLine(e.Error);
        }
    }
}

Walkthrough: Executing Synchronization with Second Client

To execute synchronization

Complete Code Example

See Also

Concepts

Additional resources