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!

    • Jose
    • November 3rd, 2016 6:08am

    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.

      • John
      • November 3rd, 2016 3:42pm

      What Linux are you using, and what Mono version (“mono –version”) do you run?

    • José
    • November 3rd, 2016 5:09pm

    John :
    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)

    • John
    • November 4th, 2016 12:38pm

    Jose :

    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

    • Chris
    • November 12th, 2016 2:16am

    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

  1. 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.

    • Patrick
    • April 17th, 2017 5:59pm

    works like a charm here !

    • Tim
    • April 19th, 2017 4:25pm

    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!

    • Mac
    • April 22nd, 2017 3:59am

    Brilliant! Thanks for the Tip.

    • Pete Gordon
    • June 20th, 2017 12:15am

    You rock! Thank you so very much! Your instructions were perfect!

  1. No trackbacks yet.