Memory Stream as DB

Question

I'm currently thinking of using SQLite as db engine for my C# project, but i ran into the following problem: i can't find any API for memory storage. What i want to achieve is the following:

Upon start of the program i want to load the db file (from HDD) into memory. During execution of the program i want to use this memory stream as a real db (read,write,insert,select etc). Upon closing save the stream to the file.

Can anyone point me in the right way or suggest another db engine that would be better suited for this purpose.

score 46 · Accepted Answer · answered Jul 08 '12 at 17:47

46

You can use SQLite Online Backup API that has ability to copy db file to memory, memory to file. Native support for SQLite Online Backup API is present in System.Data.SQLite from version 1.0.80.0 (with SQLite 3.7.11).

This is simple example how API can be used in C#:

SQLiteConnection source = new SQLiteConnection("Data Source=c:\\test.db");
source.Open();

using (SQLiteConnection destination = new SQLiteConnection(
  "Data Source=:memory:"))
{
  destination.Open();               

  // copy db file to memory
  source.BackupDatabase(destination, "main", "main",-1, null, 0);
  source.Close();

  // insert, select ,...        
  using (SQLiteCommand command = new SQLiteCommand())
  {
    command.CommandText =
      "INSERT INTO t1 (x) VALUES('some new value');";

    command.Connection = destination;
    command.ExecuteNonQuery();
  }             

  source = new SQLiteConnection("Data Source=c:\\test.db");
  source.Open();

  // save memory db to file
  destination.BackupDatabase(source, "main", "main",-1, null, 0);
  source.Close();               
}

answered Jul 08 '12 at 17:47

saladin

576
6
3

1

Thank you, for this nice and clean solution. – Anonymous Jul 08 '12 at 20:40
14

Is there a way to do the same thing to get the byte[] without touching disk? – Gleno Aug 31 '13 at 22:57
3

Warning: Using the Backup API for an in-memory database *removes [Durability](http://en.wikipedia.org/wiki/Durability_(database_systems)) guarantees*. – user2864740 Sep 15 '14 at 19:51
1

@Gleno: You take the byte[] and save to a temp file (temp.db), then use the backup API to to copy it into a memory db. Then make changes. Then store to temp.db again using backup api. Then read the temp.db back into byte[]. – Quintonn Dec 28 '15 at 16:05
1

This API is written in C. how can i use this API with mono? please suggest – chetanya gehlot May 25 '16 at 07:11
This has given me amazing performance gains. Is writing to memory thread safe? i.e. if I have two simultaneous connections using the "Data Source=:memory:" connection will there be any problems. – David Christopher Reynolds Sep 06 '17 at 15:47
1

Gleno have you found any solution regarding saving sqlite database to memorystream ? – Nick Chan Abdullah Sep 26 '19 at 04:52
@saladin this answer literally saved me on an identical scenario. Thank you so much!!! – felisimo Jul 01 '20 at 12:42

Memory Stream as DB

1 Answers1

Linked