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.
[Update 2021-10-20: The page view statistics show that this post has been quite popular. However, these days it’s better to ditch Mono and use .NET 5 or later, together with one of the SQLite NuGet packages. For example sqlite-net-pcl, which I have used on Raspberry Pi OS.]
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!
Hi,
I followed your steps, but i´m still getting the same error message. I copied the .so to the same folder of System.Data.SQLite.dll. The application still looking for a SQLite.Interop.dll file.
What Linux are you using, and what Mono version (“mono –version”) do you run?
Raspbian Jessie with Mono JIT compiler version 4.6.1 (Stable 4.6.1.5/ef43c15)
I just tried it on Raspbian, and it works for me. You can download the working binaries (including the .so file) of a sample at https://minfil.org/WoMQqnqFc71sQr6cDmYjAm095BEAaulw/I7L0p3b8be/sqlite_qnd.zip and run it on the Pi. The source code of the sample is here: https://gist.github.com/wezeku/9120e80bd6cb181f195c09c761d20647
Yes, it´s working with your sample! I found another way compiling the System.Data.SQLite.dll with this command (using your steps above)
xbuild /p:Configuration=Release /p:UseInteropDll=false /p:UseSqliteStandard=true ./System.Data.SQLite/System.Data.SQLite.2010.csproj
Thanks for your help!
Actually, that’s not just another way of compiling. That’s building a different version, lacking features that the Interop version has. See Joe Mistachkin’s comment: http://sqlite.1065341.n5.nabble.com/System-Data-SQLite-Using-sqlite3-dll-instead-of-Interop-dll-what-s-the-trade-off-td91484.html
Hi,
just found your blog and tried it. Worked perfectly on a Linux machine running a Windows DevStudio developed sqlite client without modification in a mono environment.
Thanks a lot for the excellent work.
Chris
Great work!
If you get an error about type not found, make sure you are building your mono/.net executable in Release mode. These instructions build the library for release mode.
If you want a Debug library/.so, use the compile-interop-assembly-debug.sh script instead.
works like a charm here !
Fantastic! No more #DEFINE MONO #IF MONO stuff – plus the SQLiteDataReader and the SqliteDataReader (from the MONO stuff) do not work exactly the same requiring more code workarounds. Thanks for this, it has saved my bacon!
Brilliant! Thanks for the Tip.