Add SQLite database directly to Zip file

Question

I am trying to create an SQLite database in memory and then add it directly to a zip file I have created. So far I can create the zip file using the ZipArchive and ZipFile classes. However, I cannot find a way to add an SQLite database I have created in memory to the zip container.

Following is the code I have to create the database in memory:

private static void MemoryDB()
        {
            SQLiteConnection conn = new SQLiteConnection("Data Source = :memory:");
            conn.Open();

            String sql = "CREATE TABLE highscores (name VARCHAR(25), score INT)";
            SQLiteCommand command = new SQLiteCommand(sql, conn);
            command.ExecuteNonQuery();

            // Insert Data
            sql = "INSERT INTO highscores (name, score) VALUES ('Jennie', 98)";
            command = new SQLiteCommand(sql, conn);
            command.ExecuteNonQuery();

            sql = "INSERT INTO highscores (name, score) VALUES ('Michael', 42)";
            command = new SQLiteCommand(sql, conn);
            command.ExecuteNonQuery();

            sql = "INSERT INTO highscores (name, score) VALUES ('Jason', 76)";
            command = new SQLiteCommand(sql, conn);
            command.ExecuteNonQuery();

            // Select Data
            sql = "SELECT * FROM highscores ORDER BY score desc";
            command = new SQLiteCommand(sql, conn);
            SQLiteDataReader reader = command.ExecuteReader();
            while (reader.Read())
                Console.WriteLine("Name: " + reader["name"] + "\tScore: " + reader["score"]);

            conn.Close();
        }

I have been able to create a normal database on disk and add that to the zip container with the ZipArchive.CreateEntryFromFile() method and then deleting the database, but it looks very bad when you can see the databases being created and then removed.

Is there a better way?

Answer 1

As far as I know you can only save the in-memory database to disk (and load it back into memory) using the SQLite Backup API.

In c#, a SQLiteConnection has the BackupDatabase method available to do that. This will still require a temporary database file (the backup) to be created on disk. You could do this like so (assuming your in-memory connection is named source):

var myBackupPath = @"c:\backups\backup.db";
using (var destination = new SQLiteConnection("Data Source=" + myBackupPath))
{
    // saves from in-memory to the on-disk backup.
    source.BackupDatabase(destination, "main", "main", -1, null, -1);
}

// Now you can zip the backup & delete it.