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

Try adding a name for the datasource instead of :memory:

data source=testName;foreign keys=True;default timeout=3;datetimekind=Utc;pooling=False;mode=memory;cache=shared

Answer 2

I was having same issue with sharing in memory sqlite database. My solution was to set connection string to "FullUri=file::memory:?cache=shared"

Answer 3

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;
    }
}

Answer 4

I guess you stopped reading to early. The second paragraph of the link you posted, "In-memory Databases And Shared Cache" describes what you can do and need to do in your case:

The database is deleted after closing the last connection if you use the method described in the link you posted.

You need a file URI and mode=memory for in-memory and cache=shared to have multiple connections go to the same named database.

So if your unit tests starts out with a using statement for a connection that is named (for example with the same name as your current test) then inside that using statement, all connections using the same connection string should connect to the same in-memory database.