System.Data.SQLite : VACUUM INTO an in-memory database

Question

First I use System.Data.SQLite v1.0.111 to open a connection to in-memory database as below.

SQLiteConnection dest= new SQLiteConnection("FullUri='file:db1?mode=memory&cache=shared'");
dest.Open();

Then I have another sqlite database on file which is opened as below.

SQLiteConnection src= new SQLiteConnection(@"Data Source=C:\db1.sqlite");
src.Open();

Next, I tried to VACUUM INTO SQL to copy the file database into in-memory database, and it gave me an error.

using( SQLiteCommand cmd = src.CreateCommand() )
{
    cmd.CommandText = $"VACUUM main INTO 'file:db1?mode=memory&cache=shared';";
    cmd.ExecuteNonQuery();
}

SQLiteException: out of memory

   at System.Data.SQLite.SQLite3.Reset(SQLiteStatement stmt)
   at System.Data.SQLite.SQLite3.Step(SQLiteStatement stmt)
   at System.Data.SQLite.SQLiteDataReader.NextResult()
   at System.Data.SQLite.SQLiteDataReader..ctor(SQLiteCommand cmd, CommandBehavior behave)
   at System.Data.SQLite.SQLiteCommand.ExecuteReader(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery(CommandBehavior behavior)
   at System.Data.SQLite.SQLiteCommand.ExecuteNonQuery()

The database in file is small(20KB), but it says out of memory?

Thank you

Answer 1

Well, first of all thanks for your Vacuum into syntax example, i wasn't able to find one! here's the solution:

SQLiteConnection dest = new SQLiteConnection("FullUri='file:db1?mode=memory&cache=shared'");
dest.Open();

SQLiteConnection src = new SQLiteConnection(@"Data Source=MYDb.sqlite");
src.Open();

using (SQLiteCommand cmd = src.CreateCommand())
{
    cmd.CommandText = @"VACUUM INTO 'file:db1?mode=memory&cache=shared';";
    cmd.ExecuteNonQuery();
}

using (SQLiteCommand cmd = dest.CreateCommand())
{
    cmd.CommandText = "SELECT count(*) FROM sqlite_master WHERE type = 'table'";
    var ret = cmd.ExecuteScalar();
}

in my case ret was equal to 9 (number of tables in 'MYDb.sqlite').

I think the problem was in the vacuum into syntax: you should not specify the source db (VACUUM INTO not VACUUM main INTO).

Answer 2

If you use "Microsoft.Data.Sqlite" (lightweight ADO.NET provider for SQLite) here is an example of C# code that uses VACUUM:

using (SqliteConnection connection = new SqliteConnection("Data Source=hello.db"))
   connection.Open();
   SqliteCommand sqliteCommand = new SqliteCommand("VACUUM", connection);
   sqliteCommand.ExecuteNonQuery();
}