Mobile Development at U2U Consult

Based in Brussels, Belgium, U2U Consult has been offering consulting & development services in the EMEA region for over 10 years. We offer mobile development on all platforms from Apple to Android & Microsoft.

This article explains how to get started with SQLite in an UWP XAML app. SQLite is a cross-platform single-user public-domain (read: ‘free’) embedded relational database engine. It doesn’t require a server and it doesn’t require configuration, but it still provides all essential relational database features including SQL syntax, transactions, indexes, and even common table expressions. I created an UWP sample app that

Then add a reference to the engine and to the C++ runtime on which it depends (Visual Studio will warn you if you forget the latter):

Your code now has access to the SQLite engine, but it makes sense to install a .NET wrapper to facilitate this. At the time of writing this article, it looks like most of the Windows 8.1 wrappers on NuGet are broken. The problems are caused by changes in NuGet. In NuGet 3.1 a package cannot add source code files to a project anymore. The original SQLite.NET package uses this feature, so it cannot be used in Visual Studio 2015. Fortunately a spin-off from this package, the SQLite.NET PCL package and also its asynchronous version still do the trick:

SQLite.Net-PCL exposes a rich API to your code:

Here’s the entire signature of the SQLiteConnection class:

The SQLiteConnection constructor takes the platform type and the path to the file that hosts the database. Here’s how the methods in the data access layer of sample app get a connection:

The code-first approach works very well in SQLite: the easiest way to create tables is by first defining your model classes. I created a Person class, with some properties of simple atomic data types, but also some more challenging data types: a Picture (a Blob in the database, a byte array in the model, an ImageSource in the viewModel) and a DateTime (a typical show stopper in many cross platform environments). The model class members are decorated with attributes to indicate keys, value generation algorithms, and validation constraints:

To create the table, just call … CreateTable … and provide the model type. For those who want to inspect the table structure programmatically, GetMapping reveals the entire table definition (columns, keys, indices, mappings):

Records can be saved to the table by InsertOrReplace. The Id property was set to AutoIncrement, so it’s not mandatory for new records. But you can provide a value if you want. This is what I do in the initial load of the table:

All SQLite related code in the sample app is consolidated in the Dal.cs class. The rest of the app tries to mimic a MVVM business app. Here it is in Edit mode, with all TextBlocks replaced by TextBoxes, DatePicker, and Buttons that open FilePickers:

There’s a converter between the DatePicker’s Date property (of type DateTimeOffset) and the Person’s DayOfBirth (of type DateTime). If you don’t do that, then the user’s time zone is taken into account, and that can give nasty surprises:

Otherwise -on my machine- a value of DateTime(2015, 25, 12) would appear as ‘24 DEC 2015’ after the subtraction of one hour for the time zone and one hour for daylight savings time. That’s the kind of confusion you want to avoid, I guess. Anyway, here’s how the UWP DatePicker looks like on a tablet:

That’s it. As you see, SQLite is feature rich and easy to use on any platform, including the Universal Windows Platform.

As usual, the source code of the sample app lives on GitHub. It was written in Visual Studio 2015.