NHibernate with C#-Sqlite has no DbConnection in > .NET 2.0

Question

I tried to run my unit tests with C# Sqlite instead of going to a real database or mocking the hell out of our database layer using this blog post as guideline: Nhibernate C# Sqlite.

Unfortunately NHibernate requires a connection to be an DbConnection which is not provided by the C#-Sqlite Driver. I looked into the source code and noticed the following code:

#if NET_2_0
public class SqliteConnection : DbConnection, ICloneable
#else
    public class SqliteConnection : IDbConnection, ICloneable
#endif

Aparently the author decided to discontinue the "old" base classes and use the IDbConnection, IDbTransaction, ... interfaces instead.

Changing the code is no option because this would be equal to write a new client which has to be tested and maintained.

Does anyone know an easy solution to get the current version of c#sqlite work with NHibernate?

Answer 1

I'm testing using sqlite. I have a different approach.

I let NH create the connection, but store it in a static variable to pass it to subsequent sessions.

When I begun to write the test framework, I had initialization code like this:

static IDbConnection staticSqliteConnection;

if (staticSqlliteConnection == null)
{
  sessionFactory.CreateSession().Connection;
}

Then I had some issues and had too much test specific code in my production code (I prefer to have none). Hi-Lo id generators do also not work if you pass the connection to the session.

Then I wrote a ConnectionProvider class looking like this:

public class SingleConnectionProvider : DriverConnectionProvider
{
    private static IDbConnection staticConnection = null;

    public static void ResetConnection()
    {
        if (staticConnection == null) return;
        staticConnection.Dispose();
        staticConnection = null;
    }

    public override IDbConnection GetConnection()
    {
        if (staticConnection == null) 
        {
            staticConnection = base.GetConnection();
        }
        if (staticConnection.State != ConnectionState.Open)
        {
            staticConnection.Open();
        }
        return staticConnection;
    }

    protected override void Dispose(bool isDisposing)
    {
        ResetConnection();
    }
}

In the unit test cleanup method, you call SingleConnectionProvider.ResetConnection() which destroys the database for the next test.

The whole thing is of course not fully thread safe. You can create as many sessions as you want, but you should avoid to create them concurrently.

Answer 2

If you look at your code sample,

#if NET_2_0
public class SqliteConnection : DbConnection, ICloneable
#else
    public class SqliteConnection : IDbConnection, ICloneable
#endif

You can see that by defining the compiler option NET_2_0, and recompiling, it will expose the older interfaces

Answer 3

I solved the problem using System.Data.SQLite and a custom Connection Provider:

public class SqLiteInMemoryConnectionProvider : DriverConnectionProvider
{
    public static IDbConnection Connection;

    public static event EventHandler ConnectionReset;

    public static void ResetConnection()
    {
        if(Connection == null) return;

        Connection.Close();
        Connection.Open();

        if(ConnectionReset != null) ConnectionReset(null, new EventArgs());
    }

    public override IDbConnection GetConnection()
    {
        if (Connection == null)
        {
            Connection = base.GetConnection();
        }
        if (Connection.State != ConnectionState.Open)
        {
            Connection.Open();
        }
        return Connection;
    }

    public override void CloseConnection(IDbConnection conn) { }
}

Differences to Stefan Steineggers solution are I am closing and reopening the session to reset because NHibernate could not aquire a new connection if I just disposed it. Further I added an event to use in my factory class to rebuild the session factory (which exports our schema to the database)

SqLiteInMemoryConnectionProvider.ConnectionReset += (s, _) => RebuildSessionFactory();