Synchronization with SQLite on WinRT
Hi all;
As you probably know, a synchronization framework already exists in the .NET Framework : SyncFramework.
This post will show you how to synchronize a server, exposing data with this framework, with WinRT using SQLite embedded database.
You can get the source code on Codeplex : https://syncwinrt.codeplex.com/
Introduction
Sync Framework 2.1
 |
The last version of Sync Framework, (Sync Framework V2.1) allows you to synchronize any data source from a server to a desktop application running SQL SERVER, SQL SERVER CE, SQL SERVER EXPRESS. |
Multiples scenarios are possible :
- Hub and Spoke sync. (1st picture)
- Peer to Peer sync. (2nd picture)
Sync Framework Toolkit
This toolkit is based on Sync Framework 2.1. It’s not a new version.
You can download the latest version from Code MSDN at the following address : Sync Framework ToolKit.
This toolkit allows other Microsoft platforms to be used for offline clients such as Silverlight, Windows Phone 7, and Windows Mobile.
In addition non-Microsoft platforms such as iPhone, Android, and Blackberry can be utilized as well as HTML
Sync Framework Toolkit use the OData protocol to communicate between the Server and the Client, using JSON or ATOM format.
You’ll find samples for these scenarios in the .zip package of the Sync Framework Toolkit.
Sync.Win8
There is no sync client for WinRT, based on Sync Framework Toolkit. You need to develop this part with the SDK provided by the Sync Framework Toolkit.
To achieve this goal, we need to:
- Store data with WinRT : We can do it with Local Storage or SQLite (the sample contains the two models of storage)
- Create a sync client who can manage a complete sequence of Synchronization
- Create all the necessary code to implement in your solution to map with your entities.
Requirements
Before starting, you must install on your server, the latest version of Sync Framework 2.1 for .NET:
You have to choose between the x64 version and x86 version : https://www.microsoft.com/en-us/download/details.aspx?id=23217
If you install it on a x64 developer machine, you must install both of them, BUT you can’t install both of the x64 and x86 version of the SDK.
The solution is to install the x64 version (https://www.microsoft.com/en-us/download/details.aspx?id=23217) and install the redistributables of the x86 version (https://www.microsoft.com/en-us/download/details.aspx?id=19502: Install the 3 msi)
For WinRT, you need to Install SQLite For WinRT: Just go to Extensions and Update in your Visual Studio 2012 and search for SQLite for Windows Runtime, like this screenshot :
Now, you can download my version of the toolkit on Codeplex 
: https://syncwinrt.codeplex.com/
How It Works ?
After downloading the code, you need to:
- Configure your database to create an additionnal synchronization schema.
- Create a server which will deliver data (with ODATA)
- Create a WinRT client which will get, modify, delete and pull data to the server.
SyncSvcUtil.exe is a command line utility provided with the toolkit to generate all the required code to configure your server, client and database.
Sample
I’m going to show you how to create a simple application which will synchronize data with a simple server.
This sample is based on the adventureworks database 2012 (you can download it form this address : SQL SERVER SAMPLES DATABASES).
Database
We will configure this database to be able to synchronize 5 tables.
The SyncSvcUtil.exe works with an xml file configuration. This is the xml contained in the AdventureWorks.xml file.
We can choose to synchronize all columns from a table or we can choose particular columns.
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
<configSections>
<section name="SyncConfiguration" type="Microsoft.Synchronization.ClientServices.Configuration.SyncConfigurationSection, SyncSvcUtil, Version=1.0.0.0, Culture=neutral"
allowDefinition="Everywhere" allowExeDefinition="MachineToApplication" restartOnExternalChanges="true" />
</configSections>
<SyncConfiguration>
<SyncScopes>
<SyncScope Name="DefaultScope" IsTemplateScope="true">
<SyncTables>
<SyncTable Name="Person.AddressType" GlobalName="AddressType" IncludeAllColumns="true" />
<SyncTable Name="Person.Address" GlobalName="Address" IncludeAllColumns="false" >
<SyncColumns>
<SyncColumn Name="AddressID" GlobalName="AddressID" SqlType="int" IsPrimaryKey="true" IsNullable="false" />
<SyncColumn Name="AddressLine1" GlobalName="AddressLine1" SqlType="nvarchar" IsNullable="false" />
<SyncColumn Name="AddressLine2" GlobalName="AddressLine2" SqlType="nvarchar" />
<SyncColumn Name="City" GlobalName="City" SqlType="nvarchar" IsNullable="false" />
<SyncColumn Name="StateProvinceID" GlobalName="StateProvinceID" SqlType="int" IsNullable="false" />
<SyncColumn Name="PostalCode" GlobalName="PostalCode" SqlType="nvarchar" IsNullable="false" />
<SyncColumn Name="rowguid" GlobalName="RowGuid" SqlType="uniqueidentifier" IsNullable="false" />
<SyncColumn Name="ModifiedDate" GlobalName="ModifiedDate" SqlType="DateTime" IsNullable="false" />
</SyncColumns>
</SyncTable>
<SyncTable Name="HumanResources.Employee" GlobalName="Employee" IncludeAllColumns="false" >
<SyncColumns>
<SyncColumn Name="BusinessEntityID" GlobalName="BusinessEntityID" SqlType="int" IsPrimaryKey="true" IsNullable="false" />
<SyncColumn Name="NationalIDNumber" GlobalName="NationalIDNumber" SqlType="nvarchar" IsNullable="false" />
<SyncColumn Name="LoginID" GlobalName="LoginID" SqlType="NVarchar" IsNullable="false" />
<SyncColumn Name="JobTitle" GlobalName="JobTitle" SqlType="NVarchar" IsNullable="false" />
<SyncColumn Name="BirthDate" GlobalName="BirthDate" SqlType="Date" IsNullable="false" />
<SyncColumn Name="MaritalStatus" GlobalName="MaritalStatus" SqlType="NChar" IsNullable="false" />
<SyncColumn Name="Gender" GlobalName="Gender" SqlType="NChar" IsNullable="false" />
<SyncColumn Name="HireDate" GlobalName="HireDate" SqlType="Date" IsNullable="false" />
<SyncColumn Name="SalariedFlag" GlobalName="SalariedFlag" SqlType="Bit" IsNullable="false" />
<SyncColumn Name="VacationHours" GlobalName="VacationHours" SqlType="SmallInt" IsNullable="false" />
<SyncColumn Name="SickLeaveHours" GlobalName="SickLeaveHours" SqlType="SmallInt" IsNullable="false" />
<SyncColumn Name="CurrentFlag" GlobalName="CurrentFlag" SqlType="Bit" IsNullable="false" />
<SyncColumn Name="rowguid" GlobalName="RowGuid" SqlType="UniqueIdentifier" IsNullable="false" />
<SyncColumn Name="ModifiedDate" GlobalName="ModifiedDate" SqlType="DateTime" IsNullable="false" />
</SyncColumns>
</SyncTable>
<SyncTable Name="HumanResources.Department" GlobalName="Departement" IncludeAllColumns="true" />
<SyncTable Name="HumanResources.EmployeeDepartmentHistory" GlobalName="EmployeeDepartmentHistory" IncludeAllColumns="true" />
</SyncTables>
</SyncScope>
</SyncScopes>
<Databases>
<TargetDatabase Name="AdventureWorks2012" DbServer=".\SQL2012" DbName="AdventureWorks2012" UseIntegratedAuth="true" />
</Databases>
</SyncConfiguration>
</configuration>
And here is the command line expression to configure my database:
SyncSvcUtil.exe /mode:Provision /scopeconfig:AdventureWorks.xml
Your database is ready ! Note that 5 more tables will be added to your database schema, and 1 more table per synchronization table.
For each table in the synchronization schema, 3 triggers will be added to track changes.
Server
On the server side, you have to create a web site to provide an handler for your WinRT application.
SyncSvcUtil.exe /mode:codegen /target:Server /scopeconfig:AdventureWorks.xml /directory:"C:\temp\server"
The SyncSvcUtil.exe will generate two classes (depending on your scope name)
DefaultscopeEntities.cs and DefaultscopeSyncService.svc
DefaultscopeEntities will contains all entities mandatories for synchronization. Here is the code generated for the entity Employee:
[Microsoft.Synchronization.Services.SyncEntityTypeAttribute(TableGlobalName="Employee", TableLocalName="[HumanResources].[Employee]", KeyFields="BusinessEntityID")]
public partial class Employee : DefaultScopeOfflineEntityBase {
private int _BusinessEntityID;
private string _NationalIDNumber;
private string _LoginID;
private string _JobTitle;
private System.DateTime _BirthDate;
private string _MaritalStatus;
private string _Gender;
private System.DateTime _HireDate;
private bool _SalariedFlag;
private short _VacationHours;
private short _SickLeaveHours;
private bool _CurrentFlag;
private System.Guid _RowGuid;
private System.DateTime _ModifiedDate;
public int BusinessEntityID {
get {
return _BusinessEntityID;
}
set {
_BusinessEntityID = value;
}
}
public string NationalIDNumber {
get {
return _NationalIDNumber;
}
set {
_NationalIDNumber = value;
}
}
public string LoginID {
get {
return _LoginID;
}
set {
_LoginID = value;
}
}
public string JobTitle {
get {
return _JobTitle;
}
set {
_JobTitle = value;
}
}
public System.DateTime BirthDate {
get {
return _BirthDate;
}
set {
_BirthDate = value;
}
}
public string MaritalStatus {
get {
return _MaritalStatus;
}
set {
_MaritalStatus = value;
}
}
public string Gender {
get {
return _Gender;
}
set {
_Gender = value;
}
}
public System.DateTime HireDate {
get {
return _HireDate;
}
set {
_HireDate = value;
}
}
public bool SalariedFlag {
get {
return _SalariedFlag;
}
set {
_SalariedFlag = value;
}
}
public short VacationHours {
get {
return _VacationHours;
}
set {
_VacationHours = value;
}
}
public short SickLeaveHours {
get {
return _SickLeaveHours;
}
set {
_SickLeaveHours = value;
}
}
public bool CurrentFlag {
get {
return _CurrentFlag;
}
set {
_CurrentFlag = value;
}
}
[Microsoft.Synchronization.Services.SyncEntityPropertyMappingAttribute(LocalName="rowguid")]
public System.Guid RowGuid {
get {
return _RowGuid;
}
set {
_RowGuid = value;
}
}
public System.DateTime ModifiedDate {
get {
return _ModifiedDate;
}
set {
_ModifiedDate = value;
}
}
}
DefaultscopeSyncService.svc is the Service Handler generated for you that will provide all the methods mandatories to sync from the client
You have to configure this file for working with your sync – enabled – database :
public class DefaultScopeSyncService : Microsoft.Synchronization.Services.SyncService<DefaultScopeOfflineEntities> {
public static void InitializeService(Microsoft.Synchronization.Services.ISyncServiceConfiguration config) {
// TODO: MUST set these values
config.ServerConnectionString = @"Data Source=.\SQL2012;Initial Catalog=AdventureWorks2012;Integrated Security=true;";
config.SetEnableScope("DefaultScope");
//
// TODO: Optional.
config.SetDefaultSyncSerializationFormat(Microsoft.Synchronization.Services.SyncSerializationFormat.ODataJson);
config.SetConflictResolutionPolicy(Microsoft.Synchronization.Services.ConflictResolutionPolicy.ServerWins);
config.UseVerboseErrors = true;
}
}
That’s all !
If you look at the .svc in your browser, you will see an OData Stream
WinRT Client
Last step before starting your project, you have to generate all the mandatory code for the client side. Like Database and Server, you achieve this part with SvcSyncUtil.exe :
SyncSvcUtil.exe /mode:codegen /target:SQLiteClient /scopeconfig:AdventureWorks.xml /directory:"C:\temp\client"
Note : As you can see, you have now a new option to generate client code for SQLite for WinRT : “SQLiteClient”.
The files generated : DefaultScopeEntities.cs and DefaultScopeOfflineContext.cs
DefaultScopeEntities contains all your entities, that inherit from a new class in the toolkit : SQLiteOfflineEntity.
public partial class Employee : Microsoft.Synchronization.ClientServices.SQLite.SQLiteOfflineEntity {
private int _BusinessEntityID;
private string _NationalIDNumber;
private string _LoginID;
private string _JobTitle;
private System.DateTime _BirthDate;
private string _MaritalStatus;
private string _Gender;
private System.DateTime _HireDate;
private bool _SalariedFlag;
private short _VacationHours;
private short _SickLeaveHours;
private bool _CurrentFlag;
private System.Guid _RowGuid;
private System.DateTime _ModifiedDate;
[Microsoft.Synchronization.ClientServices.SQLite.PrimaryKey()]
public int BusinessEntityID {
get {
return _BusinessEntityID;
}
set {
base.OnPropertyChanging("BusinessEntityID");
_BusinessEntityID = value;
base.OnPropertyChanged("BusinessEntityID");
}
}
[Microsoft.Synchronization.ClientServices.SQLite.MaxLength(15)]
public string NationalIDNumber {
get {
return _NationalIDNumber;
}
set {
base.OnPropertyChanging("NationalIDNumber");
_NationalIDNumber = value;
base.OnPropertyChanged("NationalIDNumber");
}
}
[Microsoft.Synchronization.ClientServices.SQLite.MaxLength(256)]
public string LoginID {
get {
return _LoginID;
}
set {
base.OnPropertyChanging("LoginID");
_LoginID = value;
base.OnPropertyChanged("LoginID");
}
}
[Microsoft.Synchronization.ClientServices.SQLite.MaxLength(50)]
public string JobTitle {
get {
return _JobTitle;
}
set {
base.OnPropertyChanging("JobTitle");
_JobTitle = value;
base.OnPropertyChanged("JobTitle");
}
}
public System.DateTime BirthDate {
get {
return _BirthDate;
}
set {
base.OnPropertyChanging("BirthDate");
_BirthDate = value;
base.OnPropertyChanged("BirthDate");
}
}
[Microsoft.Synchronization.ClientServices.SQLite.MaxLength(1)]
public string MaritalStatus {
get {
return _MaritalStatus;
}
set {
base.OnPropertyChanging("MaritalStatus");
_MaritalStatus = value;
base.OnPropertyChanged("MaritalStatus");
}
}
[Microsoft.Synchronization.ClientServices.SQLite.MaxLength(1)]
public string Gender {
get {
return _Gender;
}
set {
base.OnPropertyChanging("Gender");
_Gender = value;
base.OnPropertyChanged("Gender");
}
}
public System.DateTime HireDate {
get {
return _HireDate;
}
set {
base.OnPropertyChanging("HireDate");
_HireDate = value;
base.OnPropertyChanged("HireDate");
}
}
public bool SalariedFlag {
get {
return _SalariedFlag;
}
set {
base.OnPropertyChanging("SalariedFlag");
_SalariedFlag = value;
base.OnPropertyChanged("SalariedFlag");
}
}
public short VacationHours {
get {
return _VacationHours;
}
set {
base.OnPropertyChanging("VacationHours");
_VacationHours = value;
base.OnPropertyChanged("VacationHours");
}
}
public short SickLeaveHours {
get {
return _SickLeaveHours;
}
set {
base.OnPropertyChanging("SickLeaveHours");
_SickLeaveHours = value;
base.OnPropertyChanged("SickLeaveHours");
}
}
public bool CurrentFlag {
get {
return _CurrentFlag;
}
set {
base.OnPropertyChanging("CurrentFlag");
_CurrentFlag = value;
base.OnPropertyChanged("CurrentFlag");
}
}
public System.Guid RowGuid {
get {
return _RowGuid;
}
set {
base.OnPropertyChanging("RowGuid");
_RowGuid = value;
base.OnPropertyChanged("RowGuid");
}
}
public System.DateTime ModifiedDate {
get {
return _ModifiedDate;
}
set {
base.OnPropertyChanging("ModifiedDate");
_ModifiedDate = value;
base.OnPropertyChanged("ModifiedDate");
}
}
}
DefaultScopeOfflineContext contains a class that inherit from OfflineSyncProvider : SQLiteContext
public class DefaultScopeOfflineContext : Microsoft.Synchronization.ClientServices.SQLite.SQLiteContext {
private const string SyncScopeName = "DefaultScope";
public DefaultScopeOfflineContext(string cachePath, System.Uri serviceUri) :
base(DefaultScopeOfflineContext.GetSchema(), SyncScopeName, cachePath, serviceUri) {
}
private static Microsoft.Synchronization.ClientServices.Common.OfflineSchema GetSchema() {
Microsoft.Synchronization.ClientServices.Common.OfflineSchema schema = new Microsoft.Synchronization.ClientServices.Common.OfflineSchema();
schema.AddCollection<AddressType>();
schema.AddCollection<Address>();
schema.AddCollection<Employee>();
schema.AddCollection<Departement>();
schema.AddCollection<EmployeeDepartmentHistory>();
return schema;
}
}
You now have all the code needed to create an application on WinRT that can synchronize data with your server.
Synchronizing
This is my application (provided with source code). As you can see, there is 2 projects : The first project is a WinRT Xaml application and the second is the server project.
Here is the “awaitable” code to synchronize :
public async Task<CacheRefreshStatistics> Sync()
{
try
{
var result = await this.SyncContext.SynchronizeAsync();
return result;
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
return null;
}
}
you will find all the necessary code in the sample provided with this post (and Codeplex)
Happy Synchronization time on WinRT
Seb
Comments
Anonymous
March 03, 2013
I think you should drop the "4.0" in the Sync Framework Toolkit. There is no Sync Framework Toolkit v 4.0 and neither is there Sync Framework v 4.0. The Sync Framework Toolkit is what used to be Sync Framework 4.0 CTP. btw, great work on the SQLite provider! Cheers, juneTAnonymous
March 04, 2013
Thank you JuneT for your comment :) You are totally right ! I have updated the post :)Anonymous
March 18, 2013
Great Example. Is it possible to convert this to use SQLExpress and LocalDB ? If so can you please give me some guidance to start with ?Anonymous
March 18, 2013
Hi Dush_a; Do you want to use LocalDB as the client side ? If this is the case you are interested, you don't need to work with this modified Toolkit. You just have to work with the original Sync Framework 2.1 Here is the MSDN Sample : msdn.microsoft.com/.../ff928676(v=SQL.110).aspx If you want LocalDB as the Server Side (and by the way SQLite on the clientside) you just have to create the local database and change the connection string in the DefaultScopeSyncService.svc.cs file. Tell me if i didn't understand your question :) SébastienAnonymous
April 19, 2013
hi, i am looking for the option to sync between sqlite and oracle databaseAnonymous
November 06, 2013
Hi Sebastien , I want to sync my local sqlite database with sqplite on skydrive. I need some start fir doing this. Please guide me how can I do this ?Anonymous
June 25, 2015
hii..thanks for nice article..I did this in the same way..but i am not able to find where my synced database is storing after syncronization...but i am getting result.Totaldownloads greater than 0