Synchronization with SQLite on WinRT

Synchronization with SQLite on WinRT

  • Comments 6

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/

image

Introduction

Sync Framework 2.1

image 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 :

  1. Hub and Spoke sync. (1st picture)
  2. 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.

image

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.

 

image

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 : http://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 (http://www.microsoft.com/en-us/download/details.aspx?id=23217) and install the redistributables of the x86 version (http://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 :

image

Now, you can download my version of the toolkit on Codeplex Sourire : https://syncwinrt.codeplex.com/

How It Works ?

After downloading the code, you need to:

  1. Configure your database to create an additionnal synchronization schema.
  2. Create a server which will deliver data (with ODATA)
  3. 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

imageWe 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

image

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.

 

image

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"

image


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 Sourire

image

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

image

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.

image

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 Sourire

Seb

Leave a Comment
  • Please add 8 and 5 and type the answer here:
  • Post
  • 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,

    juneT

  • Thank you JuneT for your comment :)

    You are totally right ! I have updated the post :)

  • 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 ?

  • 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ébastien

  • hi,

    i am looking for the option to sync between sqlite and oracle database

  • 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 ?

Page 1 of 1 (6 items)