Sqlite-Csharp and NHibernate

Published on 2010-6-7

Taking a brief  interlude from my RavenDB series, I was doing some work on an internal project tonight with the build scripts and test-runner and I finally got bored of having to deal with un-managed SQLite dependencies with a project which other than that was platform agnostic.

The problem with having un-managed dependencies in a managed project is that Visual Studio quite frankly sucks at it, you can set up certain projects (in this case the tests) to be x86 only, and remove their Any CPU configuration – but as soon as you add a new project to the solution it decides to re-add the old configuration and potentially break things again.

This doesn't really rear any problems until you write a build script and things start falling over as your test runner tries to run as an x64 process and tries to load in the x86 dependency, or any number of combinations where this kind of thing can blow up. If it can happen, it will happen and it’s just something I’d rather not deal with.

So I had a look at Sqlite-Csharp, the code is atrocious as far as natively written C# libraries go (that’s not the point though, it’s a *port*), but it looks to be a superb direct-port of a C project (Sqlite) and passes most of the tests that it needs to in order for it to be viable for use in at least our in-memory tests.

Anyway, you can’t download binaries, so you have to build it – but no changes are required so just do it.

I’m not going to cover the process of setting up in-memory databases for testing with SQLite as that’s an easily Google-able topic, but there are a few differences between doing it with the unmanaged libraries and with the pure managed libraries.

This is what my FluentNHibernate configuration looks like:

   1:  Fluently.Configure()
   2:              .Database(
   3:                   SQLiteConfiguration.Standard.ConnectionString(
   4:                        x => x.Is(mConnectionString)).Driver<SqliteDriver>());

I’ve had to create a driver to make this work properly as there isn’t one provided as stock in NHibernate, the code for this is as simple as this:

   1:  public class SqliteDriver : ReflectionBasedDriver
   2:      {
   3:          /// <summary>
   4:          /// Initializes a new instance of <see cref="SQLiteDriver"/>.
   5:          /// </summary>
   6:          /// <exception cref="HibernateException">
   7:          /// Thrown when the <c>Community.CsharpSqlite.SQLiteClient</c> assembly can not be loaded.
   8:          /// </exception>
   9:          public SqliteDriver()
  10:              : base(
  11:                  "Community.CsharpSqlite.SQLiteClient",
  12:                  "Community.CsharpSqlite.SQLiteClient.SqliteConnection",
  13:                  "Community.CsharpSqlite.SQLiteClient.SqliteCommand")
  14:          {
  15:          }
  17:          public override bool UseNamedPrefixInSql
  18:          {
  19:              get { return true; }
  20:          }
  22:          public override bool UseNamedPrefixInParameter
  23:          {
  24:              get { return true; }
  25:          }
  27:          public override string NamedPrefix
  28:          {
  29:              get { return "@"; }
  30:          }
  32:          public override bool SupportsMultipleOpenReaders
  33:          {
  34:              get { return false; }
  35:          }
  37:          public override bool SupportsMultipleQueries
  38:          {
  39:              get { return true; }
  40:          }
  41:      }

Yeah, not terribly exciting – just add a reference to Community.CsharpSqlite.SQLiteClient and this will work.

The other major difference is the delimiter between connection string components is a comma and the method of selecting an in-memory database looks different. This is my connection string:

   1:  "uri=file://:memory:,Version=3";

And this is the code I use to create the connection:

   1:  private SqliteConnection GetConnection()
   2:          {
   3:              if (mConnection == null) {
   4:                  mConnection = new SqliteConnection(mConnectionString);
   5:                  mConnection.Open();
   6:              }
   7:              return mConnection;
   8:          }

And this is therefore the code I use to create a session factory:

   1:  mFactory.OpenSession(GetConnection());

A word of warning

Mileage may vary, I had 11 tests from about 300 fail, mostly due to unrecognised types/null values and exceptions that were different in this version of Sqlite, I’m submitting some code fixes for the unrecognised types and null values and modifying my tests to take into account the new exception types.

Also, I can’t guarantee I’ve done it right, so let me know if I’ve done something stupid.

All of my tests are now Any CPU and my build process is suddenly a lot simpler, I’ll take the hit of having to submit and change a bit of code in order to get that.

This used to ask if you wanted to hire me

But chances are I'm not available, as I'm busy shipping stuff.

I am available for conference speaking, I like talking and will do so for just T&E (and perhaps a bottle of wine or two).

I have done soft keynotes, and usually entertain/rile people in equal proportion, but prefer to talk tech as that's what I do

Email me :)

blog comments powered by Disqus

Gabriel Schenker

your post comes in timely since we faced exactly the same problems with SQLite unmanaged. I'll give the managed version a try. Thanks for sharing


Thank you for this looks very helpful if only I can get it to work, I keep getting this error:NHibernate.HibernateException: The IDbCommand and IDbConnection implementation in the assembly Community.CsharpSqlite.SQLiteClient could not be found. Ensure that the assembly Community.CsharpSqlite.SQLiteClient is located in the application directoryAnd I do have that assembly in the test directory.Any ideeas ?


For anyone else who comes across this issue - the projects are set to target the x86 platform by default. If you are running on a 64-bit platform this can be an issue. Simple enough to change the build target to Any CPU.