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.

saladin · Accepted Answer · 2012-07-08 17:47:23Z

48

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 8, 2012 at 17:47

saladin

5966 silver badges3 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Anonymous Over a year ago

Thank you, for this nice and clean solution.

Gleno Over a year ago

Is there a way to do the same thing to get the byte[] without touching disk?

user2864740 Over a year ago

Warning: Using the Backup API for an in-memory database removes Durability guarantees.

Quintonn Over a year ago

@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[].

chetanya gehlot Over a year ago

This API is written in C. how can i use this API with mono? please suggest

|

Collectives™ on Stack Overflow

Memory Stream as DB

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related