UPDATED HOWTO: SQLite with Windows 8 apps

I’ve previously posted a few things about SQLite including a HOWTO on how to build from their source code.  If you still want to build your own DLL from their source code that is totally fine, but not necessary in most every single case I’ve seen in app usage.  One of the challenges I noted is that since SQLite is a native component and if you are a managed (.NET) app you can’t be architecture neutral anymore (AnyCPU).  What this means is that you have to build your app for each architecture you want to support: x86, x64 and ARM.  The flow of this using SQLite3.dll was that you would have to package, change the DLL, re-package. 

Not anymore.

In working with the great folks on the SQLite team, they’ve packaged the binaries up (for Windows 8 apps) in a nice installer using the Extension SDK format.  What this means is you now add a ‘reference’ to the SQLite binary and based on the architecture being built for your package, it will pull in the right DLL without you having to manage that yourself.  Here’s some step-by-step…

Installing the SQLite for Windows Runtime package

The first thing you want to do is install the package.  You can do this from within Visual Studio itself in all editions.  From the Tools menu, choose Extensions and Updates and then choose the Online section (on the left of the dialog) and search for ‘sqlite’ in the search term.  This will show you the SQLite for Windows Runtime package:

Click install my friends.  You will be prompted to restart Visual Studio which you should do.  Go ahead…I’ll wait.

Using the new package in your C#/VB app

Now that you have the SQLite for Windows Runtime package installed in your Visual Studio environment, you want to use it.  In a managed (.NET) app you would do the following steps.

First, create your app (e.g., a Blank XAML app is fine).  Once within your app, use the Add Reference mechanism to get to the next step.  Now you will not be browsing for any DLL directly like you would in a traditional .NET.  What we are adding here is a reference to the Extension SDK…not the library itself, a small but important distinction.  Once in the Add Reference dialog choose the Windows\Extensions view (see on left) and you’ll see SQLite for Windows Runtime.

To correctly use this in a managed app you’ll need to select that *and* the C++ runtime as seen below:

The reason for this is to ensure that your app declares the right dependencies that are needed for the app to run.  The likelihood of the C++ runtime not being on a Windows machine where your app will be installed is extremely rare, but you need to declare this anyway.  Failure to do so will fail your app certification tests.

Now with this involved you can grab a managed wrapper to call the SQLite APIs as I’ve previously described in my HOWTO video.  I personally recommend the sqlite-net library (available via NuGet) to make this easier for you.

NOTE: sqlite-net is available as source in C#.  If you are using a VB app, you would first need to compile the sqlite-net source in a separate DLL so you can just add a reference to that in your VB project.

Using the sqlite-net library you can perform tasks using a model similar to LINQ2SQL where you can have types represent database entities:

   1: public sealed partial class MainPage : Page

   2: {

   3:     public MainPage()

   4:     {

   5:         this.InitializeComponent();

   6:         LoadData();

   7:     }

   8:  

   9:     public void LoadData()

  10:     {

  11:         var dbPath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.sqlite");

  12:         using (var db = new SQLite.SQLiteConnection(dbPath))

  13:         {

  14:             db.CreateTable<Person>();

  15:  

  16:             db.RunInTransaction(() =>

  17:                 {

  18:                     db.Insert(new Person() { FirstName = "Tim", LastName = "Heuer" });

  19:                 });

  20:         }

  21:     }

  22: }

  23:  

  24: public class Person

  25: {

  26:     [SQLite.AutoIncrement, SQLite.PrimaryKey]

  27:     public int ID { get; set; }

  28:     public string FirstName { get; set; }

  29:     public string LastName { get; set; }

  30: }

Now you just need to specify your architecture for your app (x86, x64, ARM) and when you build, the appropriate sqlite3.dll will be packaged in your app automatically.  This also happens during the packaging step for the store so the right item is included for each architecture-specific package.

WARNING: Do not package in DEBUG mode as you will fail certification.  Ensure that you build/package in RELEASE mode prior to submitting to the store or running the app certification toolkit (also referred to as WACK in some places).  You will get false positives if you are compiled in DEBUG mode.

This should make your development much easier without having to  swap out DLL files each time.

Using the new package in your C++ app

If you are a C++ developer you will do the same steps for installing and adding as a reference to your app.  In the C++ project system there is no 'Add Reference’ menu on the project context menu, but you will choose References and then the Add Reference button shows up.

Once you have the reference to the SQLite SDK then as a C++ developer you can just #include the header and go to work:

   1: #include <sqlite3.h>

Since C++ projects are already architecture-specific you don’t have to worry about the AnyCPU situation because there isn’t one!  You’ll get IntelliSense on the API by just including the header.  The Extension SDK mechanism already includes the C++ props file to help the project system know where to get the header for development and the lib for linking when building.  Most C++ developers will interact with SQLite using the native APIs and not need any additional wrapper library.

Using the new package in your JavaScript app

If you are using JavaScript/HTML to developer your app, you will follow the same flow as the C#/VB flow.  Add a reference to both the SQLite SDK as well as the C++ runtime (to declare the dependency).  As to accessing SQLite in your app, you’ll need a WinRT wrapper library to do that.  The one that seems to be gaining favor is the SQLite3-WinRT library on GitHub.  I have not personally used this, but seen a lot of people using this.  It allows you to use the JavaScript programming model in a familiar way:

   1: var dbPath = Windows.Storage.ApplicationData.current.localFolder.path + '\\db.sqlite';

   2: SQLite3JS.openAsync(dbPath)

   3:   .then(function (db) {

   4:     return db.runAsync('CREATE TABLE Item (name TEXT, price REAL, id INT PRIMARY KEY)');

   5:   })

   6:   .then(function (db) {

   7:     return db.runAsync('INSERT INTO Item (name, price, id) VALUES (?, ?, ?)', ['Mango', 4.6, 123]);

   8:   })

   9:   .then(function (db) {

  10:     return db.eachAsync('SELECT * FROM Item', function (row) {

  11:       console.log('Get a ' + row.name + ' for $' + row.price);

  12:     });

  13:   })

  14:   .then(function (db) {

  15:     db.close();

  16:   });

If using JavaScript this might be the way to go for your app.

Summary

I’m very glad the SQLite team worked to get this deployment package out there.  I think for some Microsoft developers, using SQLite is fairly new and this SDK package will make it easier to ensure you have the right bits at the right time.  Of course you are free to do it your own way, but I think this will ease the process a little bit.

Why no NuGet? Well, the NuGet infrastructure right now doesn’t support some of these semantics around native components to deal with headers, linking and architecture-specific deployments.  We’ll continue to work with them to see if we can drive these changes into that platform.

So please feel free to download via the Visual Studio ‘Extensions and Updates’ option from within VS, download directly from the Visual Studio Gallery, or download from the SQLite site themselves.  Once installed, once an update is available, VS will notify you that an update is available and you can install it.

Hope this helps!