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.

Your example shows a wrapped connection, not a wrapped DBCommand object. So it should be possible to use one DbConnection with many DbCommand objects. — user743414
– user743414, Commented Feb 22, 2018 at 10:51
Sorry my mistake! I have updated the code sample how I use it. — Dominic Boexler
– Dominic Boexler, Commented Feb 22, 2018 at 10:53

Dominic Boexler · Accepted Answer · 2018-02-22 14:42:02Z

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

nvoigt · Accepted Answer · 2018-02-22 11:06:11Z

0

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.

answered Feb 22, 2018 at 11:06

nvoigt

77.8k28 gold badges103 silver badges156 bronze badges

6 Comments

Dominic Boexler Over a year ago

Does not solve the problem. This is my ConnectionString data source=:memory:;foreign keys=True;default timeout=3;datetimekind=Utc;pooling=False;mode=memory;cache=shared.

nvoigt Over a year ago

So did you implement my suggestion that each unit tests holds an open connection so that the test determines what the "last connection" is? If so, please post it.

Dominic Boexler Over a year ago

Have added a complete (trimmed) example where I get the exception

nvoigt Over a year ago

@DominicJonas Your connection string does not look like described in the link you posted. You need a file URI, like "file::memory:..."

Dominic Boexler Over a year ago

If I use ConnectionString = "file::memory:?cache=shared"; or ConnectionString = "file:memdb1?mode=memory&cache=shared"; I get the following error Error opening database connection. ---> System.ArgumentException: Data Source cannot be empty. Use :memory: to open an in-memory database..

|

Vlad Setchin · Accepted Answer · 2018-11-30 02:54:46Z

0

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

answered Nov 30, 2018 at 2:54

Vlad Setchin

1311 silver badge5 bronze badges

Comments

emkill2005 · Accepted Answer · 2019-11-13 09:38:16Z

0

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

answered Nov 13, 2019 at 9:38

emkill2005

132 bronze badges

Collectives™ on Stack Overflow

SQLite :memory: database usage with using / dispose

Complete Example

Database

Unit Test

Exception

4 Answers 4

Working Solution approach

Database class

Unit Test

Comments

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Complete Example

Database

Unit Test

Exception

4 Answers 4

Working Solution approach

Database class

Unit Test

Comments

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related