SQLite :memory: database usage with using / dispose

Question

I have wrapped all of my SQL Connections in a using statement.

using (DbConnection dbConnection = GetConnection())
{
    using (DbCommand dbCommand = dbConnection.CreateCommand(cmdInsert))
    {
        //some work
    }
}

For UnitTests I supposed to use a :memory: database, but the database is automatically deleted, after closing the connection.

https://www.sqlite.org/inmemorydb.html

The database is automatically deleted and memory is reclaimed when the last connection to the database closes.

Is there a solution how to use a :memory: database and use using? I don't want to write exactly the same code twice without using..

Complete Example

Database

public abstract class SqliteBase
{
    public string ConnectionString;

    protected SqliteBase()
    {      
        SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder
        {
            DataSource = ":memory:",
            ForeignKeys = true,
            DefaultTimeout = 3,
            DateTimeKind = DateTimeKind.Utc,
            Pooling = false             
        };

        ConnectionString = builder.ConnectionString + ";mode=memory;cache=shared";
    }

    private DbConnection _MemoryConnection;
    protected DbConnection GetConnection()
    {
        try
        {
            if (_MemoryConnection == null)
            {
                _MemoryConnection = new SQLiteConnection(ConnectionString);                    
                _MemoryConnection.Open();
            }

            DbConnection dbConnection = new SQLiteConnection(ConnectionString);
            dbConnection.Open();

            return dbConnection;
        }

        catch (Exception ex)
        {
            throw new Exception("Error opening database connection.", ex);
        }
    }

    /// <summary>
    /// Creates a table in the SQL database if it does not exist
    /// </summary>
    /// <param name="tableName">The name of the table</param>
    /// <param name="columns">Comma separated column names</param>
    protected void CreateTable(string tableName, string columns)
    {
        using (DbConnection dbConnection = GetConnection())
        {
            using (DbCommand dbCommand = dbConnection.CreateCommand($"create table if not exists {tableName} ({columns})"))
            {
                dbCommand.ExecuteNonQuery();
            }
        }
    }
}

public class FooDatabase : SqliteBase
{
    public FooDatabase()
    {
        CreateTable("FooTable", "Foo TEXT");
    }

    public void DoFoo()
    {
        using (DbConnection dbConnection = GetConnection())
        {
            using (DbCommand dbCommand = dbConnection.CreateCommand("Select * from FooTable"))
            {
                dbCommand.ExecuteNonQuery();
            }
        }
    }
}

Unit Test

public static class SQLiteTestSetup
{
    public static FooDatabase ClassInit()
    {
       return new FooDatabase();
    }

    public static void Cleanup()
    {

    }
}

public abstract class SQLiteTestBase
{
    public static FooDatabase Database { get; set; }

    [TestMethod]
    public void DoSomeFooTest()
    {
        Database.DoFoo();
    }
}

[TestClass]
public class SQLiteTest : SQLiteTestBase
{
    [ClassInitialize]
    public static void ClassInit(TestContext context)
    {
        Database = SQLiteTestSetup.ClassInit();
    }

    [ClassCleanup]
    public static void ClassCleanup() => SQLiteTestSetup.Cleanup();
}

Exception

Die Testmethode "....SQLiteTest.DoSomeFooTest" hat eine Ausnahme ausgelöst: 
System.Data.SQLite.SQLiteException: SQL logic error
no such table: FooTable
    bei System.Data.SQLite.SQLite3.Prepare(SQLiteConnection cnn, String strSql, SQLiteStatement previous, UInt32 timeoutMS, String& strRemain)
   bei System.Data.SQLite.SQLiteCommand.BuildNextCommand()
   bei System.Data.SQLite.SQLiteCommand.GetStatement(Int32 index)
   bei System.Data.SQLite.SQLiteDataReader.NextResult()
   bei System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   bei System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   bei System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
   bei System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()
   bei ....FooDatabase.DoFoo() in ...\SqliteDatabaseBase.cs:Zeile 83.
   bei ....SQLiteTestBase.DoSomeFooTest() in ...\SQLiteTest.cs:Zeile 30.

Answer 1

Working Solution approach

I have added a ConnectionContext class, where I can set a flag to decide if I want to dispose my DbConnection or not.

Database class

public class ConnectionContext : IDisposable
{
    private readonly bool _ContextOwnsConnection;
    public readonly DbConnection Connection;

    public ConnectionContext(DbConnection connection, bool contextOwnsConnection)
    {
        Connection = connection;
        _ContextOwnsConnection = contextOwnsConnection;            
    }

    public void Dispose()
    {
        if(_ContextOwnsConnection)
            Connection.Dispose();
    }
}

public abstract class SqliteBase
{
    public Func<ConnectionContext> GetContext;

    private ConnectionContext _GetConnectionContext()
    {
        return new ConnectionContext(GetConnection(), true);
    }
    private string _ConnectionString;
    private readonly string _Dbfile;

    protected SqliteBase()
    {
        GetContext = _GetConnectionContext;
        _Dbfile = ":memory:";

        _InitConnectionString();
    }

    private void _InitConnectionString()
    {
        SQLiteConnectionStringBuilder builder = new SQLiteConnectionStringBuilder
        {
            DataSource = _Dbfile,
            ForeignKeys = true,
            DefaultTimeout = 3,
            DateTimeKind = DateTimeKind.Utc,
            Pooling = true
        };

        _ConnectionString = builder.ConnectionString;
    }

    public DbConnection GetConnection()
    {
        try
        {
            DbConnection dbConnection = SQLiteFactory.Instance.CreateConnection();                
            dbConnection.ConnectionString = _ConnectionString;
            dbConnection.Open();

            return dbConnection;
        }

        catch (Exception ex)
        {
            throw new Exception("Error opening database connection.", ex);
        }
    }

    /// <summary>
    /// Creates a table in the SQL database if it does not exist
    /// </summary>
    /// <param name="tableName">The name of the table</param>
    /// <param name="columns">Comma separated column names</param>
    protected void CreateTable(string tableName, string columns)
    {
        using (ConnectionContext context = GetContext())
        {
            using (DbCommand dbCommand = context.Connection.CreateCommand($"create table if not exists {tableName} ({columns})"))
            {
                dbCommand.ExecuteNonQuery();
            }   
        }                   
    }
}

public class FooDatabase : SqliteBase
{
    public FooDatabase()
    {
        Initialize();
    }

    public void Initialize()
    {
        CreateTable("FooTable", "Foo TEXT");
    }

    public void DoFoo()
    {
        using (ConnectionContext context = GetContext())
        {
            using (DbCommand dbCommand = context.Connection.CreateCommand("Select * from FooTable"))
            {
                dbCommand.ExecuteNonQuery();
            }   
        }                        
    }
}

Unit Test

public abstract class SQLiteTestBase
{
    public static ConnectionContext Connection { get; set; }
    public static FooDatabase Database { get; set; }

    [TestMethod]
    public void DoSomeFooTest()
    {
        Database.DoFoo();
    }
}

[TestClass]
public class SQLiteTest : SQLiteTestBase
{
    [ClassInitialize]
    public static void ClassInit(TestContext context)
    {                 
        Database = new FooDatabase();            
        Database.GetContext = () => Connection;
        Connection = new ConnectionContext(Database.GetConnection(), false);            
    }

    [TestInitialize]
    public void TestInit()
    {
        Connection = new ConnectionContext(Database.GetConnection(), false);
        Database.Initialize();
    }

    [TestCleanup]
    public void TestCleanup()
    {
        Connection.Dispose();
        Connection = null;
    }
}