# Sync SQL Server 2012 Data with SQL CE 3.5 SP2 over the Internet using WCF Services using MS Sync Framework v2.1 **by:** Saurabh Nandu **in:** [Programming](https://www.systenics.com/blogs/category/programming) **tags:** [*.NET*](https://www.systenics.com/blogs/tag/net) , [*SQL CE 3.5 SP2*](https://www.systenics.com/blogs/tag/sql-ce-35-sp2) , [*SQL Server*](https://www.systenics.com/blogs/tag/sql-server) , [*Sync Framework 2.1*](https://www.systenics.com/blogs/tag/sync-framework-21) , [*WCF*](https://www.systenics.com/blogs/tag/wcf) , [*WPF*](https://www.systenics.com/blogs/tag/wpf) ## Overview Recently, we were working on a client project where the client required multiple line of business Windows Based Client Applications to be able to work offline and synchronize with the master MS SQL Database (hosted on the internet), when internet connection is available. We faced several challenges in building this application and I thought it would be a good idea to blog the solution we created for the client. In this post we have tried to build upon the samples provided by Microsoft but explain the whole process so that its easier for others to understand and implement this platform. This blog post does not try to explain in detail how sync framework works, but tries to explain the creation of WCF based web service to sync between server and clients over the internet. The particular client that we were working for had employees who used to travel extensively and needed data at least refreshed daily. Internet connection could not be guaranteed at all times so creating a web/mobile based application was ruled out. There are significantly large number of businesses in developing countries like India who still don’t have access to reliable Internet connections, we have no choice but to build Windows desktop based applications for them. The best solution for this business problem was to create Windows based applications which would sync data with a central server hosted on the internet. ## Technology Selection Over the years there has been a much greater push of new technologies built for the web and mobile devices, but there seems to be much slower pace of development on the Windows application development side from Microsoft. The choice of technology for building Windows based application is WPF (Windows Presentation Framework), but the pace of development on WPF is certainly way behind the pace of development on the web technologies side. The state of technologies that allow synchronization between client and server is bad and totally confusing! The sync framework has not advanced in ages, while there is no alternative path presented. We are stuck to using Sync Framework v2.1, released in last quarter of 2010 for our solution since we wish to have a framework that provides automatic synchronization between SQL CE and SQL Server databases. The latest, Sync Framework 4 builds upon Sync Framework 2.1 and provides OData based synchronization so that you can synchronize between device platforms without installing any framework on the client platform, but it does not provide any features for automatic sync between client and server db. If you want to use Sync Framework 4 then you need to manually handle the client side sync code. The only highlight for this framework is that Microsoft is promoting it as the means to synchronize with Azure cloud databases, so there are some updated samples provided. The other major issue with using Sync Framework 2.1 is that it depends upon SQL CE 3.5 SP2 and does NOT work with the latest SQL CE 4.0. For reasons best known to Microsoft they have chosen to release SQL CE 4.0 without support for synchronization, so we are stuck to using SQL CE 3.5 SP2. Tooling support in Visual Studio 2012 is built to support SQL CE 4.0 and does not work with SQL CE 3.5. On the server side, we will expose the sync framework using WCF (Windows Communication Framework) web services. The core reason behind this blog post is that the sample presented by Microsoft is not well documented nor is it clear how to deploy the sample application. In this blog post we are building upon the sample provided by Microsoft but making things clear to understand. The default WCF sample provided by Microsoft is built under the assumption that the WCF service will be self-deployed, while we have modified the sample to deploy the service in IIS. To summarize technologies used, on client side we will use WPF, SQL CE 3.5 Sp2 and MS Sync Framework v2.1. On the server side we will use WCF web services, MS SQL 2012 (you can use MS SQL 2008/Express does not matter) and MS Sync Framework v2.1. We will be using Visual Studio 2012 and MS SQL 2012 for building this application. ## Installing SDK’s and Frameworks Before we begin development we need to have the following SDK’s and Frameworks installed if they are missing: [Microsoft Sync Framework 2.1 Software Development Kit (SDK)](http://www.microsoft.com/en-us/download/details.aspx?id=23217) - SyncSDK-v2.1-x64-ENU.msi or SyncSDK-v2.1-x86-ENU.msi. Its very important to remember to install the right flavor of the SDK based on your development machine. Make sure you have the 64-bit version installed for a 64-bit machine else the sync framework will not work correctly. [Microsoft SQL Server Compact 3.5 Service Pack 2 for Windows Desktop](http://www.microsoft.com/en-us/download/details.aspx?id=5783) - SSCERuntime-ENU.exe. Please read the information provided on the download page. The above file contains two msi installers. On a 32 bit machine only install the SSCERuntime_x86-ENU.msi file. But on a 64 bit machine you need to install BOTH SSCERuntime_x86-ENU.msi as well as SSCERuntime_x64-ENU.msi files as per the instructions provided. Even if you install this version over SQL CE 4.0, both versions run side-by-side and do not cause any issues. [Microsoft Sync Framework v4.0 Toolkit](http://code.msdn.microsoft.com/Sync-Framework-Toolkit-4dc10f0e) (optional) This is an optional download, we use a particular tool provided in this toolkit for provisioning the database the support synchronization. Please read the download notes to install the pre-requisites required to build these samples. We have provided a pre-built version of the tool in the downloads. [SQL Server Compact Toolbox](http://sqlcetoolbox.codeplex.com/releases) (optional) As mentioned above, Visual Studio 2012 does not connect to SQL CE 3.5 SP2. This Visual Studio 2012 add-in is very useful to help you create and manage SQL CE 3.5 databases from within Visual Studio. ## Getting Started – Database Design The WCF service sample that we will be providing will be independent of any particular application. To explain the working of the code base we are taking a simple application where there are 3 tables Products, Employees and EmployeeSales. The Database diagram for the same is shown below: Figure 1: Database diagram In the attached download file, you can extract the db folder to find a script **SampleSync.sql** as well as backup of the database **SampleSync.bak** (Note: the database backup will only restore to SQL Server 2012, if you have a lower version please apply the script to create the database). You can use the script file to create the above database schema. We are using a very simplified database schema with just 3 table, the schema is pretty self-explanatory. **Products** table will have a list of products that are defined on the server and only fetched on the client end. No updates to products will be made on the client end. **Employees** table too defines the employees on the server, no changes are made to them on the client. Ideally it would contain Employee login information too so that you can automatically determine which employee is making the sale. Finally, we have the **EmployeSales** table which will record the sales of Employee’s, this table will be updated on both client and server and will have to be synchronized both ways. One of the most important factor in designing a database which needs synchronization is to make sure that the primary key is set to **uniqueidentifier** data type. Its crucial since records can be generated on both client and server side and there will be issues in making sure they are unique if you do not use **uniqueidentifier** as the data type. ## Provisioning the Database Before we can set up synchronization we need to provision the MS SQL server side database to support Sync Framework. There are several different ways of doing it and its beyond the scope of this blog post. We choose to use the **SyncSvcUtilUI** tool provided in the Sync Toolkit v4.0 since it provides a visual interface for setting up synchronization. If can setup synchronization using which ever method you prefer, it does not affect the wcf service code. We have provided a pre-built version of the tool in the **SyncSvcUtilUI** folder. There are two scopes we wish to define for our application as given below: - **ProductsEmployees** – This scope will contain the **Products** and **Employees** table and will be used to push data from the server to the client. No changes to these tables will be synced to the server, as per the specifications discussed above. - **EmployeeSales** – This scope will contain filtered scope for **EmployeeSales** table. This scope will allow changes to be synced both ways, to and from the server. Start the **SyncSvcUtilUI** tool and click on **Generate or Edit Sync Configuration** link. Provide the file path to save the sync configuration file with name **SyncConfig** and click **Next** as shown in figure 2. Figure 2: Create a new Sync Configuration file. On the next screen first click the **Add** button to add a new targeted database. Provide the **Config Name** as **SyncSample**, **Db Server** as your SQL Server name, **Db Name** as **SyncSample** and then select **Windows Authentication** or **SQL Authentication** based on your SQL Server configuration. Most important, after you enter the settings click on the **Save** button, if you do not click on the save button then the wizard will not save the values if you just click the **Next** button. Once the database setting is saved click on the **Next** button. Figure 3 shows the database settings. Figure 3: Add database On the Select Scope screen first click on the Add button to add a new scope. We need to define the two scopes as defined above, so provide the Scope Name as **ProductsEmployees** for the first scope and select **Enable Apply Bulk Procedures** checkbox and click **Save** button as seen in Figure 4. The reason why we select Enable Bulk Apply procedures is that it speeds up the synchronization process significantly when we use Bulk Procedures, you could alternatively leave it unchecked if that’s your requirement, it does not affect our services implementation. Figure 4: Add ProductsEmployees scope On the same screen click Add button again to add another scope. Provide the Scope Name as **EmployeeSales** and select **Enable Apply Bulk Procedures** checkbox and click **Save** button as seen in Figure 5. Then click the **Next** button to proceed further. Figure 5: Add EployeeSales scope On the** Select Sync Tables** screen, first select **ProductsEmployees **from the **Select Scope** dropdown. Then click the **Add/Edit** button to open the popup. Select the **SyncSample** database from **Select Database** dropdown. From the list of tables select **Products** and **Employees** table and click **Ok** to close the pop-up and add the tables to the scope. Figure 6: Add tables to ProductsEmployees scope Change the Select Scope dropdown to **EmployeeSales** and click on the **Add/Edit** button again to open table select pop-up. Select **SyncSample** database from the **Select Database** dropdown to populate the tables list. Select the **EmployeeSales** table from the Tables list and click **Ok** to close the pop-up and add the table to scope. Click **Next** to continue after adding tables to both the scope. The last screen shows the XML generated which defines the configuration made by us. Click on the **Finish** button to save the scope configuration and close this wizard. Figure 7 shows the table added to the scope. Figure 7: Define Tables for Scope On the main application screen now select **Provision or Deprovision** to start a new Wizard. On the Settings screen, select the SyncSample.config file that was created previously. This step will actually run scripts on the database to provision our tables to support synchronization. Once the file is loaded, select **ProductEmployees** from the **Select Sync Scope** dropdown and click **Next** button. This will automatically start modifying the database and the status will be shown on the screen. Once the process finishes click on Back button again so that we can provision the other scope too. In case you get an “Invalid Parameter” error while provisioning, please move the sync config file created in steps above to the root drive and then follow the process again. There is a bug in this tool, where if there are any spaces in the path to the sync config file the provisioning fails (*internally this UI application is passing parameters to the command line syncsvcutil application and they are not encapsulating string parameters in quotations, hence if the path contains spaces the command line applications throws an error.*). Figure 8 shows the sync scope selection. Figure 8: Select sync scope for provision Once the previous provisioning is completed, select **EmployeesSales** from **Select Sync Scope** dropdown and click **Next** button to start provisioning for this scope. Once the provisioning is complete, click **Finish** to close the wizard and then close the **SyncSvcUtilUI** application. Our database provisioning process is complete, you can examine the database in SQL management studio to find extra tables and triggers added to track any changes that are made to the synced tables. This completes the database provisioning step. Figure 9 shows the completed provisioning step. Figure 9: Finish Sync Scope provisioning ## Creating Visual Studio 2012 Projects ### 1) Service Interface Fire up Visual Studio 2012 and start a new Project of type Class Library. Enter the project name as **ServiceInterface** and the solution name as **SyncSample** and click **OK** to create the project. Figure 10: Create new Console Project The **ServiceInterface** project will contain a single Interface which will be implemented by both the WCF Web service as well as the client proxy will use the service interface to invoke the web service manually. The interface can also be defined in the web service project and does not need a special project defined (as shown in the Microsoft Samples), but then you would have to reference the web service assembly in your client project exposing your business logic to the client machine which is not a good security decision. In the solution explorer select the default **Class1.cs** and delete it. Then right-click the project name in the solution explorer and select **Add** –> Add **New Item** from the context menu. Select **Interface** from Add new Item dialog and provide the file name **ISyncService.cs** and click **OK** to create the new interface. Before we get started with coding the interface we need to add references to couple of assemblies. In solution explorer, right click **References** and select **Add Reference** from the context menu. Select **Framework** under **Assemblies**, to list all the framework assemblies. Then check assemblies **System.ServiceModel** and **System.Runtime.Serialization** to select them. Next, we need to references to the Sync Framework assemblies. Its important to note that Sync Framework needs platform specific assemblies so if you are working on a 64-bit machine you need to references 64 bit assemblies or if you are on a 32 bit machine then you need to use 32 bit assemblies. Its important to ensure that you check the deployment web server and build platform specific assemblies, else the Sync Framework will not work. Select Browse on the **Add Reference** dialog, navigate to the path where the Sync Framework is installed to reference **Microsoft.Synchronization.dll** and **Microsoft.Synchronization.Data.dll**. On a 64 bit machine these assemblies can be found in the **C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\x64** and** C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x64** folders respectively. If you go one level up you would find the x86 folder containing the 32-bit version of the assemblies. If you are developing on a 32 bit machine then look within the **C:\Program Files\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\x86** and **C:\Program Files\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x86** folders to find these assemblies. Click **OK** to reference the two assemblies in your project. Now copy paste code listing 1 below into the ISyncSerivce.cs interface, save the file and compile the project. ``` using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.ServiceModel; using System.Runtime.Serialization; using System.Data; using Microsoft.Synchronization.Data; using Microsoft.Synchronization; namespace ServiceInterface { [ServiceContract(SessionMode = SessionMode.Required)] // Important attribute [ServiceKnownType(typeof(SyncIdFormatGroup))] [ServiceKnownType(typeof(DbSyncContext))] [ServiceKnownType(typeof(SyncSchema))] [ServiceKnownType(typeof(WebSyncFaultException))] [ServiceKnownType(typeof(SyncBatchParameters))] [ServiceKnownType(typeof(GetChangesParameters))] public interface ISyncService { // Defines the first method to call when using SessionMode [OperationContract(IsInitiating = true)] void Initialize(string scopeName); [OperationContract] DbSyncScopeDescription GetScopeDescription(); [OperationContract] void BeginSession(SyncProviderPosition position); [OperationContract] SyncBatchParameters GetKnowledge(); [OperationContract] GetChangesParameters GetChanges(uint batchSize, SyncKnowledge destinationKnowledge); [OperationContract] SyncSessionStatistics ApplyChanges(ConflictResolutionPolicy resolutionPolicy, ChangeBatch sourceChanges, object changeData); [OperationContract] bool HasUploadedBatchFile(string batchFileid, string remotePeerId); [OperationContract] void UploadBatchFile(string batchFileid, byte[] batchFile, string remotePeerId); [OperationContract] byte[] DownloadBatchFile(string batchFileId); [OperationContract] void EndSession(); //Indicates the last method to call when use SessionMode [OperationContract(IsTerminating = true)] void Cleanup(); } [DataContract] public class SyncBatchParameters { [DataMember] public SyncKnowledge DestinationKnowledge; [DataMember] public uint BatchSize; } [DataContract] [KnownType(typeof(DataSet))] public class GetChangesParameters { [DataMember] public object DataRetriever; [DataMember] public ChangeBatch ChangeBatch; } [DataContract] public class WebSyncFaultException { public string message; public Exception innerException; public WebSyncFaultException(string message, Exception innerException) { this.message = message; this.innerException = innerException; } [DataMember] public string Message { get { return this.message; } set { this.message = value; } } [DataMember] public Exception InnerException { get { return this.innerException; } set { this.innerException = value; } } } } ``` Listing 1: ISyncService.cs The code for Listing 1 is from the sample provided by Microsoft, it’s a standard interface definition with methods defined which match the methods called during Synchronization. One key point to note here is the **[ServiceContract(SessionMode = SessionMode.Required)]** attribute on line 13. This attribute forces the web service to enable sessions on server, but its different from ASP.NET sessions. Instances of the WCF web service class which will implement the **ISyncService** interface will remain in memory and will service the same client call. This way objects that are created on the server side in the **Initialize** method, will remain active on the server to respond to further service calls until the **CleanUp** method is called by the client explicitly. Another benefit of enabling Sessions is that WCF guarantees the method calls will respond in the order they were called, making the service behave like a native class rather than a stateless service. The obvious drawback of this is that service instances end up taking more memory on the server while they are executing as compared to stateless services. But this seems to be the only way to get Sync Framework to work on the internet transparently. Besides the **ISyncService** interface there are 3 data classes defined which are basically used to serialize data and exceptions over the wire. ### 2) Web Service Project In solution explorer right click the solution name and **Add –> New Project** from the context menu. Select the **WCF Service Application** template, provide the project name as **SyncService** and click **OK** to add the new project to our solution. In solution explorer select and delete the default IService1.cs as well as Service1.svc files. Again in the solution explorer right click **References** and select **Add Reference** from the context menu. In the **Add Reference** dialog select **Solution** – > **Projects** and select the **ServiceInterface** checkbox to add reference to the project. Select Browse on the **Add Reference** dialog, navigate to the path where the Sync Framework is installed to reference **Microsoft.Synchronization.dll,** **Microsoft.Synchronization.Data.dll **and **Microsoft.Synchronization.Data.SqlServer.dll **. On a 64 bit machine these assemblies can be found in the **C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\x64** and** C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x64** folders respectively. If you go one level up you would find the x86 folder containing the 32-bit version of the assemblies. If you are developing on a 32 bit machine then look within the **C:\Program Files\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\x86** and **C:\Program Files\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x86** folders to find these assemblies. Click **OK** to reference the assemblies in your project. Once the references have been added we need to reference the database in the web.config file. In solution explorer, double click on the web.config file to open it and add the connection string section to it as shown in Listing 2. We are using Windows authentication to connect to the local SQL Server instance, you can modify the connection string to correctly point to your database server. ``` ``` Listing 2: Connection String setting in web.config file. Once database connection has been set, let’s add code to this project. Right click the project name in solution explorer and select **Add** –> **Class**. Provide the class name as **ServerSynchronizationHelper.cs** and click **OK **to create the class. This class is a helper class used to connect with the SQL Server database. Copy paste the code from listing 3 below to populate the code for the class. The code for this class is also extracted from the Microsoft Sample, but its refined to remove all CE client provider’s reference. The code below is a simple class which creates and configures an instance of **SqlSyncProvider** object using the provided connection string and scope name. ``` using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Data; using System.IO; using Microsoft.Synchronization; using Microsoft.Synchronization.Data; using Microsoft.Synchronization.Data.SqlServer; using System.Configuration; namespace SyncService { public class ServerSynchronizationHelper { string conString = ConfigurationManager.ConnectionStrings["DBConnection"].ConnectionString; /// /// Configure the SqlSyncprovider. Note that this method assumes you have a direct /// conection to the server as this is more of a design time use case vs. runtime /// use case. We think of provisioning the server as something that occurs before /// an application is deployed whereas provisioning the client is somethng that /// happens during runtime (on intitial sync) after the application is deployed. /// /// /// public SqlSyncProvider ConfigureSqlSyncProvider(string scopeName) { SqlSyncProvider provider = new SqlSyncProvider(); provider.ScopeName = scopeName; provider.Connection = new SqlConnection(conString); //create anew scope description and add the appropriate tables to this scope DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription(scopeName); //class to be used to provision the scope defined above SqlSyncScopeProvisioning serverConfig = new SqlSyncScopeProvisioning((SqlConnection)provider.Connection); //determine if this scope already exists on the server and if not go ahead //and provision if (!serverConfig.ScopeExists(scopeName)) { //note that it is important to call this after the tables have been added //to the scope serverConfig.PopulateFromScopeDescription(scopeDesc); //indicate that the base table already exists and does not need to be created serverConfig.SetCreateTableDefault(DbSyncCreationOption.Skip); //provision the server serverConfig.Apply(); } return provider; } } } ``` Listing 3: ServerSynchronizationHelper.cs source code Finally, we need to add the WCF Web service to our project. Right click the project name in solution explorer and select **Add** –> **New Item**. In the Add New Item dialog select **WCF Service** and provide the service name as **SqlWebSyncService.svc** and click **OK** to create the WCF web service.Cope paste the code from listing 4 below into the code for the service. This code is again from the Microsoft Sample and it implements the ISyncService contract we defined above. We also need to delete the auto created **ISqlWebSyncService.cs** interface from solution explorer, since we are not using it. ``` using System; using System.Collections.Generic; using System.Text; using Microsoft.Synchronization; using Microsoft.Synchronization.Data; using Microsoft.Synchronization.Data.SqlServer; using System.IO; using System.ServiceModel; using System.Data.SqlClient; using ServiceInterface; using System.ServiceModel.Activation; namespace SyncService { [ServiceBehavior(InstanceContextMode = InstanceContextMode.PerSession)] [AspNetCompatibilityRequirements( RequirementsMode = AspNetCompatibilityRequirementsMode.Required)] public class SqlWebSyncService : ISyncService { protected bool isProxyToCompactDatabase; protected SqlSyncProvider sqlProvider; protected DirectoryInfo sessionBatchingDirectory = null; protected Dictionary batchIdToFileMapper; int batchCount = 0; public void Initialize(string scopeName) { ServerSynchronizationHelper helper = new ServerSynchronizationHelper(); this.sqlProvider = helper.ConfigureSqlSyncProvider(scopeName); this.batchIdToFileMapper = new Dictionary(); } public Microsoft.Synchronization.Data.DbSyncScopeDescription GetScopeDescription() { Log("GetSchema: {0}", this.sqlProvider.Connection.ConnectionString); DbSyncScopeDescription scopeDesc = SqlSyncDescriptionBuilder.GetDescriptionForScope( this.sqlProvider.ScopeName, (SqlConnection)this.sqlProvider.Connection); return scopeDesc; } public void BeginSession(Microsoft.Synchronization.SyncProviderPosition position) { Log("*****************************************************************"); Log("******************** New Sync Session ***************************"); Log("*****************************************************************"); Log("BeginSession: ScopeName: {0}, Position: {1}", this.sqlProvider.ScopeName, position); //Clean the mapper for each session. this.batchIdToFileMapper = new Dictionary(); this.sqlProvider.BeginSession(position, null/*SyncSessionContext*/); this.batchCount = 0; } public SyncBatchParameters GetKnowledge() { Log("GetSyncBatchParameters: {0}", this.sqlProvider.Connection.ConnectionString); SyncBatchParameters destParameters = new SyncBatchParameters(); this.sqlProvider.GetSyncBatchParameters(out destParameters.BatchSize, out destParameters.DestinationKnowledge); return destParameters; } public GetChangesParameters GetChanges(uint batchSize, Microsoft.Synchronization.SyncKnowledge destinationKnowledge) { Log("GetChangeBatch: {0}", this.sqlProvider.Connection.ConnectionString); GetChangesParameters changesWrapper = new GetChangesParameters(); changesWrapper.ChangeBatch = this.sqlProvider.GetChangeBatch(batchSize, destinationKnowledge, out changesWrapper.DataRetriever); DbSyncContext context = changesWrapper.DataRetriever as DbSyncContext; //Check to see if data is batched if (context != null && context.IsDataBatched) { Log("GetChangeBatch: Data Batched. Current Batch #:{0}", ++this.batchCount); //Dont send the file location info. Just send the file name string fileName = new FileInfo(context.BatchFileName).Name; this.batchIdToFileMapper[fileName] = context.BatchFileName; context.BatchFileName = fileName; } return changesWrapper; } public Microsoft.Synchronization.SyncSessionStatistics ApplyChanges( Microsoft.Synchronization.ConflictResolutionPolicy resolutionPolicy, Microsoft.Synchronization.ChangeBatch sourceChanges, object changeData) { Log("ProcessChangeBatch: {0}", this.sqlProvider.Connection.ConnectionString); DbSyncContext dataRetriever = changeData as DbSyncContext; if (dataRetriever != null && dataRetriever.IsDataBatched) { string remotePeerId = dataRetriever.MadeWithKnowledge.ReplicaId.ToString(); //Data is batched. The client should have uploaded this file to us prior to //calling ApplyChanges. //So look for it. //The Id would be the DbSyncContext.BatchFileName which is just the batch //file name without the complete path string localBatchFileName = null; if (!this.batchIdToFileMapper.TryGetValue(dataRetriever.BatchFileName, out localBatchFileName)) { //Service has not received this file. Throw exception throw new FaultException( new WebSyncFaultException("No batch file uploaded for id " + dataRetriever.BatchFileName, null)); } dataRetriever.BatchFileName = localBatchFileName; } SyncSessionStatistics sessionStatistics = new SyncSessionStatistics(); this.sqlProvider.ProcessChangeBatch(resolutionPolicy, sourceChanges, changeData, new SyncCallbacks(), sessionStatistics); return sessionStatistics; } public bool HasUploadedBatchFile(String batchFileId, string remotePeerId) { this.CheckAndCreateBatchingDirectory(remotePeerId); //The batchFileId is the fileName without the path information in it. FileInfo fileInfo = new FileInfo(Path.Combine( this.sessionBatchingDirectory.FullName, batchFileId)); if (fileInfo.Exists && !this.batchIdToFileMapper.ContainsKey(batchFileId)) { //If file exists but is not in the memory id to location mapper //then add it to the mapping this.batchIdToFileMapper.Add(batchFileId, fileInfo.FullName); } //Check to see if the proxy has already uploaded this file to the service return fileInfo.Exists; } public void UploadBatchFile(string batchFileId, byte[] batchContents, string remotePeerId) { Log("UploadBatchFile: {0}", this.sqlProvider.Connection.ConnectionString); try { if (HasUploadedBatchFile(batchFileId, remotePeerId)) { //Service has already received this file. So dont save it again. return; } //Service hasnt seen the file yet so save it. String localFileLocation = Path.Combine( sessionBatchingDirectory.FullName, batchFileId); FileStream fs = new FileStream(localFileLocation, FileMode.Create, FileAccess.Write); using (fs) { fs.Write(batchContents, 0, batchContents.Length); } //Save this Id to file location mapping in the mapper object this.batchIdToFileMapper[batchFileId] = localFileLocation; } catch (Exception e) { throw new FaultException( new WebSyncFaultException("Unable to save batch file.", e)); } } public byte[] DownloadBatchFile(string batchFileId) { try { Log("DownloadBatchFile: {0}", this.sqlProvider.Connection.ConnectionString); Stream localFileStream = null; string localBatchFileName = null; if (!this.batchIdToFileMapper.TryGetValue(batchFileId, out localBatchFileName)) { throw new FaultException( new WebSyncFaultException("Unable to retrieve batch file for id." + batchFileId, null)); } using (localFileStream = new FileStream(localBatchFileName, FileMode.Open, FileAccess.Read)) { byte[] contents = new byte[localFileStream.Length]; localFileStream.Read(contents, 0, contents.Length); return contents; } } catch (Exception e) { throw new FaultException( new WebSyncFaultException("Unable to read batch file for id " + batchFileId, e)); } } public void EndSession() { Log("EndSession: {0}", this.sqlProvider.Connection.ConnectionString); Log("*****************************************************************"); Log("******************** End Sync Session ***************************"); Log("*****************************************************************"); this.sqlProvider.EndSession(null); Log(""); } public void Cleanup() { this.sqlProvider = null; //Delete all file in the temp session directory if (sessionBatchingDirectory != null) { sessionBatchingDirectory.Refresh(); if (sessionBatchingDirectory.Exists) { try { sessionBatchingDirectory.Delete(true); } catch { //Ignore } } } } protected void Log(string p, params object[] paramArgs) { Console.WriteLine(p, paramArgs); } private void CheckAndCreateBatchingDirectory(string remotePeerId) { //Check to see if we have temp directory for this session. if (sessionBatchingDirectory == null) { //Generate a unique Id for the directory //We use the peer id of the store enumerating the changes so that the //local temp directory is same for a given source //across sync sessions. This enables us to restart a failed sync by not //downloading already received files. string sessionDir = Path.Combine(this.sqlProvider.BatchingDirectory, "WebSync_" + remotePeerId); sessionBatchingDirectory = new DirectoryInfo(sessionDir); //Create the directory if it doesnt exist. if (!sessionBatchingDirectory.Exists) { sessionBatchingDirectory.Create(); } } } } } ``` Listing 4: SqlWebSyncService.Svc.cs We are not going to explain the above code in detail since its provided in the Microsoft samples. You can build the project to test that all the code complies correctly without any errors. We need to deploy the service into a virtual directory before we start its configuration. You can either use IIS Express or choose to deploy the service into a virtual directory. We choose to have the service deployed in its own virtual directory, right click the project name in solution explorer and select **Properties** from the context menu. In the properties screen choose Web and then remove **Use IIS Express** checkbox and click on **Create Virtual Directory** button to create new Virtual Directory and then click on **Save** to save the settings.. See figure 11 for the properties screen settings. Figure 11: Create Virtual Directory Now click on Build item We now need to add the web service configuration to the web.config file, open the web.config file from solution explorer and add the following section as shown in listing 5 to configure the web service. ``` ``` Listing 5: system.serviceModel settings to be added in web.config file The above config section configures the end points for the service. You will have to make some updates to point to the right URL of the service once you deploy on the server. One important point to note here is that for enabling Session support in the web service we have to enable **reliableSession** WCF module. This completes the changes to the wcf service. Its now ready to synchronize against any client providers, as you can see from the above codebase, the WCF service is pretty generic and can be used in any kind of synchronization project. One important step is to make sure that the codebase is compiled in either x86 or x64 based on the platform you plan to deploy this service. We have described this step below, but in case you are just copying the web services codebase ensure to make it compile specific to your deployment platform. ### 3) WPF Client We now add the last part of this project, right click the solution name in solution explorer and select **Add** –> **Add New Project** from the context menu. Select** WPF Application** from the** Add New Project** dialog. Provide the project name as **SyncClient** and click **OK** to create the new project. Again in the solution explorer right click **References** and select **Add Reference** from the context menu. In the **Add Reference** dialog select **Solution** – > **Projects** and select the **ServiceInterface** checkbox to add reference to the project. Then select **Framework** under **Assemblies**, to list all the framework assemblies. Then check assemblies **System.ServiceModel**, **System.ServiceModel.Channels** and **System.Runtime.Serialization** to select them. Now select Browse on the **Add Reference** dialog, navigate to the path where the Sync Framework is installed to reference **Microsoft.Synchronization.dll,** **Microsoft.Synchronization.Data.dll **and **Microsoft.Synchronization.Data.SqlCE.dll **. On a 64 bit machine these assemblies can be found in the **C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\x64** and** C:\Program Files (x86)\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x64** folders respectively. If you go one level up you would find the x86 folder containing the 32-bit version of the assemblies. If you are developing on a 32 bit machine then look within the **C:\Program Files\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\x86** and **C:\Program Files\Microsoft SDKs\Microsoft Sync Framework\2.1\Runtime\ADO.NET\V3.1\x86** folders to find these assemblies. We also need to reference SQL CE assembly **System.Data.SqlServerCe.dll**, click on **Browse** again and navigate to the **C:\Program Files\Microsoft SQL Server Compact Edition\v3.5\Desktop** folder to select the assembly. This assembly is also platform specific so ensure that you select the right version based on the client platform. Click **OK** to reference the assemblies in your project. Let’s start adding code to our WPF project, right-click the SyncClient project in solution explorer and select Add – > Class from the context menu. Give the class name as RelationalProviderProxy.cs and click ADD to add the class to the project. Copy paste the code as shown below in listing 6 for the RelationalProviderProxy class. ``` using System; using System.Collections.Generic; using System.Text; using Microsoft.Synchronization; using System.ServiceModel; using System.ServiceModel.Channels; using Microsoft.Synchronization.Data; using System.IO; using ServiceInterface; namespace SyncClient { public class RelationalProviderProxy : KnowledgeSyncProvider, IDisposable { protected SyncIdFormatGroup idFormatGroup; protected string scopeName; protected DirectoryInfo localBatchingDirectory; protected bool disposed = false; ISyncService proxy; protected string serviceURL; private string batchingDirectory = Environment.ExpandEnvironmentVariables("%TEMP%"); public string BatchingDirectory { get { return batchingDirectory; } set { if (string.IsNullOrEmpty(value)) { throw new ArgumentException("value cannot be null or empty"); } try { Uri uri = new Uri(value); if (!uri.IsFile || uri.IsUnc) { throw new ArgumentException("value must be a local directory"); } batchingDirectory = value; } catch (Exception e) { throw new ArgumentException("Invalid batching directory.", e); } } } public RelationalProviderProxy(string scopeName, string serviceURL) { this.scopeName = scopeName; this.serviceURL = serviceURL; this.CreateProxy(); this.proxy.Initialize(scopeName); } public override void BeginSession(SyncProviderPosition position, SyncSessionContext syncSessionContext) { this.proxy.BeginSession(position); } public override void EndSession(SyncSessionContext syncSessionContext) { proxy.EndSession(); if (this.localBatchingDirectory != null) { this.localBatchingDirectory.Refresh(); if (this.localBatchingDirectory.Exists) { //Cleanup batching releated files from this session this.localBatchingDirectory.Delete(true); } } } public override ChangeBatch GetChangeBatch(uint batchSize, SyncKnowledge destinationKnowledge, out object changeDataRetriever) { GetChangesParameters changesWrapper = proxy.GetChanges(batchSize, destinationKnowledge); //Retrieve the ChangeDataRetriever and the ChangeBatch changeDataRetriever = changesWrapper.DataRetriever; DbSyncContext context = changeDataRetriever as DbSyncContext; //Check to see if the data is batched. if (context != null && context.IsDataBatched) { if (this.localBatchingDirectory == null) { //Retrieve the remote peer id from the //MadeWithKnowledge.ReplicaId. MadeWithKnowledge is the local //knowledge of the peer that is enumerating the changes. string remotePeerId = context.MadeWithKnowledge.ReplicaId.ToString(); //Generate a unique Id for the directory. //We use the peer id of the store enumerating the changes so //that the local temp directory is same for a given source //across sync sessions. This enables us to restart a failed sync //by not downloading already received files. string sessionDir = Path.Combine(this.batchingDirectory, "WebSync_" + remotePeerId); this.localBatchingDirectory = new DirectoryInfo(sessionDir); //Create the directory if it doesnt exist. if (!this.localBatchingDirectory.Exists) { this.localBatchingDirectory.Create(); } } string localFileName = Path.Combine(this.localBatchingDirectory.FullName, context.BatchFileName); FileInfo localFileInfo = new FileInfo(localFileName); //Download the file only if doesnt exist if (!localFileInfo.Exists) { byte[] remoteFileContents = this.proxy.DownloadBatchFile(context.BatchFileName); using (FileStream localFileStream = new FileStream(localFileName, FileMode.Create, FileAccess.Write)) { localFileStream.Write(remoteFileContents, 0, remoteFileContents.Length); } } //Set DbSyncContext.Batchfile name to the new local file name context.BatchFileName = localFileName; } return changesWrapper.ChangeBatch; } public override FullEnumerationChangeBatch GetFullEnumerationChangeBatch(uint batchSize, SyncId lowerEnumerationBound, SyncKnowledge knowledgeForDataRetrieval, out object changeDataRetriever) { throw new NotImplementedException(); } public override void GetSyncBatchParameters(out uint batchSize, out SyncKnowledge knowledge) { SyncBatchParameters wrapper = proxy.GetKnowledge(); batchSize = wrapper.BatchSize; knowledge = wrapper.DestinationKnowledge; } public override SyncIdFormatGroup IdFormats { get { if (idFormatGroup == null) { idFormatGroup = new SyncIdFormatGroup(); // //1 byte change unit id (Harmonica default before flexible ids) // idFormatGroup.ChangeUnitIdFormat.IsVariableLength = false; idFormatGroup.ChangeUnitIdFormat.Length = 1; // // Guid replica id // idFormatGroup.ReplicaIdFormat.IsVariableLength = false; idFormatGroup.ReplicaIdFormat.Length = 16; // // Sync global id for item ids // idFormatGroup.ItemIdFormat.IsVariableLength = true; idFormatGroup.ItemIdFormat.Length = 10 * 1024; } return idFormatGroup; } } public override void ProcessChangeBatch(ConflictResolutionPolicy resolutionPolicy, ChangeBatch sourceChanges, object changeDataRetriever, SyncCallbacks syncCallbacks, SyncSessionStatistics sessionStatistics) { DbSyncContext context = changeDataRetriever as DbSyncContext; if (context != null && context.IsDataBatched) { string fileName = new FileInfo(context.BatchFileName).Name; //Retrieve the remote peer id from the MadeWithKnowledge.ReplicaId. //MadeWithKnowledge is the local knowledge of the peer //that is enumerating the changes. string peerId = context.MadeWithKnowledge.ReplicaId.ToString(); //Check to see if service already has this file if (!this.proxy.HasUploadedBatchFile(fileName, peerId)) { //Upload this file to remote service FileStream stream = new FileStream(context.BatchFileName, FileMode.Open, FileAccess.Read); byte[] contents = new byte[stream.Length]; using (stream) { stream.Read(contents, 0, contents.Length); } this.proxy.UploadBatchFile(fileName, contents, peerId); } context.BatchFileName = fileName; } SyncSessionStatistics stats = this.proxy.ApplyChanges(resolutionPolicy, sourceChanges, changeDataRetriever); sessionStatistics.ChangesApplied += stats.ChangesApplied; sessionStatistics.ChangesFailed += stats.ChangesFailed; } public override void ProcessFullEnumerationChangeBatch( ConflictResolutionPolicy resolutionPolicy, FullEnumerationChangeBatch sourceChanges, object changeDataRetriever, SyncCallbacks syncCallbacks, SyncSessionStatistics sessionStatistics) { throw new NotImplementedException(); } protected void CreateProxy() { WSHttpBinding binding = new WSHttpBinding(); //BasicHttpBinding binding = new BasicHttpBinding(); binding.ReaderQuotas.MaxArrayLength = 10485760; binding.MaxReceivedMessageSize = 10485760; binding.Security.Mode = SecurityMode.None; binding.ReliableSession.Enabled = true; ChannelFactory factory = new ChannelFactory(binding, new EndpointAddress(serviceURL)); this.proxy = factory.CreateChannel(); } public DbSyncScopeDescription GetScopeDescription() { return this.proxy.GetScopeDescription(); } ~RelationalProviderProxy() { Dispose(false); } public virtual void Dispose() { Dispose(true); GC.SuppressFinalize(this); } protected virtual void Dispose(bool disposing) { if (!this.disposed) { if (disposing) { if (proxy != null) { CloseProxy(); } } disposed = true; } } /// /// Clean up and close proxy. /// public virtual void CloseProxy() { if (proxy != null) { proxy.Cleanup(); ICommunicationObject channel = proxy as ICommunicationObject; if (channel != null) { try { channel.Close(); } catch (TimeoutException) { channel.Abort(); } catch (CommunicationException) { channel.Abort(); } } proxy = null; } } } } ``` Listing 6: RelationalProviderProxy.cs The code for the **RelationalProviderProxy** class is also from the Microsoft sample, it provides the client side proxy for invoking the WCF web service. Since the WCF web service uses sessions, we can’t rely on Visual Studio to auto create a proxy class for us. Instead we define our own client proxy class which will ultimately call the wcf service and perform synchronization. Next we need to create a SQL CE 3.5 database for the client application. We use the SQL Server Compact Toolbox, Visual Studio plug-in to create the SqlCE database. In visual studio got to **View** menu –> **Other Windows** –> **SQL Server Compact Toolbox** to bring up the toolbox window. Right click on **SQL Server Compact Data Connections** and select **Add SQL Server Compact 3.5 Connection** as shown in figure 12 below. Figure 12: Add SQL Server Compact 3.5 Connection On the **Add SQL Server Compact 3.5** dialog click on **Create** and provide the path to the project to store the SQL CE database in the project folder and the database file name as **ClientDB**. We have provided the path **C:\SystenicsBlog\SyncSample\SyncSample\SyncClient\ClientDB.sdf** to create the database. You can optionally choose to provide a password for your CE database. Click **OK** to create the database, you will also see the database listed in the **SQL Server Compact Toolbox** window. You should follow these steps every time you wish to reset the client database, creating a new one so that it can synchronize with the server and fetch fresh schema. We need to add the path to the client database and the Sync Web Service in the settings file so that we do not hardcode them. In solution explorer expand the **Properties** section under the **SyncClient** project and double click on **Settings.settings** item to open it. Add a new setting named **DBConnection**, **Type** as **Connection String**, **Scope** as **Application** and **Value** as **Data Source=C:\SystenicsBlog\SyncSample\SyncSample\SyncClient\ClientDB.sdf**. Add another setting named **ServiceUrl**, **Type** as **string**, **Scope** as **Application** and **Value** as **http://localhost/SyncService/SqlWebSyncService.svc**. Save the settings, these need to be updated at time of client deployment to point to the folder where the SQL CE database exists and the URL where the WCF service has been hosted. Figure 13: Settings.settings file Once the settings are configured, right click the solution explorer and choose **Add** – > **Class** from the context menu. Provide the class name as **SynchronizationHelper.cs** and click **Add** to add a new class to the project. Copy paste the code from listing 7 below to populate the code for the **SynchronizationHelper** class. ``` using System; using System.Collections.Generic; using System.Text; using System.Data.SqlClient; using System.Data; using System.Data.SqlServerCe; using System.IO; using Microsoft.Synchronization; using Microsoft.Synchronization.Data.SqlServerCe; using Microsoft.Synchronization.Data; using System.Threading; namespace SyncClient { class SyncResults { private SyncOperationStatistics stats; public SyncOperationStatistics Stats { get { return stats; } set { stats = value; } } private string message; public string Message { get { return message; } set { message = value; } } public SyncResults(string message, SyncOperationStatistics stats) { this.message = message; this.stats = stats; } } class SynchronizationHelper { public void SynchronizeAsync( System.ComponentModel.BackgroundWorker worker, System.ComponentModel.DoWorkEventArgs e) { SyncResults results; results = SynchronizeProductsEmployees(); worker.ReportProgress(0, results); results = SynchronizeEmployeeSales(); worker.ReportProgress(0, results); } public SyncResults SynchronizeProductsEmployees() { SyncResults results = null; // Create the SQL CE Sync Provider for the given scope name SqlCeSyncProvider localProvider = ConfigureCESyncProvider("ProductsEmployees"); // Create the remote provider for the given scope name RelationalProviderProxy destinationProxy = new RelationalProviderProxy("ProductsEmployees", Properties.Settings.Default.ServiceUrl); // Synchronize and collect results results = new SyncResults("ProductsEmployees", SynchronizeProviders(localProvider, destinationProxy, SyncDirectionOrder.Download)); destinationProxy.Dispose(); localProvider.Dispose(); return results; } public SyncResults SynchronizeEmployeeSales() { SyncResults results = null; // Create the SQL CE Sync Provider for the given scope name SqlCeSyncProvider localProvider = ConfigureCESyncProvider("EmployeeSales"); // Create the remote provider for the given scope name RelationalProviderProxy destinationProxy = new RelationalProviderProxy("EmployeeSales", Properties.Settings.Default.ServiceUrl); // Synchronize and collect results results = new SyncResults("EmployeeSales", SynchronizeProviders(localProvider, destinationProxy, SyncDirectionOrder.UploadAndDownload)); destinationProxy.Dispose(); localProvider.Dispose(); return results; } /// /// Utility function that configures a CE provider /// /// /// private SqlCeSyncProvider ConfigureCESyncProvider(string scopeName) { SqlCeConnection sqlCeConnection = new SqlCeConnection( Properties.Settings.Default.DbConnection); SqlCeSyncProvider provider = new SqlCeSyncProvider(); //Set the scope name provider.ScopeName = scopeName; //Set the connection. provider.Connection = sqlCeConnection; //Thats it. We are done configuring the CE provider. return provider; } /// /// Utility function that will create a SyncOrchestrator and /// synchronize the two passed in providers /// /// Local store provider /// Remote store provider /// private SyncOperationStatistics SynchronizeProviders( KnowledgeSyncProvider localProvider, KnowledgeSyncProvider remoteProvider, SyncDirectionOrder direction) { SyncOrchestrator orchestrator = new SyncOrchestrator(); orchestrator.LocalProvider = localProvider; orchestrator.RemoteProvider = remoteProvider; orchestrator.Direction = direction; //Check to see if any provider is a SqlCe provider and if it needs schema CheckIfProviderNeedsSchema(localProvider as SqlCeSyncProvider); SyncOperationStatistics stats = orchestrator.Synchronize(); return stats; } /// /// Check to see if the passed in CE provider needs Schema from server /// /// private void CheckIfProviderNeedsSchema(SqlCeSyncProvider localProvider) { if (localProvider != null) { SqlCeConnection ceConn = (SqlCeConnection)localProvider.Connection; SqlCeSyncScopeProvisioning ceConfig = new SqlCeSyncScopeProvisioning(ceConn); string scopeName = localProvider.ScopeName; //if the scope does not exist in this store if (!ceConfig.ScopeExists(scopeName)) { //create a reference to the server proxy RelationalProviderProxy serverProxy = new RelationalProviderProxy(scopeName, Properties.Settings.Default.ServiceUrl); //retrieve the scope description from the server DbSyncScopeDescription scopeDesc = serverProxy.GetScopeDescription(); serverProxy.Dispose(); //use scope description from server to intitialize the client ceConfig.PopulateFromScopeDescription(scopeDesc); ceConfig.Apply(); } } } } } ``` Listing 7: SynchronizationHelper class The code for listing 7 is also from the Microsoft sample, but we have modified it to just have synchronization code for syncing our CE database with the remote database calling the web service. There are two methods defined **SynchronizeProductsEmployees** and **SynchronizeEmployeeSales** which are used to synchronize our two different scopes that we defined at the start of the blog. The first method only downloads the changes from the server while the second one uploads as well as downloads data between both databases. These methods are invoked from the **BackgroundWorker** thread defined in the **MainWindow** class listed below so that synchronization is performed without blocking the UI thread making the WPF application responsive while synchronization is in progress. The Main WPF app is a simple application with two buttons and a richtextbox control, since the goal here is not to demo WPF capabilities but to showcase synchronization. The **Synchronize** button will synchronize the client SQL CE database with the SQL Server database, bringing down botch schema and data from the server as well as update the main server with any changes made in the **EmployeeSales** table. The **Insert Record** button inserts a new record in **EmployeeSales** table in the local SQL CE database (Please ensure that you have first clicked on **Synchronize** before inserting data else error will be thrown for inserting data into non existent table). After inserting records into the local SQL CE database you can click the **Synchronize** button again to see the newly added record being synchronized with the server, you can then manually check the MS SQL database to find the newly added record. If you make data changes to the MS SQL database and synchronize, you should find the data updated in the SQL CE database locally. This makes the synchronize process pretty pain free once you have it all setup correctly. The listing below show the code for MainWindows.xaml.cs file. ``` using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows; using System.Windows.Controls; using System.Windows.Data; using System.Windows.Documents; using System.Windows.Input; using System.Windows.Media; using System.Windows.Media.Imaging; using System.Windows.Navigation; using System.Windows.Shapes; using System.ComponentModel; using Microsoft.Synchronization; using System.Data.SqlServerCe; namespace SyncClient { /// /// Interaction logic for MainWindow.xaml /// public partial class MainWindow : Window { // Create a BackgroundWorker object to synchronize without blocking // the UI thread private System.ComponentModel.BackgroundWorker backgroundWorker1; public MainWindow() { InitializeComponent(); this.backgroundWorker1 = new System.ComponentModel.BackgroundWorker(); this.backgroundWorker1.WorkerReportsProgress = true; // Register the various BackgroundWorker events this.backgroundWorker1.DoWork += new System.ComponentModel.DoWorkEventHandler( this.backgroundWorker1_DoWork); this.backgroundWorker1.ProgressChanged += new System.ComponentModel.ProgressChangedEventHandler( this.backgroundWorker1_ProgressChanged); this.backgroundWorker1.RunWorkerCompleted += new System.ComponentModel.RunWorkerCompletedEventHandler( this.backgroundWorker1_RunWorkerCompleted); } private void btnSync_Click(object sender, RoutedEventArgs e) { // Check if sync is already in progress if (!backgroundWorker1.IsBusy) { msg.AppendText( "Starting Data Synchronization Process...\r\n Please wait till the process compeletes.\r\n"); Application.Current.MainWindow.Cursor = Cursors.Wait; SynchronizationHelper syncHelper = new SynchronizationHelper(); // Start synchronization backgroundWorker1.RunWorkerAsync(syncHelper); } } private void btnInsert_Click(object sender, RoutedEventArgs e) { // Insert a test value in local CE database using (SqlCeConnection con = new SqlCeConnection(Properties.Settings.Default.DbConnection)) { SqlCeCommand cmd = new SqlCeCommand("INSERT INTO [EmployeeSales] ([Id],[ProductId],[EmployeeId],[Unit],[Date])" +" Values (@Id,@ProductId,@EmployeeId,@Unit,@Date)"); cmd.Parameters.Add("@Id", Guid.NewGuid()); //Hardcoded ProductId cmd.Parameters.Add("@ProductId", "F4FC70E6-5DC7-4982-95F0-32E806E21D84"); //Hardcoded EmployeeId cmd.Parameters.Add("@EmployeeId", "0A5F6AD1-F345-48D4-B6D6-0850A7C930C9"); // Hardcoded Units cmd.Parameters.Add("@Unit", 10); cmd.Parameters.Add("@Date", DateTime.Now); cmd.Connection = con; try { con.Open(); cmd.ExecuteNonQuery(); con.Close(); } catch (Exception ed) { MessageBox.Show("Error Occurred while inserting.\r\n" + ed.ToString()); } } } // Method to start syncthonization in background private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { System.ComponentModel.BackgroundWorker worker; worker = (System.ComponentModel.BackgroundWorker)sender; SynchronizationHelper syncHelper = (SynchronizationHelper)e.Argument; syncHelper.SynchronizeAsync(worker, e); } //Method to report synchronization progress private void backgroundWorker1_ProgressChanged(object sender, ProgressChangedEventArgs e) { SyncResults results = (SyncResults)e.UserState; if (results != null) { DisplayStats(results); } } //Method runs when synchtonization compeletes private void backgroundWorker1_RunWorkerCompleted( object sender, RunWorkerCompletedEventArgs e) { Application.Current.MainWindow.Cursor = Cursors.Arrow; if (e.Error != null) { msg.AppendText( "An Error has occurred. Please try synchronization later.\r\nThe error:" + e.Error.Message); MessageBox.Show("Error: " + e.Error.Message); } else { msg.AppendText("Synchronization Finished Successfully\r\n"); MessageBox.Show("Finished Synchronization"); } } // Method to format the SyncResults for display private void DisplayStats(SyncResults results) { TimeSpan diff = results.Stats.SyncEndTime.Subtract(results.Stats.SyncStartTime); msg.AppendText( string.Format( "{4}: - Total Time To Synchronize = {0}:{1}:{2}:{3}\r\nTotal Records Uploaded: {5} Total Records Downloaded: {6}\r\n", diff.Hours, diff.Minutes, diff.Seconds, diff.Milliseconds, results.Message, results.Stats.UploadChangesTotal, results.Stats.DownloadChangesTotal)); } } } ``` Listing 8 – MainWindows.xaml.cs Update the c# code as well as XAML code for the default **MainWindow** class copy pasting code from listing 8 and 9. In the above listing you can see that we have used the **BackgroundWorker** object to invoke the WCF services in the background, as there are updates from the WCF server we update the richtextbox with the synchronization results. Listing 9 below shows the XAML for **MainWindow**. ```