title: SQLite :memory: database usage with using / dispose
body: <p>I have wrapped all of my <code>SQL Connections</code> in a <code>using</code> statement.</p>
  
  <pre><code>using (DbConnection dbConnection = GetConnection())
  {
      using (DbCommand dbCommand = dbConnection.CreateCommand(cmdInsert))
      {
          //some work
      }
  }
  </code></pre>
  
  <p>For <code>UnitTests</code> I supposed to use a <code>:memory: database</code>, but the <code>database</code> is automatically deleted, after closing the connection.</p>
  
  <p><a href="https://www.sqlite.org/inmemorydb.html" rel="nofollow noreferrer">https://www.sqlite.org/inmemorydb.html</a></p>
  
  <blockquote>
    <p>The database is automatically deleted and memory is reclaimed when the last connection to the database closes. </p>
  </blockquote>
  
  <p>Is there a solution how to use a <code>:memory: database</code> and use <code>using</code>? I don't want to write exactly the same code twice without <code>using</code>..</p>
  
  <h1>Complete Example</h1>
  
  <h2>Database</h2>
  
  <pre><code>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);
          }
      }
  
      /// &lt;summary&gt;
      /// Creates a table in the SQL database if it does not exist
      /// &lt;/summary&gt;
      /// &lt;param name="tableName"&gt;The name of the table&lt;/param&gt;
      /// &lt;param name="columns"&gt;Comma separated column names&lt;/param&gt;
      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();
              }
          }
      }
  }
  </code></pre>
  
  <h2>Unit Test</h2>
  
  <pre><code>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() =&gt; SQLiteTestSetup.Cleanup();
  }
  </code></pre>
  
  <h2>Exception</h2>
  
  <pre><code>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&amp; 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.
  </code></pre>