Others Talk, We Listen.

Cross-Platform SQLite Support – Part 2

by Nicholas Cipollina on Feb 27, 2014

In Part 1 of this series, we created our Portable Class Library (PCL) project that allows us to access SQLite from different platforms. In this post, 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.

Adding the Store App

To add the store app project, right click on the solution in Solution Explorer and select Add --> New Project. Select Windows Store and Blank App (XAML) and click OK.

Add Store Project

This will add the store app project to our solution. Next we need to add a few references. First let’s add a reference to SQLite. To do this, right-click on References and select "Add Reference". In the Reference Manager dialog, expand Windows and select Extensions. We will need to add the Microsoft Visual C++ 2013 Runtime Package and SQLite for Windows Runtime (Windows 8.1). Select these and click OK.

SQLite and C++ references

If you try to build the solution right now, you’ll probably get an error message that is something like this:

The processor architecture of the project being built "Any CPU" is not supported by the referenced SDK "Microsoft.VCLibs, Version=12.0". Please consider changing the targeted processor architecture of your project (in Visual Studio this can be done through the Configuration Manager) to one of the architectures supported by the SDK: "x86, x64, ARM".

To fix this you will need to set the architecture in Configuration Manager to x86 for this example.

Configuration Manager

Now we need to add a reference to SQLite.Net PCL – WinRT Platform from NuGet.

Add Store Platform

This will add a reference to the SQLite.Net PCL package and it will also add a reference to the SQLite WinRT Platform implementation. In part 1 of this series, I mentioned an implementation of this is provided in the SQLite.Net-PCL project on GitHub but no NuGet package currently exists. Since then one has been added to NuGet, and so now it’s much easier to add all the logic to your project you need for SQLite.

Now we can add the logic to display our ToDo items from the SQLite database. In my store app I’ve created two pages, MainPage.xaml and ToDoItem.xaml. The MainPage.xaml contains a gridview to show all of our ToDo items in descending date order. The ToDoItem.xaml page is used for adding new ToDo items, editing existing ToDo items, and deleting existing ToDo items.

I’m not going to cover all of the logic I used to create the views and how they interact with the code behind, but I am going to cover how we utilize the PCL assembly we created. In the code behind for the MainPage.xaml.cs file we create an instance of our Database class like this:

private Database _database;
 
private async Task<Database> GetDatabase()
{
	if (_database == null)
	{
		_database = new Database(new SQLitePlatformWinRT(),
			Path.Combine(Path.Combine(ApplicationData.Current.LocalFolder.Path, "todo.sqlite")));
		await _database.Initialize();
	}
 
	return _database;
}

You’ll notice that when we create our new Database object we pass in a new instance of SQLitePlatformWinRT. This the implementation of the ISQLitePlatform interface for Windows Store apps that we added from the SQLite.Net.Platform.WinRT project. Also, we are specifying that the data should be stored in our local App Data folder. We are putting it here because we need the SQLite database to be persistent and local to this device. If we wanted to have the data be available on multiple devices, we could put it in the roaming App Data folder. Then we call the Initialize method, which creates our ToDo table in the database.

Now that we have an instance of Database we can use the following logic to perform our CRUD logic. To get a list of ToDo items we do the following:

 ItemsGrid.ItemsSource = await database.GetAllToDos();
 

To get a single ToDo:

 DataContext = await database.GetToDoById(id);
 

To insert a new ToDo item:

 result = await database.AddNewToDo(item);
 

To update an existing ToDo item:

 result = await database.UpdateToDo(item);
 

To delete an existing ToDo item:

 await database.DeleteToDo(item);
 

We now have all of our logic in place to perform all the necessary operations against our SQLite database from our Windows Store app. In the final part of this series we will add our Windows Phone project to the solution and you will see that the logic is very similar to what we’ve just done in the Windows Store project.