Share via

Tutorial: Synchronizing SQL Server and SQL Server Compact [Sync Framework]

  • Article

This tutorial provides step-by-step instructions for implementing data synchronization between a SQL Server database and a SQL Server Compact database using Sync Framework 2.1. It contains following walkthroughs, which provide detailed steps to perform a specific task within the tutorial.

This topic is a how to.
Please keep it as clear and simple as possible. Avoid speculative discussions as well as a deep dive into underlying mechanisms or related technologies.

Basic Walkthroughs

Walkthrough  Description    
Walkthrough: Creating a simple server database     In this walkthrough you will create a sample SQL Server database that you will use later in a synchronization scenario with a SQL Server Compact database.    
Walkthrough: Defining a scope and provisioning the server database with sync artifacts In this walkthrough you will create a console application that defines a sync scope and provision the SQL Server database that you created in previous walkthrough with scope related artifacts.   
Walkthrough: Provisioning a SQL Compact database In this walkthrough you will create a SQL Server compact database, which acts as a client for the SQL Server database you created earlier in this tutorial, and create a console application that provisions the compact database with sync scope related artifacts.
Walkthrough: Executing synchronization In this walkthrough, you will create a console application that kicks off synchronization process between the SQL Server and SQL Server compact databases you created in previous walkthroughs.

**Walkthrough: Creating a sample server database
**
In this walkthrough, you will create a sample SQL Server database that you will use later to synchronize with a SQL Server compact database. The following list contains the detailed steps to create the sample database.

  1. Launch SQL Server Management Studio: Click Start, point to Programs, point to Microsoft SQL Server 2008, and then click SQL Server Management Studio. Use similar steps if you are using SQL Server 2005 or SQL Server 2008 R2 to launch SQL Server Management Studio for that specific version.

  2. Connect to SQL Server by using appropriate credentials: On the Connect to Server dialog box, select Database engine for Server type, select a SQL Server instance for Server name, and use appropriate authentication settings to connect to the server.

  3. Click New Query on the toolbar or press Ctrl+N to launch a query window.

  4. Copy the following SQL code to the query editor.

    USE [master]  
GO 
   
IF EXISTS(SELECT name FROM sys.databases WHERE name = 'SyncDB')  
    DROP DATABASE SyncDB 
   
CREATE DATABASE [SyncDB]  
GO 
   
USE [SyncDB]  
GO 
   
CREATE TABLE [dbo].[Products]( 
    [ID] [int] NOT NULL,  
    [Name] [nvarchar](50) NOT NULL,  
    [ListPrice] [money] NOT NULL
   
    CONSTRAINT [PK_Products] PRIMARY KEY CLUSTERED ([ID] ASC)  
)  
GO 
   
CREATE TABLE [dbo].[Orders](  
    [OrderID] [int] NOT NULL,  
    [ProductID] [int] NOT NULL,  
    [Quantity] [int] NOT NULL,  
    [OriginState] [nvarchar](2) NOT NULL,  
    CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED ([OrderID] ASC,[ProductID] ASC)  
)  
GO 
   
ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Products] FOREIGN KEY([ProductID])  
REFERENCES [dbo].[Products] ([ID])  
GO 
   
ALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Products]  
GO 
   
INSERT INTO Products VALUES (1, 'PC', 400)  
INSERT INTO Products VALUES (2, 'Laptop', 600)  
INSERT INTO Products VALUES (3, 'NetBook', 300)  
   
INSERT INTO Orders VALUES (1, 1, 2, 'NC')  
INSERT INTO Orders VALUES (2, 2, 1, 'NC')  
INSERT INTO Orders VALUES (3, 1, 5, 'WA')  
INSERT INTO Orders VALUES (3, 3, 10, 'WA')  
INSERT INTO Orders VALUES (4, 2, 4, 'WA')

  5. Press F5 to execute the query.

  6. In the Object Explorer pane, right-click <database name> and click Refresh. Expand Databases, and confirm that SyncDB database is created with two tables: dbo.Products and dbo.Orders.

  7. Keep SQL Server Management Studio open  to perform next walkthrough.

Walkthrough: Defining a scope and provisioning the server database with sync related artifacts

To prepare the server database for synchronization, you will need to describe a sync scope and provision the server database with scope related artifacts.
By describing a sync scope, you define what you want to synchronize. A sync scope is a set of tables that must be synchronized as a single unit. The tables can already exist in the database or they can be described by using the Sync Framework object model and then be generated at runtime when the underlying store is provisioned. In this walkthrough, you will use the Products table that already exists in the server database.

The provisioning of a database involves adding sync scope related artifacts such as tracking tables, triggers, and stored procedures to the database. These artifacts are used by the synchronization process at runtime. Optionally, the base table is also added to the database as specified in the previous paragraph. For in-depth technical details about provisioning, see How To: Execute Database Synchronization (SQL Server) and Provisioning for Synchronization (SQL Server).

In this walkthrough you will create a console application that defines a sync scope named ProductsScope, which includes the Products table, and provisions the SQL Server database that you created in previous walkthrough with sync scope related artifacts.

  1. Launch Visual Studio 2008 or Visual Studio 2010: Click Start, point to Programs, point to Microsoft Visual Studio 2008 or Microsoft Visual Studio 2010, and then click Microsoft Visual Studio 2008 or Microsoft Visual Studio 2010.

  2. Click File on menu bar, point to New, and click Project.

  3. Select Visual C# from Project Types, and select Console Application from Templates.

  4. Type ProvisionServer for the project name, C:\ for Location, and SyncSQLServerAndSQLCompact for Solution Name.

  5. Click OK to close the New Project dialog box.

  6. In the Solution Explorer window, right-click ProvisionServer, and click Add Reference.

  7. Select Microsoft.Synchronization.Data and Microsoft.Synchronization.Data.SqlServer on the .NET tab. These are the assemblies shipped with Microsoft Sync Framework.

  8. Click OK to close the Add Reference dialog box.

  9. Add the following using statements to the beginning of the Program.cs file after the existing using statements. These namespaces contains the classes that you will be using in the code for this console application.

    using System.Data.SqlClient; 
using Microsoft.Synchronization.Data; 
using Microsoft.Synchronization.Data.SqlServer;

  10. Add the following statement to the Main method to create a connection to the SyncDB server database. 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.

    SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True");

  11. Add the following code to the Main method to define a sync scope. This code creates the ProductsScope sync scope, gets the description of Products table in the SyncDB database, and adds the description to the ProductsScope. The high level steps for defining a sync scope are:

    1. Create an instance of the Microsoft.Synchronization.Data.DbSyncScopeDescription class. The Microsoft.Synchronization.Data.DbSyncScopeDescription class is used to specify the name of the sync scope and the list of tables to be synchronized. The tables are specified using the Microsoft.Synchronization.Data.DbSyncTableDescription class.

    2. Create an instance of the DbSyncTableDescription class based on the schema of Products table retrieved from the SyncDB server database. The DbSyncTableDescription class is used to specify the name of the table, columns of the table to be synchronized, data types of the table, and other information that is required for the sync. This information can be specified explicitly or it can be obtained by querying the database using the GetDescriptionForTable(String, SqlConnection) method. In this walkthrough, you will use the GetDescriptionForTable(String, SqlConnection) method of the SqlSyncDescriptionBuilder class to retrieve the description of the table

    3. Add the DbSyncTableDescription object to Tables collection of the **DbSyncScopeDescription **object using the Add method.

      // define a new scope named ProductsScope 
DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("ProductsScope"); 
  
// get the description of the Products table from SyncDB dtabase 
DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Products", serverConn); 
  
// add the table description to the sync scope definition 
scopeDesc.Tables.Add(tableDesc);

  12. Add the following code to the Main method to provision the SyncDB server database with sync related artifacts. This code creates a Microsoft.Synchronization.Data.SqlServer.SqlSyncScopeProvisioning object, skips the creation of Products table on the server since the table already exists, and provisions the SyncDB database with scope related artifacts. The high level steps for provisioning the server database with sync scope related artifacts are:

    1. Create an instance of the Microsoft.Synchronization.Data.DbSyncScopeDescription class. The Microsoft.Synchronization.Data.DbSyncScopeDescription class is used to specify the name of the sync scope and the list of tables to be synchronized. The tables are specified using the **Microsoft.Synchronization.Data.DbSyncTableDescription **class.

    2. Invoke the SetCreateTableDefault(DbSyncCreationOption) method by specifying the DbSyncCreationOption value as Skip because the Products table already exists in the server database. The SetCreateTableDefault(DbSyncCreationOption) method is used to specify whether to create base tables when a scope is configured.

    3. Invoke the Apply() method on SqlSyncScopeProvisioning object to start the provisioning process, which creates the change-tracking infrastructure in the server database.

      // create a server scope provisioning object based on the ProductScope 
SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc); 
  
// skipping the creation of table since table already exists on server 
serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip); 
  
// start the provisioning process 
serverProvision.Apply();

  13. In Solution Explorer, right-click ProvisionServer, and click Build.

  14. Press Ctrl+F5 to execute the program.

  15. Press ENTER to close the command prompt window.

  16. In SQL Server Management Studio, expand SyncDB node, expand Tables, and you should see the following additional tables created by the provisioning process: Products_Tracking, schema_info, scope_config, and scope_info. There are also other database objects such as triggers and stored procedures created by the provisioning process.

  17. Keep Visual Studio open and SQL Server Management open. 

  18. **Complete Code Example: ** 

    using System.Data.SqlClient; 
using Microsoft.Synchronization.Data; 
using Microsoft.Synchronization.Data.SqlServer; 
  
namespace ProvisionServer 
{ 
    class Program 
    { 
        static void Main(string[] args) 
        { 
            SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True"); 
  
            // define a new scope named ProductsScope 
            DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("ProductsScope"); 
  
            // get the description of the Products table from SyncDB dtabase 
            DbSyncTableDescription tableDesc = SqlSyncDescriptionBuilder.GetDescriptionForTable("Products", serverConn); 
  
            // add the table description to the sync scope definition 
            scopeDesc.Tables.Add(tableDesc); 
  
            // create a server scope provisioning object based on the ProductScope 
            SqlSyncScopeProvisioning serverProvision = new SqlSyncScopeProvisioning(serverConn, scopeDesc); 
  
            // skipping the creation of table since table already exists on server 
            serverProvision.SetCreateTableDefault(DbSyncCreationOption.Skip); 
  
            // start the provisioning process 
            serverProvision.Apply(); 
        } 
    } 
}

Walkthrough: Provisioning a SQL Compact client database
In this walkthrough you will create a SQL Server Compact database named SyncCompactDB, and create a console application that provisions the compact database with ProductsScope related artifacts. The provision process prepares the client database for synchronization with the server. In the previous walkthrough, you provisioned the server database with sync related artifacts already.

**To create a SQL Server Compact database
The following list contains steps to create the SyncCompactDB compact database, which will be used as a client for the SyncDB server database. **
**
**

  1. In SQL Server Management Studio, click File menu, and click Connect Object Explorer.
  2. In the Connect to Server dialog box, select SQL Server Compact Edition for Server type.
  3. Click down-arrow next to Database file, and click New Database.
  4. In the Create New SQL Server Compact Database dialog box, type C:\SyncSQLServerAndSQLCompact\SyncCompactDB.sdf for Database file name, and click OK.
  5. Click Yes on the message box you see about the blank password
  6. Click Connect on the Connect to Server dialog box.

To provision SQL Server Compact database

The following list contains steps to create a console application using Visual Studio to provision the SQL Server Compact database SyncCompactDB with artifacts related to sync scope ProductsScope.

  1. In Visual Studio, In Solution Explorer, right-click Solution ‘SyncSQLServerAndSQLCompact’, point to Add, and click New Project.

  2. Select Visual C# from Project Types, and select Console Application from Templates.

  3. Type ProvisionClient for project name.

  4. Click OK to close the New Project dialog box.

  5. In Solution Explorer window, right-click ProvisionClient, and click Add Reference.

  6. Select Microsoft.Synchronization.Data, Microsoft.Synchronization.Data.SqlServer, Microsoft.Synchronization.Data.SqlServerCe and click OK to close the Add Reference dialog box.

  7. Repeat previous two steps to add a reference to System.Data.SqlServerCe assembly.

  8. 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.Data; 
using Microsoft.Synchronization.Data.SqlServer; 
using Microsoft.Synchronization.Data.SqlServerCe;

  9. Add the following statement to the Main method to create a SQL connection to the compact database.

    // create a connection to the SyncCompactDB database 
SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\SyncSQLServerAndSQLCompact\SyncCompactDB.sdf'");

  10. Add the following statement to the Main method to create a SQL connection to the server database. 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.

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

  11. Add the following statement to the Main method to get the description of the ProductsScope scope from the SQL Server. This statement invokes the SqlCeSyncDescriptionBuilder.GetDescriptionForScope method on the Microsoft.Synchronization.Data.SqlServer.SqlSyncDescriptionBuilder class to retrieve description of the ProductsScope from server.
    The Microsoft.Synchronization.Data.DbSyncScopeDescription class is used to specify the name of the sync scope and the list of tables to be synchronized. This information can be specified explicitly or it can be obtained by querying the database using the SqlSyncDescriptionBuilder.GetDescriptionForScope method. In this walkthrough, you will use the SqlSyncDescriptionBuilder.GetDescriptionForScope method of the Microsoft.Synchronization.Data.SqlServer.SqlSyncDescriptionBuilder class to retrieve the description of the scope from the server.

    // get the description of ProductsScope from the SyncDB server database 
DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("ProductsScope", serverConn);

  12. Add the following statements to provision the SQL Server compact database with the ProductsScope related artifacts. The high level steps for provisioning a SQL Server Compact database with sync scope related artifacts are:

    1. Create an instance of the Microsoft.Synchronization.Data.SqlServerCe.SqlCeSyncScopeProvisioning class based on the Microsoft.Synchronization.Data.DbSyncScopeDescription obtained in the previous step and a connection to the compact database. The Microsoft.Synchronization.Data.SqlServerCe.SqlCeSyncScopeProvisioning class represents the provisioning of a SQL Server Compact database for a particular scope that is represented by a Microsoft.Synchronization.Data.DbSyncScopeDescription object.

    2. Invoke the Apply method on SqlCeSyncScopeProvisioning object to start the provisioning process, which creates the change-tracking infrastructure in the compact database.

      // create CE provisioning object based on the ProductsScope 
SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc); 
  
// starts the provisioning process 
clientProvision.Apply();

  13. In Solution Explorer, right-click ProvisionClient, and click Build.

  14. In Solution Explorer, right-click ProvisionClient again, and click Set as Startup Project. If you do not set the current project as a startup project and press Ctrl+F5 again, the ProvisionServer application is executed again, and you get an error message similar to the following: “scope that already exists in the server database”.

  15. Press Ctrl+F5 to execute the program.

  16. Press ENTER to close the command prompt window.

  17. In SQL Server Management Studio, expand SQL Server Compact [My Computer\..\SyncCompactDB] node, expand Tables, and you should see the following additional tables created by the provisioning process: Products_Tracking, schema_info, scope_config, and scope_info. There are other objects such as triggers and stored procedures created by the provisioning process

  18. Keep Visual Studio and SQL Server Management Studio open.

  19. Complete Code Example:

    using System.Data.SqlClient; 
using System.Data.SqlServerCe; 
  
using Microsoft.Synchronization.Data; 
using Microsoft.Synchronization.Data.SqlServer; 
using Microsoft.Synchronization.Data.SqlServerCe; 
  
namespace ProvisionClient 
{ 
    class Program 
    { 
        static void Main(string[] args) 
        { 
            // create a connection to the SyncCompactDB database 
            SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\SyncSQLServerAndSQLCompact\SyncCompactDB.sdf'"); 
  
            // create a connection to the SyncDB server database 
            SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True"); 
  
            // get the description of ProductsScope from the SyncDB server database 
            DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope("ProductsScope", serverConn); 
  
            // create CE provisioning object based on the ProductsScope 
            SqlCeSyncScopeProvisioning clientProvision = new SqlCeSyncScopeProvisioning(clientConn, scopeDesc); 
  
            // starts the provisioning process 
            clientProvision.Apply(); 
  
        } 
    } 
}

Executing Synchronization
In previous two walkthroughs you prepared the server database SyncDB and the compact database SyncCompactDB for synchronization. In this walkthrough, you will create a console application that kicks off synchronization process between these two databases. For in-depth technical details about provisioning servers/clients and executing the synchronization process, see How To: Execute Database Synchronization (SQL Server).

  1. In Visual Studio, In Solution Explorer, right-click Solution ‘SyncSQLServerAndSQLCompact’, point to Add, and click New Project.

  2. Select Visual C# from Project Types, and select Console Application from Templates.

  3. Type ExecuteCompactSync for project name.

  4. Click OK to close the New Project dialog box.

  5. In Solution Explorer window, right-click ExecuteCompactSync, and click Add Reference.

  6. Select Microsoft.Synchronization, Microsoft.Synchronization.Data, Microsoft.Synchronization.Data.SqlServer, Microsoft.Synchronization.Data.SqlServerCe and click OK to close the Add Reference dialog box.

  7. Repeat previous two steps to add a reference to System.Data.SqlServerCe assembly.

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

    using System.Data; 
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;

  9. Add the following statement to the Main method to create a SQL connection to the compact database.

    // create a connection to the SyncCompactDB database 
SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\SyncSQLServerAndSQLCompact\SyncCompactDB.sdf'");

  10. Add the following statement to the Main method to create a SQL connection to the server database. 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.

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

  11. 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 compact client database. The high level steps of creating an orchestrator for this scenario are:

    1. Create an instance of the Microsoft.Synchronization.SyncOrchestrator class. The SyncOrchestrator class initiates and controls synchronization sessions.

    2. Set the local provider of the sync orchestrator object to a Microsfot.Synchronization.Data.SqlServerCe.SqlCeSyncProvider object associated with the SyncCompactDB 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 Microsoft.Synchronization.Data.SqlServer.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 SyncDirectionOrder.UploadAndDownload so that the client can download/upload changes from/to the server.

      // create the sync orhcestrator 
SyncOrchestrator syncOrchestrator = new SyncOrchestrator(); 
  
// set local provider of orchestrator to a CE sync provider associated with the  
// ProductsScope in the SyncCompactDB compact client database 
syncOrchestrator.LocalProvider = new SqlCeSyncProvider("ProductsScope", clientConn); 
  
// set the remote provider of orchestrator to a server sync provider associated with 
// the ProductsScope in the SyncDB server database 
syncOrchestrator.RemoteProvider = new SqlSyncProvider("ProductsScope", serverConn); 
  
// set the direction of sync session to Upload and Download 
syncOrchestrator.Direction = SyncDirectionOrder.UploadAndDownload;

  12. Add the following code to subscribe for any errors that occur when applying changes to the client. The SqlCeClientSyncProvider.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);

  13. Add the following code to the Main method. This code invokes the Synchronize method on the SyncOrchestrator object to start the synchronization process between the SyncDB server database and SyncCompactDB compact database.

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

  14. Add the following statements to the Main method to display statistics returned by the SyncOrchestrator.Synchronize method. The Microsoft.Synchronization.SyncOperationStatistics object returned by this method contains statistics about the synchronization session that was executed.

    // print 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);

  15. 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); 
}

  16. In Solution Explorer, right-click ExecuteCompactSync, and click Build.

  17. In Solution Explorer, right-click ExecuteCompactSync again, and click Set as Startup Project. If you do not perform this step and you press Ctrl+F5 again, the ProvisionClient application is executed again, and you get an error message about the scope that already exists in the client database.

  18. Press Ctrl+F5 to execute the program. You should see the following output. Note that 3 records were downloaded and added to the client database.

      Start Time: 6/14/2010 6:24:19 PM 
Total Changes Uploaded: 0 
Total Changes Downloaded: 3 
Complete Time: 6/14/2010 6:24:22 PM 
   
Press any key to continue . . .

  19. Press ENTER to close the command prompt window.

  20. In SQL Server Management Studio, right-click SQL Server Compact [My Computer\..\SyncCompactDB] node, and click New Query to open a query window

  21. Type and execute (by pressing F5) the following SQL command to confirm that records are indeed downloaded to the compact client.

    select * from products

  22. In the SQL Server Management Studio, expand (local) SQL Server, expand Databases, expand SyncDB, and expand Tables.

  23. Right-click dbo.Products, and click Edit Top 200 Rows.

  24. Add a record at the end with 4 as ID, Wireless Mouse for Name, and 45 for ListPrice. Make sure the record is saved by pressing TAB after entering the last value.

  25. Now, in Visual Studio, press Ctrl+F5 to execute the client program again. You should see output similar to the following output. The one record you added to the server database should be downloaded to the client.

      Start Time: 6/14/2010 6:32:44 PM 
Total Changes Uploaded: 0 
Total Changes Downloaded: 1 
Complete Time: 6/14/2010 6:32:47 PM 
   
Press any key to continue . . .

  26. In SQL Server Management Studio, select SQL Server Compact [My Computer\..\SyncCompactDB] node.

  27. Click New Query from the toolbar.

  28. Type and execute (by pressing F5) the following SQL command to confirm that records are indeed downloaded to the compact client.

    select * from products

  29. 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.

  30. Keep Visual Studio and SQL Server Management Studio open if you want to perform walkthroughs that follow this walkthrough in the tutorial.

  31. Complete Code Example:

    using System; 
using System.Data; 
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 ExecuteCompactSync 
{ 
    class Program 
    { 
        static void Main(string[] args) 
        { 
            // create a connection to the SyncCompactDB database 
            SqlCeConnection clientConn = new SqlCeConnection(@"Data Source='C:\SyncSQLServerAndSQLCompact\SyncCompactDB.sdf'"); 
  
            // create a connection to the SyncDB server database 
            SqlConnection serverConn = new SqlConnection("Data Source=localhost; Initial Catalog=SyncDB; Integrated Security=True"); 
  
            // create the sync orhcestrator 
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator(); 
  
            // set local provider of orchestrator to a CE sync provider associated with the  
            // ProductsScope in the SyncCompactDB compact client database 
            syncOrchestrator.LocalProvider = new SqlCeSyncProvider("ProductsScope", clientConn); 
  
            // set the remote provider of orchestrator to a server sync provider associated with 
            // the ProductsScope in the SyncDB server database 
            syncOrchestrator.RemoteProvider = new SqlSyncProvider("ProductsScope", serverConn); 
  
            // set the direction of sync session 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 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); 
        } 
    } 
}