One of the comments that was made on one of my previous posts about “Universal” projects in Visual Studio 2013 Update 2 and how you might deal with small differences in code across the Windows/Phone 8.1 platforms was;

“How do we handle SQLite libraries? I’m pretty sure that both WinPRT and WinRT SQLite implementations have the exact same API. Yet, different libraries have to be deployed to different platforms. How do we handle that?”

I’m interpreting the question as “How do I make use of SQLite from Windows Phone 8.0, 8.1 and Windows 8.1”. I think that adding Windows Phone 8.0 into that mix makes the question a bit more interesting and I think it’s also realistic – i.e. you may well want to try and target Phone 8.0 and Phone 8.1 right now.

It seems like a fairly complicated story based on all the (great) blog posts out there like these and I suspect that some of that comes from Windows Phone 8.1 being in preview;

Windows Phone Blog: How to use SQLite in Windows Phone 8.0 Apps

Universal App with SQLite – Part 1

Universal App with SQLite – Part 2

and also this post from my colleague Andy around his SQLite-WinRT API that he has on Codeplex;

SQLite-WinRT: Database Programming on Windows Phone and Windows 8

Being honest, I’d say that it feels like making use of a simple client-side database in a Windows/Phone 8.0 or 8.1 project is more complicated than a developer would perhaps expect it to be.

In terms of SQLite, I think this ultimately comes down to a bit of impedance between SQLite being shipped as a flat, C-like API (I think it’s written in C – haven’t checked the source) which shows up inside of a Windows/Phone project as a .DLL which makes perfect sense from the point of view of the SQLite guys building something that targets a variety of platforms but, for a Windows/Phone app developer, it perhaps isn’t the packaging they’d expect.

To put that into practice, if I grab the right Visual Studio extensions for SQLite from;

1. The Visual Studio Gallery for Windows 8.1
2. The Visual Studio Gallery for Windows Phone 8.1 (NB: the “Part 1” blog post above was written before 12th May when these bits were posted).

and then I visit a Windows/Phone 8.1 project and add a reference to SQLite;

I might expect that I can then start to write code against SQLite but I’ve not really got myself into that position by adding those references.

What that “reference” is doing is making sure that I end up with sqlite3.dll inside of my platform-specific application packages. The picture below is of the DLL sitting inside of a “hello world” app-package that I made from a blank project once I’d added the reference above to SQLite and used the Store menu in Visual Studio to make the application package;

and if I go and poke into that DLL then just by dumping out its exports I can see that it’s a regular DLL (i.e. it’s not a .NET assembly) with flat, C-like exported APIs and I guess if I went and grabbed the source for SQLite I’d be able to see exactly how it was built;

and these sqlite3.dlls looked to be dropped into folders that are your equivalent of;

c:\program files (x86)\Microsoft SDKs\Windows Phone\v8.0\ExtensionSDKs\SQLite.WP80\

c:\program files (x86)\Microsoft SDKs\Windows\v8.0\ExtensionSDKs\SQLite.WinRT

c:\program files (x86)\Microsoft SDKs\WindowsPhoneApp\v8.1\ExtensionSDKs\SQLite.WP81

c:\program files (x86)\Microsoft SDKs\Windows\v8.1\ExtensionSDKs\SQLite.WinRT81

I haven’t tried to check to see whether these DLLs export all the same functions and/or whether the implementations here are identical but I suspect the former is true and that the latter is not. I’m no expert on it though.

Clearly, to make use of sqlite3.dll from managed code there’s going to be a need for something more than just this DLL – there’s a need for either;

1. A .NET wrapper that uses PInvoke to invoke the functions that are exported out of the DLL and marshal types back/forth. There’s the question of how that works for Windows Phone 8.0.
2. A WinRT wrapper written in C/C++ or C++/CX that directly links against the SQLite library in order to be able to call the functions exported from the DLL and then to offer some WinRT types that a developer can code against from Windows/Phone app code written in .NET (or JavaScript or more C++).

For me, where this then gets a little “confusing” is that I don’t think there’s an “official” wrapper that just does this for you but, instead, there are a number of wrappers that take one of the two approaches or some combination of both.

The route I’ll go down here is the WinRT wrapping route and in that area there’s the sqlite-winrt wrapper written by my colleague, Andy, along with a couple of other guys and that work is up on CodePlex, is written in C++/CX and so calls into the SQLite DLL directly.

That wrapper is a fairly direct “SQL” kind of wrapper rather than an ORM-style wrapper and the downside at the time of writing is that it’s there for Windows 8.0, Windows 8.1 and Windows Phone 8.0 but it’s not been updated yet for Windows Phone 8.1.

So…if I want to build some code that’s common across Windows 8.1, Windows Phone 8.1 and Windows Phone 8.0 I could take Andy’s source and open up the Visual Studio 2013 project that’s part of it;

and see if I can update it to target Windows Phone 8.1.

I hit a few snags in opening up the projects which were all to do with me having a later version of SQLite than the project files expected. This is mentioned on the Codeplex page for the project and the supplied scripts helped in some places but in other places I found myself doing a little bit of search/replace to get the bits to line up but it only took a few minutes.

With that in place, though, I have 3 projects that build out a WinRT component for Windows 8.0, Windows 8.1, Windows Phone 8.0.

I’ve a sneaking suspicion that it might be possible to take this set of projects and add in a Windows Phone 8.1 project based on the same code that’s already present in the Windows 8.1 project. That would be the “Universal dream” and so I thought I’d give that a quick try. Here’s the menu option;

and that adds both a Windows Phone 8.1 project and a Shared project and I figured I could nicely “lift” the source files from the Windows 8.1 project into the shared folder;

and that built ok in the context of Windows 8.1 and also in the context of Windows Phone 8.1 once I’d made sure that I’d added the right include/lib folders for the SQLite extension SDK (via the “Add Reference” metaphor);

and that then builds for Windows 8.0, 8.1 and Windows Phone 8.0, 8.1.

Having got a consistent WinRT interface here, is it possible to make use of it from a single, consistent piece of .NET code?

Referencing the WinRT Component

At this point, I’ve got a few projects producing a few binaries. Specifically;

• A Universal project producing binaries for Windows 8.1 and Phone 8.1 across ARM, x86, x64.
• A Windows 8 project producing binaries for Windows 8.0 across ARM, x86, x64.
• A Windows Phone 8 project producing binaries for Windows Phone 8.0 across ARM, x86, x64.

What I really want is the ability to make a .NET class library project in Visual Studio that can target N platforms such that there are N “head” projects and a shared folder. I could then drop my code into the shared folder and set up each “head” project to reference the right WinRT component binary. I don’t think Visual Studio quite has that though but I can produce something a little along those lines by having one “Universal” project and then having a separate project link to files from it. It feels a bit hacky but it looks like this;

What I’ve done there is to write some code that uses SQLite once in one file – the SharedClass.cs file in the UniversalApp.Shared folder. That then builds into the UniversalApp.Windows and UniversalApp.WindowsPhone projects for 8.1. What I’ve also done is link the same file into the PhoneApp project where it then gets built for Windows Phone 8.0.

I’ve then added references such that;

• UniversalApp.Windows references SQLLiteWinRT.Windows
• UniversalApp.WindowsPhone references SQLiteWinRT.WindowsPhone
• PhoneApp references SQLiteWinRTPhone

and because the WinRT component has the same public “interface” in all of those places, I should be able to write some piece of code like this one in my SharedClass.cs file;

namespace UniversalApp
{
  using SQLiteWinRT;
  using System;
  using System.Diagnostics;
  using Windows.Storage;

  public class SharedClass
  {
    public static async void DoSomething()
    {
      Database db = new Database(
        ApplicationData.Current.LocalFolder, "mytest.db");

      await db.OpenAsync(SQLiteWinRT.SqliteOpenMode.OpenOrCreateReadWrite);

      await db.ExecuteStatementAsync(
        "create table if not exists foo(id integer primary key , name varchar(20))");

      await db.ExecuteStatementAsync("delete from foo");

      for (int i = 0; i < 10; i++)
      {
        await db.ExecuteStatementAsync(
          string.Format("insert into foo(id, name) values ({0}, 'Record {0}')", i.ToString()));
      }
      var statement = await db.PrepareStatementAsync("select * from foo");

      while (await statement.StepAsync())
      {
        Debug.WriteLine("Values {0},{1}", statement.GetTextAt(0),
          statement.GetTextAt(1));
      }
    }
  }
}

and that gives me one way that I can share this piece of code across those 3 environments without duplicating the code itself.