Windows 10 UWP

Incorporating SQLite into a Windows 10 UWP project

In the UWP platform, Microsoft is encouraging us to all use SQLite as the local database. This post explains how we can add it (summary: there’s a few steps but it’s all straightforward).

SQLite VSIX

The first step is ensure the SQLite for Universal Windows Platform is installed. It’s delivered as a VSIX (Visual Studio Extension Installer). This will provide the core sqlite3.dll. Since it’s a VSIX, Visual Studio will remind you when an update is available.

There are two ways to download it.

The first is to go to SQLite.org and download the VSIX. If you go here, you will find it under the Universal Windows Platform header. Once you’ve got it, install it.

The link for the SQLite VSIX. Note the
The link for the SQLite VSIX. Note the “UAP” name (Uniersal App Platform). This name was changed to UWP before Windows 10 went RTM.

The second method is open Visual Studio 2015 > Tools > Extensions and Updates > Online tab. In the search field on the right, enter SQLite for Universal App Platform and you will see it in the list. Once you’ve found it, install it

SQLite VSIX available via Visual Studio 2015 Extensions and Updates
SQLite VSIX available via Visual Studio 2015 Extensions and Updates

As you can infer from the above picture, you can know you have it installed by checking your installed extensions. It will have the green tick as well.

Adding SQLite Service Reference to Your Project

In your project, right click References and select Add Reference. In the window that pops up select Universal Windows > Extensions > SQLite for Universal App Platform.

The reference you need to add to your project
The reference you need to add to your project

SQLite PCL

Almost done!

At this point you’ve got the main DLL on your system and your project is referring to it. The DLL is written in C. You need a .NET friendly wrapper for it and there are a ton to chose from.

I personally decided to go with SQLite-Net.PCL for a few key reasons: it’s an active project on GitHub and is a PCL (portable class library).

The SQLite.NET-PCL NuGet package
The SQLite.NET-PCL NuGet package

Bringing it all Together

And here’s how we can use this library in code. First we’ll create a simple table called Team.

    public class Team
    {
        [PrimaryKey, AutoIncrement]
        public int TeamID { get; set; }

        public string TeamName { get; set; }
    }

… and here we are opening a DB, creating the table, writing some data and reading it.

    var path = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "db.MyTeams" );

    var db = new SQLiteConnection(new SQLitePlatformWinRT(), path);
    db.CreateTable<Team>();

    // Example writing to the DB
    db.Insert(new Team() {TeamName = "All Blacks"});
    db.Insert(new Team() {TeamName = "Wallabies"});
    db.Insert(new Team() {TeamName = "Springboks"});

    // Reading from the table
    var query = DbUtils.SpotterDb.Table<Team>();

    foreach (var currentTeam in query)
    {
        // If you were to step through here, you'd see all the names
        var currentTeamName = currentTeam.TeamName;
    }

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s