Increasingly the projects that we’re undertaking at Built to Roam and Pixel and Sons span both Windows Phone and Windows 8 (aka WinRT). For anyone who’s worked across these platforms you’ll be aware that they’re similar, yet frustratingly different, requiring a significant amount of rework when building applications for both. We’ve attempted to minimise this through the use of both MVVM and Portable Class Libraries. The former allows us to have view models that work across both platforms, the latter allow us to house those view models (and a large proportion of our application logic) in a reusable library that is shared (without the need to recompile) between both our Windows 8 and Windows Phone applications.
One of the areas that we’ve avoided to date is the need to store data using an actual database (across both platforms). We’ve had Windows Phone projects where we’ve used Linq to Sql or the managed wrapper for sqlite; for other projects we’ve chosed to store either xml or json to a flat file on disk. This post is about the learnings taken from a relatively frustrating week trying to see how Microsoft has delivered (or hasn’t delivered) on their announcement last year to support sqlite across both Windows Phone and Windows 8.
Firstly, let me talk about my goals:
– Blendable: My project has to be designable in Blend at all times!
– Sqlite: I want to use sqlite to store relational data across both Win8 and WP applications
– PCL: I want to be able to do all my data access from a portable class library
– Objects Not SQL: I want to be able to read and write objects, rather than write sql statements
Let’s go through these in detail:
Blendable
You might think this a silly requirement when referring to data access. However, I have this as a prerequisite for any library or component that I’m considering. If it ain’t Blendable, it doesn’t make it through the door. I work with some awesome designers who actually know how to work with XAML. I don’t want them hand crafting it, or ideally opening VS, as this just lowers their productivity.
Sqlite
Well duh! Although actually it’s not such a silly requirement – in a lot of cases there is a massive trade off between flat files (super simple) and database (quick but frustrating to work with). More often than not, flat files win as we’re not scanning large number of items by index or doing large complex joins (that’s server stuff, typically).
PCL
I want to reuse an entire library without recompiling across both Windows 8 and Windows Phone, not just share code with # conditional compilation blocks.
Objects Not SQL
Seriously, this is like writing apps in HTML v’s XAML (oh wait, people still do this….let me think of a better example). Writing data access by hand crafting sql statements is for muppets, particularly on a client device where you shouldn’t be doing massive heavy lifting. Do this, and you should be considering which mainframe you’re going to be maintaining in 5 years time. Get with the program, use some object to sql mapper that doesn’t mean you’re writing sql code for client applications.
I think I’ve been pretty clear on my goals, so lets get into it and look at the options:
1) Use the existing managed sqlite library that was created for Window Phone 7.
(this one has been discounted as I don’t believe we’re after a managed implementation of sqlite as we’re not going to get the level of performance we really want)
2) Use sqlite-net (in conjunction with sqlite-net-wp8, a native to managed wrapper layer for Windows Phone)
3) Use the recently announced sqlite-winrt across on codeplex
I’m sure there might be other options but these are the ones I’ll be covering in these posts. This post we’ll actually take a look at the third option, sqlite-winrt, which has recently been announced on the Windows Phone developer blog.
Sqlite-Winrt: SQLite WinRT wrapper for WinRT (ie Window8 and Windows Phone)
Here are the steps I went through in my quick exploration:
– Go to the sqlite-winrt project on codeplex: http://sqlwinrt.codeplex.com
– Switch to the source code tab (there’s no actual releases yet) and click the download button
– When prompted save the zip file, then open windows explorer and “unblock” the zip file (right-click on file, select properties, click unblock button)
– Unzip the downloaded file and attempt to open the solution by double-clicking the .sln file (currently SqliteWinRTComponents.sln).
*** At this point I get a “failed to open project” notice from Visual Studio because I haven’t installed both the SQLite for Windows and SQLite for Windows Phone extensions. If you’re only interested in supporting one of the platforms then you just need to install the extensions for that platform, and simply ignore the error since it is just pointing out it can’t load one of the projects.
– Restart and Reload solution if you had to install one or more of the extensions
– At this point you should double-check you can build both projects. You can’t run anything because you only have two libraries (one for WinRT and one for Windows Phone)
– Add a Windows Phone application
– Add a reference to the SQLiteWinRTPhone library to your Windows Phone application.
– Update the MainPage.xaml.cs file to include the following code:
using System;
using System.Threading.Tasks;
using System.Windows;
using System.Windows.Navigation;
using SQLiteWinRTPhone;
using Windows.Storage;
namespace SqliteWPTest
{
public partial class MainPage
{
public MainPage()
{
InitializeComponent();
}
protected override async void OnNavigatedTo(NavigationEventArgs e)
{
base.OnNavigatedTo(e);
await CreateCitiesDatabase();
}
private async Task CreateCitiesDatabase()
{
// Get the file from the install location
var file =
await
ApplicationData.Current.LocalFolder.CreateFileAsync
("cities.db", CreationCollisionOption.OpenIfExists);
// Create a new SQLite instance for the file
using (var db = new Database(file))
{
// Open the database asynchronously
await db.OpenAsync(SqliteOpenMode.OpenReadWrite);
await db.ExecuteStatementAsync
("CREATE TABLE Cities (rowid INTEGER PRIMARY KEY ASC, CityName TEXT);");
await db.ExecuteStatementAsync
("INSERT INTO Cities (rowid, CityName) VALUES (1, 'Perth');");
// Prepare a SQL statement to be executed
var statement = await db.PrepareStatementAsync
("SELECT rowid, CityName FROM Cities;");
// Loop through all the results and display them
while (await statement.StepAsync())
MessageBox.Show(statement.GetIntAt(0) + ": " + statement.GetTextAt(1));
}
}
}
}
– Ok, you’re good to run (at least on the emulator) This will work the first time through but fail the next because it attempts to create the Cities table multiple time. I’ll leave it to you to refine the code.
– You might also want to run on a device. When I switched targets to Device I got the following error when attempting to debug:
– To fix this, right-click on your solution in Solution Explorer and select Configuration Manager. Change the Active solution platform to ARM.
– Rinse and repeat for WinRT. Make sure you reference the correct SQLite wrapper (Resharper caught me out here and automatically added a reference to the phone wrapper). Also, you’ll need to change MessageBox to use the MessageDialog class.
The question is now how did we go on our goals:
– Blendable: Tick! We haven’t done anything to break this requirement
– Sqlite: Tick! We’ll we’re using SQLite, right! You can validate this by copying the db file out of isolated storage onto your host computer and using a tool like sqlitebrowser to open it
– PCL: Fail – not a PCL in sight. The code is tightly coupled with the Sqlite wrappers. Looks like we’re going to have to add another indirection layer here to get this to work….
– Objects Not SQL: Fail. Hmmm, I’m not going any further here. I can see this as being useful for those wishing to write raw sql but for me, I’m going to invest time elsewhere.
That’s enough sqlite for one day. I’ll be back with part 2 where we’ll look at sqlite-net and how you can use this to give you objects instead of sql. Part 3 will look at how we can fix the lack of PCL support (for both sqlite-winrt and sqlite-net).