Wednesday, June 29, 2016

Using SQLite server with C# / Mono on Linux

If you've arrived on this post, it means that you're using Mono on Linux and attempting to use SQLite, and having a bit of difficulty. That's understandable. This is not a tutorial on SQL / Mono / Entity or anything of the sort. This will get a barebones implementation of a server working with mono.

The Problem
The problem is that Mono comes with Mono.Data.Sqlite. Yet, if you're looking for information on using SQLite in C#, you will be directed to use System.Data.Sqlite, which is included in the nuget library. You can add this using Xamarin's tools successfully. Intellisense should work, and most functions in System.Data.Sqlite should work as well... except for one: SQLiteConnection, which is used to connect to the database.

I'm not 100% positive on the nature of the problem, as it stands, in mono right now. I can narrow it down to (at least in my case) some kerfuffle regarding sqlite.interop.dll not playing nicely with my architecture of choice (x86_64).

The Fix
The best fix for this is to... use Mono.Data.Sqlite. However, this has some downsides.
  • Using a Mono library breaks compatibility with .NET... ouch.
  • Mono.Data.Sqlite, for whatever reason, is not a drop-in replacement of System.Data.Sqlite.
with these things in mind, let's do it.

using System;
using Mono.Data.Sqlite;
namespace SQLITETEST {
  class MainClass {
    SqliteConnection db = new SqliteConnection("Data Source={filename.sqlite};Version=3;");
    db.Open();
    SqliteDataReader reader = new SqliteCommand("SELECT x FROM y WHERE z = zz;", db).ExecuteReader();
    while (reader.Read()) { Console.WriteLine(reader.GetString(0));}

and there you go. This code will connect to a database {filename}, run a query, and return the results. If you have experience with System.Data.Sqlite, you should notice that the SYNTAX of Mono.Data.Sqlite doesn't differ from it too much... but the spelling does. System.Data.Sqlite prefers to use classes spelled as "SQLiteDataReader, SQLiteConnection", etc. while Mono uses "SqliteDataReader"... 

While this seemingly unnecessary difference is annoying, it's negligible. Enjoy kludging through Sqlite and Mono further!

2 comments: