Update: with Eric’s comment, we’ve worked out how to make SQLite work properly with Xamarin without playing versioning games. The instructions can be found on http://developer.xamarin.com/guides/cross-platform/xamarin-forms/windows/samples/#sqlite, with thanks to Craig Dunn. The short of it is that you want to add SQLite.net-PCL from NuGet, then separately add a reference to Microsoft Visual C++ 2013 Runtime.
We return you now to the original post…
The last few weeks I’ve been making significant revisions to a Xamarin project based on code review feedback. This project is part of a larger effort that we’ll be presenting in a couple MSDN Magazine articles starting in August.
One big chunk of work was cleaning up all my usage of async APIs, and properly structuring tasks+await to do background synchronization between the app’s backend and its local cache for offline use. The caching story will be a main section in Part 2 of the article, but the story of cleaning up the code is something I’ll write about here in a couple of posts.
The first bit of that story is my experience–or struggle–to find the right variant of SQLite to use in the app. As you might have experienced yourself, quite a few SQLite offerings show up when you do a search in Visual Studio’s NuGet package manager. In our project, I started with SQLite.Net-PCL, which looked pretty solid and is what one of the Xamarin samples itself used.
However, I ran into some difficulties (I don’t remember what, exactly) when I started trying to use the async SQLite APIs.
On Xamarin’s recommendation I switched to the most “official” variant, sqlite-net-pcl, which also pulls in SQLitePCL.raw_basic.0.7.1. Keep this version number in mind because it’s important here in a minute.
This combination worked just fine for Android and iOS projects, but generated a warning for Windows Phone: Some NuGet packages were installed using a target framework different from the current target framework and may need to be reinstalled. Packages affected: SQLitePCL.raw_basic.
This is because SQLitePCL.raw.basic is marked for Windows Phone 8.0 but not 8.1, which is what my Windows Phone project in the solution was targeting.
OK, fine, so I went to the NuGet package manager, saw an update for the 0.8 version of SQLitePCL.raw.basic, and installed that. No more warning but…damn…the app no longer ran on Windows Phone at all! Instead, the first attempt to access the database threw a System.TypeInitializationException, saying “The type initializer for ‘SQLitePCL.raw’ threw an exception.” The inner exception, System.IO.FileNotFoundException, had the message, “The specified module could not be found. (Exception from HRESULT: 0x8007007E).”
What’s confusing in this situation is that SQLitePCL.raw does not appear in the Windows Phone project’s references alongside sqlite-net, as it does in the Android and iOS projects. This is, from what I can see, because the Windows Phone version of sqlite-net does some auto-gen or the raw DLL or has pre-built versions in its own package, so a separate reference isn’t necessary. (If you know better, please comment.)
Still, those DLLs were right there in the package and I couldn’t for the life of me figure out why it couldn’t find them, so I resorted to the tried and true method of trying to repro the failure from scratch with a new project, where the default Windows Phone project targeted 8.0. I then added “SQLite-net PCL” to all the projects in the solution, which brought in the raw 0.7.1 dependency, tossed in a couple APIs calls to create and access the database, and gave it an F5. Cool, everything worked.
Next, I retargeted the Windows Phone project to 8.1 and F5’d again. Everything still worked, but I got the warning about SQLitePCL.raw.basic once again. Apparently it’s OK to ignore that one.
I then updated SQLitePCL.raw to the 0.8 version and boom–got the exception again, so clearly there’s an incompatibility or bug in the 0.8 version with Windows Phone 8.1.
Clearly, then, the solution is to altogether avoid using the 0.8 version with a WP8.1 target, and if you want to suppress the warning, open packages.config in the Windows Phone project and have the SQLitePCL.raw_basic line read as follows:
<package id=”SQLitePCL.raw_basic” version=”0.7.1″ targetFramework=”wp80″ requireReinstallation=”False” />