Synchronizing WinRT/SQLite using Sync Framework Toolkit

It’s been a while since my last blog post and it’s been a while since I started to write this post and a couple of more that’s been eternally in draft mode.

So, let me tackle a common question I often see in forums and I get ask personally: Database synchronization for WinRT/SQLite based applications.

I’ve been asked many times if MS is working on porting Sync Framework to WinRT or if the Sync Framework Toolkit will be ported to WinRT as well. (or if Sync Framework is even still alive ).

Robert Green did a presentation in last year’s BUILD conference (Key technologies for Windows Store business apps ) and demoed a sample application that has sync capabilities built using Sync Framework Toolkit(although i think the toolkit was still referred to as Sync Framework v4 CTP). A sample application is supposed to be forthcoming but we have yet to see one.

Thus the motivation to continue working on this post.

If you’re after the sample app that does synchronization, you can jump to bottom of this page and click on a link to download it.

But if you want to understand a bit how it’s built and how it works, allow me do indulge you with a bit of backgrounder on the Sync Framework Toolkit.

The Sync Framework Toolkit is what was supposed to be Sync Framework v4.  Microsoft released the v3 CTP which eventually become V4 CTP and then they decided to release it as a sample, open-sourced application instead. So now you know there is no V4 of Sync Framework (you may have seen many forum replies from me emphasizing this.)

Contrary to popular belief because of the huge jump in the version number from 2.1 to v4, the Sync Framework Toolkit (or V4 CTP if you insists ) is not a version upgrade from v2.1. In fact, the server side component of the toolkit actually requires an installation of Sync Framework 2.1. It’s built on 2.1, not a replacement.

The toolkit (v4) actually addresses the scenarios where you can’t install the full Sync Framework SDK. These platforms include Silverlight, Windows Phone, iOS, Android or HTML5-based apps.

Instead, MS developed client components that works with the respective client platforms. If you download the toolkit and look at the codes, you will find client components that works with Silverlight/Windows Phone and Isolated Storage, Windows Mobile and SQL Compact, etc.…

These client components then communicate with a service via HTTP and it’s the service that synchronizes with a Microsoft SQL-based backend (can be SQL Server, SQL Express, LocalDB, Azure SQL Database). You will also find some Json and OData in the codes (it uses the OData protocol, but its not same OData you see in the current generation of OData interfaces from MS).

To simplify the components of the toolkit, there’s a:

• Sync Service (uses Sync Framework 2.1, WCF-based, coupled to SQL Server/Express/LocalDB/Azure SQL)
• Cache Controller (client-side, similar to SyncAgent/SyncOrchestrator in Sync Framework)
• Offline Provider (a sync provider)
• Storage Handler (the actual code that interacts with the database/ local store/cache)

Here’s a simplified view of  the Synchronization Data Flow amongst components:

(you may have to zoom in as my blog theme may not display the diagrams clearly)

This is how an Upload Scenario looks like:

 

And here’s how a Download Scenario looks like:

As I have mentioned, the actual client components of the toolkit are specific to the target platforms.

Thus the bulk of the work is on creating client specific codes for a specific development platform and store. The server side is pretty much done via ready-made utilities that simply exposes a HTTP endpoint that client can post and get updates and the service actually takes care of interacting with a SQL Server.

So for a WinRT/SQLite based client, we have to build a Cache Controller and a Storage Handler to interact with SQLite. The codes for the Offline Provider is pretty much the same for most platforms, so there’s not much to do there.

Fortunately for us, the Sync Framework Toolkit includes a sample for Windows Mobile/SQL Compact, so I just went to modify them. The bulk of the work is on getting them to work on WinRT (just moving the toolkit to 4.5 breaks it already, and WinRT even more)

To give you an idea of the stuff that needs to be written for a Cache Controller, here’s the list of files that I converted to WinRT. The bulk of the changes are on the codes that uses Reflection, the Async stuff, and HTTP request handling (specifically, HTTPCacheRequestHandler).

So let’s move on sync-enabling an existing sample app. I downloaded the sample from Robert Green’s post here: Blog Post: Using SQLite in Windows 8 Store Apps (Code Sample: Using SQLite in a Windows Store App)

Apart from adding a reference to the Cache Controller above, I’ve also added the SQLite specific codes in the client application.

The bulk of the code for retrieving and applying changes is inside the SqlliteStorageHandler. This is the part that is going to be very specific for every application.

To give you an idea, here’s some of the codes that you need to write.

Code for retrieving changes that need to be uploaded:

Permanently deleting rows marked with IsTombstone and clearing IsDirty flags, after the rows has been uploaded:

Code for applying downloaded changes from the server:

So let’s start sync-enabling the sample app.

To sync enable the app, we have to change the way we do CRUDs so we can keep track of the changes happening (change tracking). So far this is the most intrusive change we have to include in the client app’s logic since the change tracking becomes a responsibility of the client. In the older full database sync providers for SQL Server and SQL Compact, this is done via triggers and the SQL Compact Change Tracking  API.

First we need to mark the entities that we want to include in the synchronization by having them inherit from SqliteOfflineEntity.

The toolkit reflects on these entities and this adds a property for the sync metadata. We have to tell SQLite to ignore the OfflineEntityMetadata property as otherwise it will try treat it like a normal column and attempt to persist it resulting to an error.

We also need to mark with the toolkit’s Key attribute the property that represents the Primary Key.

Then we have to explicitly declare the change tracking properties as well since the WinRT SQLite can’t handle the inherited ServiceMetadata property above.

The IsDirty flag is used to identify changes (inserts and updates).

The IsTombstone is flag that’s set when deleting rows. Instead of physically deleting the row, we just set the IsTombstone flag to identify that it’s been deleted. The sync will eventually delete the row once its been synched. Since we’re only doing logical deletes, we have to make sure we exclude all IsTombstone flagged rows when querying the tables.

Also, make sure that the namespaces in the client entities and the code-generated service entities are the same.

This is a sample snippet for the a sync tool generate entity on the server side. We’ll have a look at how to generate one later.

I’ve also added another entity SyncAnchor to represent the entity that holds the sync knowledge (holds the anchor and other info for what was synched). Makes it much easier to save the byte array than having to construct SQL Command text.

Here’s how we build the tables in SQLite.

Then we modify how we do inserts and updates by adding the setting of the IsDirty flag when doing an Insert or Update.

For deletes, we set the IsTombstone flag instead of doing a physical delete

Since were not physically deleting rows, we have to exclude the rows that has IsTombstone set from Selects.

We then set the url where the sync service the client needs to interact with is deployed and specify the corresponding scope name. (A scope in Sync Framework is the definition of what will be synched in terms of tables, columns, and filters if any).

Here’s the code where we actually instantiate the Cache Controller, the providers (represented as the service and the offline provider) and initiate the sync.

But before we can sync, let’s add a Sync button on the App Bar.

And here’s the event handler for the Sync button that includes the call to do the sync and displaying a corresponding Toasts with sync statistics when sync is done.

Now that we’re done on the client side, let’s go build the server side.

I’ve included in the project the corresponding SQL Scripts for creating the SQL Server database and the two tables we want to sync. I’ve also zipped the Sync Service utilities just in case you have issues downloading and compiling the Sync Framework Toolkit.

Here’s the two tables that get’s created with the SQL Script.

We then have to define the scope or the tables, columns and filter configuration for what we want to sync. The SyncSvcUtil.zip file included in the project contains the SyncSvcUtilUI app below:

We create a new sync configuration file.

Specify database connection information.

Add a scope.

Add Tables

Select Tables and Columns and optionally specify filters.

This then generates a config file that we can use to provision our database to participate in synchronization (see: Sync Framework Provisioning on what happens during provisioning)

We then open up a Command Prompt window and execute the following to provision the database (you can also do this in the UI).

syncsvcutil.exe /mode:provision /scopeconfig:SqliteDemo.config

You then see additional database objects created by the Sync Provisioning step.

Next, we generate the corresponding WCF service by executing the following:

syncsvcutil /mode:codegen /target:server /scopeconfig:SqliteDemo.config

The above command will generate two files: xxxScopeEntities and xxxSyncService.

Next create a Web Application project to host the service and add the two files above.

We also need to add a reference to the toolkit’s Microsoft.Synchronization.Services assembly (i’ve included as well in the file download).

Make sure to modify the xxxSyncService file to specify the connection string and other settings.

If you need to test on IIS or Azure, the documentation that comes with the Sync Framework Toolkit includes information on how to deploy the service in IIS and in Azure.

Now, to test the app, we just need one more step since WinRT apps can’t access our local website hosting the service. Download Fiddler and configure exemption for the app.

Before you add any row in the client, make sure you sync once first to get the anchor initialized. Otherwise, the service will fire an exception that the ServerBlob is empty.

If you insert rows in the server database and initiate sync, you should see them downloaded in the app. You can then insert/update/delete on either side and the changes should sync.

And here’s how the app looks like with the Sync button visible at the app bar.

Sample screen shot showing toast notification when sync is done.

You can download the full source from my SkyDrive. I’ll  publish it somewhere else once am done refining the sample app.

If you run into issues, need some clarification or has suggestions, please feel free to  contact me either via the comment section or here: Contact

Cheers and looking forward to your feedback!

JuneT