Using System.Data.SQLite under Linux and Mono
If you want to use the lean, mean and fast SQLite database in your .NET programs under Linux and Mono, using Mono.Data.SQLite might seem like the obvious choice, but you can actually do better. With a little effort, you can use System.Data.SQLite under Linux+Mono (and probably macOS too). There a couple of key benefits to be gained from this:
- If you build SQLite.Interop on the Mono machine, you get functionality not present in Mono.Data.SQLite, like SQLite virtual table support (see this answer from Joe Mistachkin).
- The System.Data.SQLite NuGet package is actively maintained, whereas Mono.Data.SQLite is rarely updated. As of October 2016, the former is about a month old, but the latter is older than 1.5 years. This means that bug fixes (e.g. DateTime conversion throwing exceptions when columns have time zone info) remain unreleased for Mono.Data.SQLite.
- In Mono.Data.SQLite, classes don’t have the same capitalization as System.Data.SQLite, so you can’t use the same source code as you do in .NET and just leave both the Mono.Data.SQLite and the System.Data.SQLite DLLs in your program directory. You’d have to resort either to #defines and conditional compilation or to using Mono.Data.SQLite under both .NET and Mono.
Building System.Data.SQLite Interop under Linux
There’s no System.Data.SQLite package for Linux, so you’ll have to build it yourself on your target Linux machine. You can build using this procedure, which is tested in Raspbian Jessie on a Raspberry Pi 3 and Ubuntu 16.04.1 on a PC:
- Download System.Data.SQLite full source code from this download page. There’s a ton of files there, and the one you should look for is named something like sqlite-netFx-full-source-<version no>.zip.
- Unzip it and transfer it to a directory on your Linux machine. In the rest of this description, I’ll call this directory “<source root>”.
- Issue these commands in a Linux terminal:
sudo apt-get update sudo apt-get install build-essential cd <source root>/Setup chmod +x compile-interop-assembly-release.sh ./compile-interop-assembly-release.sh
- Now, you will have a freshly built library file called libSQLite.Interop.so in the <source root>/bin/2013/Release/bin directory. This file might have execution permission which isn’t relevant for a library, so remove it by
chmod -x <source root>/bin/2013/Release/bin/libSQLite.Interop.so
- Copy libSQLite.Interop.so the directory where your Mono/.NET application’s binaries reside (not the x64 or x86 subdirectories containing SQLite.Interop.dll), and you’re set to go.
And that, ladies and gentlemen, is how we do that!