Sync SQL Server 2012 Data with SQL CE 3.5 SP2 over the Internet using WCF Services using MS Sync Framework v2.1
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) - 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 - 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 (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 (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.
<connectionStrings>
<add name="DBConnection" connectionString="Data Source=.;Initial Catalog=SyncSample;
Persist Security Info=True;Integrated Security=True;"
providerName="System.Data.SqlClient"/>
</connectionStrings>
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;
/// <summary>
/// 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.
/// </summary>
/// <param name="hostName"></param>
/// <returns></returns>
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<string, string> batchIdToFileMapper;
int batchCount = 0;
public void Initialize(string scopeName)
{
ServerSynchronizationHelper helper = new ServerSynchronizationHelper();
this.sqlProvider = helper.ConfigureSqlSyncProvider(scopeName);
this.batchIdToFileMapper = new Dictionary<string, string>();
}
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<string, string>();
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<WebSyncFaultException>(
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<WebSyncFaultException>(
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<WebSyncFaultException>(
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<WebSyncFaultException>(
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.
<system.serviceModel>
<services>
<service behaviorConfiguration="SyncService.SyncServiceBehavior"
name="SyncService.SqlWebSyncService">
<endpoint address="" binding="wsHttpBinding"
bindingConfiguration="largeMessageHttpBinding"
contract="ServiceInterface.ISyncService">
<identity>
<!--Change this when hosting on server-->
<dns value="localhost"/>
</identity>
</endpoint>
<endpoint address="mex" binding="mexHttpBinding" contract="IMetadataExchange"/>
<host>
<baseAddresses>
<!--Change this when hosting on server-->
<add baseAddress="http://localhost/SyncService/SqlWebSyncService.svc"/>
</baseAddresses>
</host>
</service>
</services>
<bindings>
<wsHttpBinding>
<binding name="largeMessageHttpBinding" maxReceivedMessageSize="10485760">
<readerQuotas maxArrayLength="10485760"/>
<security mode="None" />
<reliableSession enabled="true" />
</binding>
</wsHttpBinding>
</bindings>
<behaviors>
<serviceBehaviors>
<behavior name="SyncService.SyncServiceBehavior">
<serviceMetadata httpGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="true" />
</behavior>
<behavior name="">
<serviceMetadata httpGetEnabled="true" httpsGetEnabled="true" />
<serviceDebug includeExceptionDetailInFaults="false" />
</behavior>
</serviceBehaviors>
</behaviors>
<serviceHostingEnvironment aspNetCompatibilityEnabled="true"
multipleSiteBindingsEnabled="true" />
</system.serviceModel>
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<ISyncService> factory =
new ChannelFactory<ISyncService>(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;
}
}
/// <summary>
/// Clean up and close proxy.
/// </summary>
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;
}
/// <summary>
/// Utility function that configures a CE provider
/// </summary>
/// <param name="sqlCeConnection"></param>
/// <returns></returns>
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;
}
/// <summary>
/// Utility function that will create a SyncOrchestrator and
/// synchronize the two passed in providers
/// </summary>
/// <param name="localProvider">Local store provider</param>
/// <param name="remoteProvider">Remote store provider</param>
/// <returns></returns>
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;
}
/// <summary>
/// Check to see if the passed in CE provider needs Schema from server
/// </summary>
/// <param name="localProvider"></param>
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
{
/// <summary>
/// Interaction logic for MainWindow.xaml
/// </summary>
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.
<Window x:Class="SyncClient.MainWindow"
xmlns="http://schemas.microsoft.com/winfx/2006/xaml/presentation"
xmlns:x="http://schemas.microsoft.com/winfx/2006/xaml"
Title="MainWindow" Height="350" Width="525">
<Grid>
<Button x:Name="btnSync" Content="Synchronize" HorizontalAlignment="Left"
Margin="54,29,0,0" VerticalAlignment="Top" Width="75" Click="btnSync_Click"/>
<Button x:Name="btnInsert" Content="Insert Record" HorizontalAlignment="Left"
Margin="288,29,0,0" VerticalAlignment="Top" Width="111" Click="btnInsert_Click"/>
<RichTextBox x:Name="msg" Margin="0,85,0.4,-0.2">
<FlowDocument>
<Paragraph>
<Run Text=""/>
</Paragraph>
</FlowDocument>
</RichTextBox>
</Grid>
</Window>
Listing 9 – MainWindow.XAML
This completes the project source code. You can save all the file build and test your application. In case there are issues in synchronization then the first step would be to ensure that your build is platform specific i.e. x64 or x86 based on the platform of your test or deployment machine. You can create a new build configuration for x64 in visual studio by going to Build – > Configuration Manager menu. In the Configuration Manager, expand the Active Solution Platforms and select <NEW> provide the name as x64 or x86 as required and click OK and then recompile your build to ensure it gets compiled platform specific.
Conclusion
In this blog post we looked at building a WCF based web service to support synchronization between MS SQL and SQL CE databases using MS Sync Framework v2.1 over the internet. The codebase is generic enough to ensure that you can reuse it in your projects. We also looked at the various issues related to building such and application. I hope this blog post helps simplify your synchronization issues.
Source Code Download
Click here to download the source code for this project.