Using SQLite for Windows RT and Windows Phone, using MVVM and PCL

There are many options to manage data in Windows RT and Windows Phone. You can store data locally on the device using XML o JSON files, you can store data on the Cloud using Azure Mobile Services, you can use local databases, etc. Each one of these solutions have their advantages and disadvantages. In this article I will focus on the use of local databases, specifically using the SQLite database. You can find more information about SQLite on their web site www.sqlite.com. The good thing about SQLite is that it works on almost any platform you can think of, including of course Windows RT and Windows Phone. If you want to effectively use PCL and have most of the logic of the app shared between Windows RT and Windows Phone, then your best option (at least, at the time I write this article) is to use SQLite.

SQLite has SDK packages for both Windows RT (8.0 and 8.1) and Windows Phone (8) that can be installed as a .vsix package into Visual Studio. These are just wrappers to the sqlite library (written in C++) and their syntax is not what we would expect if our project is using C#/XAML. Besides, both SDKs are not suitable for a PCL project and we can’t have all the data access logic in our PCL project. Nick Randolph’s blog  has a very good 4-article series on how to use SQLite in a PCL library. He basically sets four goals: the project has to be blendable, has to use SQLite, all the data access logic needs to be in a PCL project, and the data access needs to be done using objects (LINQ style). I highly recommend reading the articles since it gives a step-by-step guide on how to install SQLite SDKs, and a bunch of nuget packages to achieve the goal. You will see that it is not easy to have it all configured, and having to do that every time you start a new project might be cumbersome. But, hey! there was no other alternative at the time he wrote the article, so kudos to him.

Fortunately, there is now a nuget package that will make things easier for you: SQLite.Net PCL. This is actually a set of packages you need to install (depending on the platform) in order to use SQLite. The thing is that setting it up is not trivial, you need to take the information from a bunch of blog posts, github docs, and even some source files, and there are some gotchas you need to be aware of. In this article I will summarize my experience in setting things up, hopefully to save time to those who would like to use the package. This is a step-by-step guide, so grab a cup of coffee and be ready to spend some time reading since it is a long article.

As with Nick’s articles, we will have the same 4 goals in mind (blendable, SQLite, PCL and LINQ). Of course, if we want to effectively achieve these goals we need to architecture our projects to use MVVM. I’m a big fan of MVVM Light, and this is what I will use in this guide.

Let’s start by creating a project structure such as the one we have in the figure. We have three projects, and the suffix for the project name describes it, but lets explain it in detail:

• SampleApp.PCL: this is where all the logic for the app, including the data access layer, will be developed.
• SampleApp.WinRT: this is the WinRT (in this case Windows 8.1) application. Notice that we have included a reference to the SampleApp.PCL project
• SampleApp.WP: this is the Windows Phone (in this case, Windows Phone 8) application. Notice that we have included a reference to the SampleApp.PCL project

Notice the project structure is already organized to work with MVVM (notice the folder structure) and services and view models are already wired up with the IoC container. You can create your own project structure, or you can follow the structure I described in this set of articles:

• Creating a project structure for cross platforms apps for Windows RT and Windows Phone
• How to prepare a cross platform visual studio project for MVVM
• A sample cross platform application using MVVM

The last article presents a sample application that uses device isolated storage for the data access layer. I will use the code in that article to change the data layer to SQLite. You can download the sample project from here.

Project configuration: the platform

Before continuing you need to change the platform of the project configuration. By default, the platform is AnyCPU. If you leave as this you will get errors when trying to install the nuget packages (and later, to run the project). This is because the SQLite’s SDKs are not built for AnyCPU, they must target a specific platform. The platform you choose should be based on the following:

• For Windows Phone:
• ARM: if you run on the device and to package the app for the Windows Store
• x86: to debug and run on the emulator
• For WinRT
• x86: you can use this to debug and run on the simulator. You also need this to package the app for the Windows Store
• x64: to package the app for the Windows Store

In the case of WinRT, when submitting the app in the Windows Store, you need to upload both x86 and x64 versions of your app.

For now we will use x86 on both the WinRT and WP projects. On VS, right click the solution and choose properties. On the configuration properties node change the platform from AnyCPU to x86 for both the WinRT and WP project:

SQLite.Net nuget package on the PCL project

SQLite.Net is actually a set of nuget packages. There is a common package for the PCL project, and then there is a package for each of the platforms you want to target. Let’s us start with our PCL project. On the PCL project, using the nuget package manager, install both SQLite.Net PCL (SQLite.Net-PCL) and SQLite.Net.Async PCL (SQLite.Net.Async-PCL) packages.

SQLite.Net nuget package on the WinRT project

For the WinRT project we need to install the same packages, plus the device specific package. The device specific package is the SQLite.Net PCL - WinRT Platform (SQLite.Net.Platform.WinRT). This package depends on a package called SQLite for Windows Runtime (Windows 8.1) – Redistribution (SQLite.WinRT.redist), which installs the SQLite SDK for the WinRT platform. The problem with this package (at the time of this writing) is that it targets a version of SQLite that is not the current one (when I write this article the latest SQLite SDK is version 3.8.4.3, and the version used by the nuget package is 3.8.4.1), so if you have already installed SQLite SDKs you will get an error when installing the nuget package. To workaround this, first uninstall the version of the SQLite SDK for the WinRT platform, and then add the nuget package.

If you have a version of the SQLite SDK that is equal or lower than the one targeted by the SQLite.WinRT.redist then you’re fine. If your installed version is greater then follow these steps (again, only do this if your version of the SQLite SDK is greater than the one targeted by SQLite.WinRT.redist):

• Open Visual Studio, go to “Tools” menu, and then select “Extensions and Updates…”.
• On the dialog box, on the tree that is displayed on the left, select “Installed –> SDK”.
• Locate the extension called “SQLite for Windows Runtime (Windows 8.1)” and click Uninstall.
• Restart Visual Studio

Now you’re ready to install the nuget packages. On the WinRT project, install the SQLite.Net.Platform.WinRT package. This will attempt to install first SQLite.WinRT.redist and you will notice that a .vsix package for the SQLite SDK is installed. Unfortunately the package will fail to install the first time, I guess because VS and the nuget package manager are not aware of the newly installed SDK. The workaround is to restart VS and then attempt to install the nuget package again. This time it should install without any problems.

Now, if you’re like me, who likes to use the latest version whenever possible, you would like to use the latest version of the SQLite SDK. These are the steps you need to use the latest version:

• Uninstall the version of the SQLite SDK that was installed with the SQLite.WinRT.redist package using the steps outlined above. Close VS.
• Download the latest .vsix package from the SQLite download page. At the time of this writing is version 8.3.4.3. You will probably download a .zip file, that’s ok, just change the file extension to .vsix. Install this package.
• Now reopen your solution in VS. On the WinRT project expand the References node. You will see that the reference to the SQLite SDK has a warning:
• Remove this reference.
• Add a reference to the new SQLite SDK. You will find this reference on the node for Windows –>Extensions
• Compile to make sure everything works

Of course, if you do this, you need to repeat all the above every time you start a new project that uses SQLite. I whish that the SQLite.Net package would not depend on the SQLite.WinRT.redist, because this whole process is a pain in the a..., but anyway… let’s move on.

The SQLite.Net.Platform.WinRT will also install the base SQLite.Net-PCL package. For consistency, let’s add the SQLite.Net.Async-PCL as well. The figure shows how the WinRT project references should be at this point.

SQLite.Net nuget package on the WP project

If you haven’t done so before, install the SQLite SDK for the Windows Phone platform first. Download the latest .vsix package from the SQLite download page. At the time of this writing is version 8.3.4.3. You will probably download a .zip file, that’s ok, just change the file extension to .vsix and then install this package. Make sure you restart VS.

Now, on your WP project use the nuget package manager to install the the SQL.Net PCL – WindowsPhone8 Platform (SQLite.Net.Platform.WindowsPhone8) package.  The SQLite.Net.Platform.WindowsPhone8 will also install the base SQLite.Net-PCL package. For consistency, let’s add the SQLite.Net.Async-PCL as well.

Next, add a reference to the SQLite SDK. On the References node, choose “Add Reference..” and on the “Windows Phone –> Extensions” node select the SQLite SDK

The figure shows how the WP project references should be at this point.

Compile to make sure everything works fine.

Using SQLite

Now for the fun part, let’s change or data access to use SQLite. If you’ve followed along with my project we will make changes to our existing model and configure a new data access to be injected in our IoC container. If you’re not following along with my project, that’s ok, just create the files with the full code below.

The model

Let’s add an id property to our model. This id will be our primary key for the object. On the PCL project, on the “Models” folder, open the file Person.cs and make the following changes:

using GalaSoft.MvvmLight;
using SQLite.Net.Attributes;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SampleApp.Models
{
    public class Person : ObservableObject
    {
        private int id;
        [PrimaryKey, AutoIncrement]
        public int Id 
        {
            get { return id; }
            set { Set(ref id, value); }
        }

        private string name;
        public string Name
        {
            get { return name; }
            set { Set(ref name, value); }
        }
    }
}

Notice the use of the PrimaryKey and AutoIncrement attributes (which are self explanatory).

The storage service

SQLite is just a file in the device storage. We will save this file on the application isolation storage. We need to tell the SQLite API the location of this file on the device. The location of a file on the device is dependent of the device, so this needs to be handled by a service. In our project we already have a service that handles storage specific actions. On the PCL project, on the “Services\Interfaces” folder, open the IStorageService.cs file and add the highlighted code:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SampleApp.Services
{
    public interface IStorageService
    {
        Task SaveTextAsync(string file, string text);

        Task<string> LoadTextAsync(string file);

        Task<string> GetPathForFileAsync(string file);
    }
}

The GetPathForFileAsync method will provide SQLite with the full path to the database file. To implement this new method go to the WinRT project and on the “Services” folder open the file StorageService.cs and add the method implementation as follows:

public async Task<string> GetPathForFileAsync(string file)
{
    StorageFile storageFile = await ApplicationData.Current.LocalFolder.CreateFileAsync(file, CreationCollisionOption.OpenIfExists);

    return storageFile.Path;
}

Notice the use of StorageFile API to create the file, and the CreationCollisionOption.OpenIfExists option used. For SQLite to work the file needs to exist. If the file doesn’t exists you will get an error when trying to use the SQLite API. This is one of the gotchas to be aware of. Notice also that the file is created on the application local folder.

The data service

We will create our data service for SQLite now. On the PCL project, on the “Services” folder, add a new class and name it SQLiteDataService.cs:

using SampleApp.Models;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using SQLite.Net;
using SQLite.Net.Interop;
using SQLite.Net.Async;
using System.IO;

namespace SampleApp.Services
{
    public class SQLiteDataService : IDataService
    {

        private IStorageService storageService;
        private ISQLitePlatform sqlitePlatform;

        private SQLiteAsyncConnection connection;

        public SQLiteDataService(ISQLitePlatform sqlitePlatform, IStorageService storageService)
        {
            this.sqlitePlatform = sqlitePlatform;
            this.storageService = storageService;

            InitializeAsync();
        }

        private async Task InitializeAsync()
        {
            if (connection == null)
            {
                string databaseFile = await storageService.GetPathForFileAsync("database.sqlite");
                connection = new SQLiteAsyncConnection(() => new SQLiteConnectionWithLock(sqlitePlatform, new SQLiteConnectionString(databaseFile, false)));
                await connection.CreateTableAsync<Person>();
            }
        }

        public async Task<IEnumerable<Person>> GetPersonsAsync()
        {
            return await connection.Table<Person>().ToListAsync();
        }

        public async Task AddPersonAsync(Person person)
        {
            await connection.InsertAsync(person);
        }
    }
}

There are a couple of things to notice here. Remember that we are using the PCL version of the SQLite.Net package. This PCL version actually relies on the platform specific implementation of SQLite. All PCL methods are based on the ISQLitePlatform interface. Each platform implements this interface and handles the platform's specific details for SQLite. In our case we are injecting the specific platform implementation using our IoC container. Notice how the constructor expects a ISQLitePlatform object which is assigned to a local property (lines 18 and 24).

The other thing to notice is the initialization of the database. First we get a reference to the file using our storage service (line 34) and then a SQLiteAsyncConnection is instantiated by passing the ISQLitePlatform implementation and the full path to the database file (line 35). I’m using the async implementation of the API, but you can use the synchronous one as well. The key thing is the platform specific object passed on the constructor.

The other parts of the file don’t deserve a details explanation since they just use the SQLite API (for more info refer to the SQLite docs or to the SQLite.Net docs).

We need to tell the IoC container to use this new data service. On the PCL project, on the “Services” folder, open the LocatorService.cs file and make the changes highlighted below:

using GalaSoft.MvvmLight;
using GalaSoft.MvvmLight.Ioc;
using Microsoft.Practices.ServiceLocation;
using SampleApp.ViewModels;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SampleApp.Services
{
    public class LocatorService
    {
        static LocatorService()
        {
            ServiceLocator.SetLocatorProvider(() => SimpleIoc.Default);

            // Services
            if (ViewModelBase.IsInDesignModeStatic)
            {
                SimpleIoc.Default.Register<IDataService, Design.DataService>();
            }
            else
            {
                SimpleIoc.Default.Register<IDataService, Services.SQLiteDataService>();
            }

            // View Models
            SimpleIoc.Default.Register<IMainViewModel, MainViewModel>();
        }

        public IMainViewModel MainViewModel
        {
            get
            {
                return ServiceLocator.Current.GetInstance<IMainViewModel>();
            }
        }

        public static void Cleanup()
        {
        }
    }
}

Also, we need to register with the IoC container the platform specific implementation of the ISQLitePlatform interface on both the WinRT and WP projects. On the WinRT project, on the “Services” folder, open the LocatorService.cs file and make the changes highlighted below:

using GalaSoft.MvvmLight;
using GalaSoft.MvvmLight.Ioc;
using Microsoft.Practices.ServiceLocation;
using SQLite.Net.Interop;
#if NETFX_CORE
using SQLite.Net.Platform.WinRT;
#else
using SQLite.Net.Platform.WindowsPhone8;
#endif
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SampleApp.Services
{
    public class DeviceLocatorService
    {
        static DeviceLocatorService()
        {
            ServiceLocator.SetLocatorProvider(() => SimpleIoc.Default);

            if (ViewModelBase.IsInDesignModeStatic)
            {
            }
            else
            {
            }

            #if NETFX_CORE
            if (!SimpleIoc.Default.IsRegistered<ISQLitePlatform>()) SimpleIoc.Default.Register<ISQLitePlatform, SQLitePlatformWinRT>();
            #else
            if (!SimpleIoc.Default.IsRegistered<ISQLitePlatform>()) SimpleIoc.Default.Register<ISQLitePlatform, SQLitePlatformWP8>();
            #endif

            if (!SimpleIoc.Default.IsRegistered<IStorageService>()) SimpleIoc.Default.Register<IStorageService, StorageService>();
        }

        public static void Cleanup()
        {
        }
    }
}

Notice the use of compiler directives, which is another trick to share files between WP and WinRT projects. In our case the LocatorService.cs file is a shared file (the WP project as a link to it from the WinRT project). We register the appropriate ISQLitePlatform interface: on WinRT we use SQLitePlatformWinRT and on WP we use SQLitePlatformWP8. With this, the IoC container will inject the appropriate SQLite implementation in the PCL data service.

Compile and make sure everything works fine. You should now be able to add objects to your SQLite database on both WinRT and WP, and all the data access logic developed on the PCL project. You can see the contents of your SQLite database using a tool such as sqlitebrowser.

You can download the sample project for this article from here: