Storage on Windows 8 and Windows Phone (part 2)

Following part 1 of this series, which covered local and roaming storage on Windows 8 and Windows Phone, this post will cover the ability to utilize SQLite on Windows 8 and Windows Phone 8 projects as a local relational database. We won’t cover the support for Windows Phone 7, but there are some good resources out there on how to make things work on that platform as well.

The first thing you will need to start using SQLite in your projects, is add the support for it in Visual Studio. You can do so by installing the SQLite for Windows Runtime and the SQLite for Windows Phone extensions. Once you’ve done that, you can find the libraries in the Reference Manager under Windows Phone -> Extensions or Windows -> Extensions.

Windows 8

To use SQLite in your Windows 8 project, the easiest thing to do is to use the sqlite-net wrapper by Frank Krueger. Tim Heuer from Microsoft has created (and maintains) a NuGet package that you can use in your project. The sqlite-net wrapper offers a bunch of methods for easy usage of SQLite, both synchronous as asynchronous. Adding a simple entry to the database for instance is done as follows:

var conn = new SQLiteAsyncConnection("entries");
var newEntry = new Entry { DateTime = DateTime.Now.ToString("dd-MM-yyyy HH:mm:ss"), Title = "Some title" };
await conn.InsertAsync(newEntry);

As this is a “code-first” solution, creating the entries database is also quite simple:

var conn = new SQLiteAsyncConnection("entries");
// Check if table "Entry" exists
var count = await conn.ExecuteScalarAsync<int>("SELECT COUNT(*) FROM sqlite_master WHERE type='table' AND name='Entry'");
if (count == 0)
    await conn.CreateTableAsync<Entry>();

Windows Phone 8

As of writing this article, the sqlite-net wrapper does not have support for the native SQLite Windows Phone 8 extension that we downloaded from the Visual Studio Gallery earlier. Instead, it relies on an open-source implementation of SQLite called csharp-sqlite.

While we could opt to use this library with the sqlite-net wrapper, there is an alternative: Peter Huene has created a fork of the sqlite-net wrapper that allows use of the native SQLite Windows Phone 8 extension. This fork has recently been pulled into the sqlite-net master branch and the latest NuGet package (1.0.7 at the time of writing) also reflects this update.

There’s a few things we need to get done to get this all working:

  1. Add the sqlite-net package (1.0.7) to our Windows Phone app project
  2. Download and add the sqlite-net-wp8 native C++ project to our solution
  3. This is the wrapper project that will replace the csharp-sqlite functionality. At this time, there’s no NuGet package available for sqlite-net-wp8, so we’ll need to download and add it to our project manually.

  4. Add a reference to the sqlite-net-wp8 native C++ project from our Windows Phone app project and add the USE_WP8_NATIVE_SQLITE compiler directive to it
  5. To tell sqlite-net we want to use the sqlite-net-wp8 project we added in step 2, we’ll need to add a compiler directive to the Windows Phone app project. Right-click on the project and select Properties. On the Build tab you’ll see Conditional compilation symbols under the General header, containing a default value of SILVERLIGHT;WINDOWS_PHONE on a Windows Phone app project. Change the value to SILVERLIGHT;WINDOWS_PHONE;USE_WP8_NATIVE_SQLITE and save the project file.

    Add conditional compilation symbol

The actual code you use to do SQLite operations is now exactly the same as the above example for Windows 8, so you can architect your solution in such a way that you can re-use a lot of your data access between the two platforms. Refer to the sample solution at the end of this post for an example of everything discussed in this post and in part 1 of this series.

Downloads

Local Storage on Windows 8 and Windows Phone sample - LocalStorage.zip (366 downloads)

6 thoughts on “Storage on Windows 8 and Windows Phone (part 2)

  1. How do I test if the database exists and how do a create the database, lets call it “mySQLiteDB.db”

    • Hi Tony,

      If you use the sqlite-net wrapper, you can simply initialize an instance of SQLiteAsyncConnection with the name of your database, so in your case:

      var conn = new SQLiteAsyncConnection(“mySQLiteDB.db”);

      This will open the database connection and create the database if it does not exist. You can take a look at the SQLite.cs and SQLiteAsync.cs files that are added with the sqlite-net wrapper to learn more about how the initialization is implemented.

  2. Pingback: Using SqlLite in Windows Phone 8 | Enzo Contini Blog

Leave a Reply