Setup SQLite in a Windows 8 Metro Style App And The Unit Tests Runner

I already mentioned that I am currently porting my app to Windows 8 and Windows Phone 8. One of the functionalities that you are expecting from any SDK is the Storage API and more precisely a structured one. On Windows Phone before Mango you were left with the most basic level: the file… Painful as it was. Mango brings SQL CE and its “Linq for SQL”-like language. It’s not perfect (the update of the database schema isn’t straightforward) but you get a good relational local database (N.B. I never had a performance problem since my app does only small transactions with the storage engine).

In Windows 8 after BUILD 2011 we were back to square one: no local database, only IO operations. But since the Windows Phone Developer Summit in June 2012, Microsoft has explicitly designated SQLite as the de-facto engine for both platforms (Windows 8 and Windows Phone 8). This collaboration has already shown its benefits: a binary for WinRT and now a Visual Studio Extension to add a reference to the correct platform (x86, x64 or ARM).

The latter is now the easiest path to add SQLite to your app. You just need to install the extension, restart Visual Studio and add the reference through the “extensions” category. You still need to specify a target platform for your project but if you change it the reference will still be valid.

For the .NET developer, sqllite-net is the easiest C# library for SQLite. It’s not a full-fledged ORM but it supports creating and querying tables with generic classes. Since in WinRT you live in an asynchronous world, it provides a compatible API. Here are some samples:

- Creating a connection

[sourcecode language=“csharp”] string DbRootPath = ApplicationData.Current.LocalFolder.Path; string ConnectionString = Path.Combine(DbRootPath, StacksSqlite); new SQLiteAsyncConnection(ConnectionString, true) [/sourcecode]

- Creating tables

[sourcecode language=“csharp”] var db = SqLiteAsyncConnection(); await db.CreateTablesAsync(typeof(User), typeof(Order), typeof(Product))); [/sourcecode]

- Querying a table

[sourcecode language=“csharp”] public async Task FindByFolder(string folderId) { return await SqLiteAsyncConnection() .Table() .Where(b => b.FolderId == folderId) .OrderByDescending(x => x.DateAdded) .ToListAsync(); } [/sourcecode]

- Insert / Update

[sourcecode language=“csharp”] await SqLiteAsyncConnection().InsertAllAsync(bookmarks); await SqLiteAsyncConnection().UpdateAsync(bookmark); [/sourcecode]

Your app can be suspended (i.e. sent to the background) and re-launched or terminated (after being suspended for a “long” time). A good practice is to reset the connection pool in the OnSuspending method so that the resources are freed (and your database file is not locked). You will need to reset the connection pool during your unit tests too . I made one modification in the source code to support such scenarios: I made public the method (and some classes) SQLiteConnectionPool.Reset. And so you can add to your App.OnSuspending method:

[sourcecode language=“csharp”] private async void OnSuspending(object sender, SuspendingEventArgs e) { var deferral = e.SuspendingOperation.GetDeferral(); //here others saving state operations SQLite.SQLiteConnectionPool.Shared.Reset(); deferral.Complete(); } [/sourcecode]