Using SQL/SQLCe–Based Offline Stores with Azure Mobile Services

A while back, the Azure Mobile Services team announced and released the early bits for supporting offline data sync with Azure Mobile Services using SQLite as an offline store(see: Using offline data sync in Mobile Services). I got more than a couple queries if it’s a good alternative to Sync Framework.

Why use SQL or SQL CE with Azure Mobile Services you may ask. Well, there’s still some shops who’s using Sync Framework or the Sync Framework Toolkit for their sync SDK needs. Given that there’s not much going on with both products (Azure SQL Data Sync still remains a preview product), there’s not much out there as a substitute (you may want to check out Zumero though).

Azure SQL Data Sync doesn’t provide programmatic interfaces, so if you’re trying to sync with an Azure SQL Database programmatically, it’s either you roll your own mechanism or you use either Sync Framework or Sync Framework Toolkit (check out the good work of @sebastienpertus keeping Sync Framework Toolkit alive at https://github.com/Mimetis/SyncWinRT). 

While the Azure Mobile Services Offline Support is not feature equivalent to either Sync Framework, Azure SQL Data Sync or SQL Replication, it’s actually very easy to use. In fact, if you’re already using Mobile Services, there’s hardly any change in the way you code when you enable the sync support (check out my prior post here)

Forget for a while that Azure Mobile Services is just for Mobile (it’s a very good backend service imho), it’s actually a good alternative for those building desktop apps (WPF/WinForms anyone?) that require sync functionalities with Azure SQL Database or even on-premises SQL Server using the Azure Hybrid Connection.

The good news is, you can actually build your own local, offline store. So I had a crack at porting the existing SQLite-based offline store (it’s open-source btw, go check out here) to support SQL and SQL CE for a start (with some encouragement from @lindydonna ).

I’ve published two Nuget packages based on the  current beta of Azure Mobile Services’s Offline support. You’ll find a SQL Offline Store that can be used for SQL Server/Express/LocalDB/AzureSQL and a SQL CE Offline Store for use with SQL CE 4. 

Using the above packages is pretty simple.

Where the default SQLite-based store is opened this way:

var store = new MobileServiceSQLiteStore("localsync.db");

For SQL Ce, you simply substitute using the MobileServiceSqlCeStore  as follows:

var store = new MobileServiceSqlCeStore(@"c:\temp\<yoursqlcedb>.sdf");

For SQL Server/Express/LocalDb/AzureSQL, you’d use MobileServiceSqlStore as follows :

var store = new MobileServiceSqlStore(@"Data Source=<yourserver>;Initial Catalog=<yoursqldb>;Integrated Security=SSPI;");

And that’s it. Everything else should be the same as if you’re using the SQLite store.

var mobileService = new MobileServiceClient("https://<yourapp>.azure-mobile.net/","<yourappkey>");
var todoTable = mobileService.GetSyncTable<TodoItem>();
            
if (!mobileService.SyncContext.IsInitialized)
{
    //use SQLite store
    //var store = new MobileServiceSQLiteStore("localsync.db");
                
    //use SQL Ce Store
    var store = new MobileServiceSqlCeStore(@"c:\temp\<yoursqlcedb>.sdf");
                
    //use SQL store
    //var store = new MobileServiceSqlStore(@"Data Source=<yourserver>;Initial Catalog=<yoursqldb>;Integrated Security=SSPI;");

    store.DefineTable<TodoItem>();                
    await mobileService.SyncContext.InitializeAsync(store, new MobileServiceSyncHandler());
}

// download 
await todoTable.PullAsync(todoTable.Where(todoItem => todoItem.Complete == false));

// insert item
await todoTable.InsertAsync(todoItem);
            
// upload
await mobileService.SyncContext.PushAsync();

You can find the code for both offline stores and a sample WPF application at:  ZumoContrib

Let me know how it works.

Advertisement