Others Talk, We Listen.

Cross-Platform SQLite Support – Part 1

by Nicholas Cipollina on Feb 26, 2014

In this 3 part series, I’m going to walk through creating a very simple ToDo project that will target Windows Phone and Windows Store to illustrate how to build a PCL for storing SQLite data that both platforms will use.

SQLite has become the de-facto standard for local databases on mobile devices running  Android, iOS, or any of the Microsoft mobile platforms.  Since the Windows Phone and Windows Store platforms are separate, this means you have to develop an application targeting each one.  To target both platforms with a shared codebase, I recommend using PCL and this post will show you how.

The Portable Class Library project is a project type that was introduced in Visual Studio 2010 that allows you to write code you can use target multiple platforms. Portable Class Libraries support a subset of assemblies from the .NET Framework, Silverlight, .NET for Windows Store apps, Windows Phone, and Xbox 360.  If you don’t use a PCL, you would have to target each single app type, and then manually rework any code for the other app types.  PCLs provide a great mechanism for developing as much code in one place as possible and sharing it with all of your different apps. 

In part 1 of this series, we will create our Portable Class Library project that allows us to access SQLite from different platforms.  In Part 2, we will add our Windows Store App to the Solution.  Part 3 will cover adding the Windows Phone App to the solution.  If you want to skip ahead and get the source code, it can be found here.

Creating the Solution

To begin create an empty solution in Visual Studio 2013.  Right click on the new solution in Solution Explorer and select Add à New Project.  Select Visual C# and Portable Class Library and click OK.

Add PCL Project

You will then be prompted to select your Target frameworks.  For this example select Windows Store apps (Windows 8.1) and Windows Phone 8.  This determines what classes are available to the PCL project.  Click OK.

Target Framework

This adds your new PCL project to the solution.  Delete the Class1.cs file that is added to the project by default.  The next step is to add SQLite to our PCL project.

Adding SQLite

Before adding the SQLite classes to your project make sure that you have SQLite for Windows Phone and Windows Runtime (Windows 8.1) installed.  You do this by selecting Tools & Extensions and Updates from the menu in Visual Studio.  You would then search for SQLite and select each and click the Install button for both.  After this is done we can add the SQLite NuGet packages to the project.

Right-click on the project in Solution Explorer and Select “Manage NuGet Packages…”.  In the search box type in sqlite-net and hit enter.  Select SQLite.Net.Async PCL and install.

Add SQLite PCL

This is a fork of the sqlite-net, a popular lightweight library created to provide a managed way to interact with SQLite .  The source code for SQLite PCL can be found here.

After adding the SQLite packages the next step is to create our table object.  Create a new class and call it ToDo.  The class will look like this:

 
    using SQLite.Net.Attributes; 
    using System; 
    namespace Shared 
    { 
        public class ToDo 
        { 
            [PrimaryKey, AutoIncrement] 
            public int Id { get; set; } 
            public string Text { get; set; } 
            public DateTime TimeStamp { get; set; } 
        } 
    } 
 

The PrimaryKey attribute designates the Id column as the primary key and the AutoIncrement attribute designates it as an identity column so that inserting a new record will automatically give the record the next id value. Next create a new class called Database. We will use this class to create our database and all associated tables. We will also add our CRUD logic to this class.

Create the constructor for this class. It should look like this:

 
    public Database(ISQLitePlatform platform, string databasePath) 
    { 
        _connectionParameters = new SQLiteConnectionString(databasePath, false); 
        _sqliteConnectionPool = new SQLiteConnectionPool(platform); 
        _dbConnection = new SQLiteAsyncConnection(() => 
        _sqliteConnectionPool.GetConnection(_connectionParameters)); 
    } 
 

The ISQLitePlatform interface in the constructor is essentially the interface that interacts directly with SQLite for the given platform. The SQLite PCL project provides implementations of this interface for Silverlight, Win32, WinRT, WindowsPhone 7.1, WindowsPhone 8, Xamarin for Android, and Xamarin for iOS. As of the writing of this blog post there are no NuGet packages available for Windows Phone or Windows Store. The databasePath parameter is the actual path to the file where the SQLite database will exist in the file system. After that we create our connection string using the path and specifying that DateTimes should not be stored as ticks. We then create a new SQLiteConnectionPool for the specified platform and use that to create our new SQLiteAsyncConnection. The SQLiteAsyncConnection will actually be used to do all of our interaction to SQLite from our apps.

Next we’ll add a method to create our table in the database. I’ve chosen to call this Initialize as this is a method that would normally run once during the application life cycle. It looks like this:

 
    public async Task Initialize() 
    { 
        await _dbConnection.CreateTableAsync<ToDo>(); 
    } 
 

In this method we simply use our SQLiteAsyncConnection to create our ToDo table. One thing to note about this method, it can actually be called multiple times. If the table doesn’t exist SQLiteAsyncConnection will create it, if the table does exist SQLiteAsyncConnection will alter it to match the object.

The last step in creating this class is to create our CRUD methods. They look like this:

 
    public async Task<int> AddNewToDo(ToDo item) 
    { 
        var result = await _dbConnection.InsertAsync(item); 
        return result; 
    } 
 
    public async Task<int> UpdateToDo(ToDo item) 
    { 
        var result = await _dbConnection.UpdateAsync(item); 
        return result; 
    } 
 
    public async Task<int> DeleteToDo(ToDo item) 
    { 
        var result = await _dbConnection.DeleteAsync(item); 
        return result; 
    } 
 
    public async Task<list<ToDo>&gt; GetAllToDos() 
    { 
        var result = await _dbConnection.Table<ToDo>().OrderByDescending(t =&gt; t.TimeStamp).ToListAsync(); 
        return result; 
    } 
 
    public async Task<ToDo> GetToDoById(int id) 
    { 
        var result = await _dbConnection.Table<ToDo>().Where(t =&gt; t.Id == id).FirstOrDefaultAsync(); 
        return result; 
    } 
 

If you’ve ever worked with LINQ to SQL or Entity Framework these methods should all seem pretty familiar. This is pretty much all we need to interact with SQLite. The next step is to create our platform apps.

We now have a PCL project that we can use to interact with SQLite from either a Windows Phone or a Windows Store application. Before PCL, this was a very difficult task to accomplish and developers would often have to use #if compiler directives to target multiple platforms from the same code. In our next post, we will create our Windows Store application that will leverage the code we wrote in this post.