Sqlite Move Database to Memory in C#

Question

How can I move an sqlite disk database to memory in C# ?

I need to open a database from disk to an in-memory database for faster operations and when it finishes the work then save it back to file.

The disk database is an empty database, I want to transfer the data from a List to the memory database and use this to sort very large data which kills the List.Sort function. After the data is sorted I don't need the database anymore.

Don't do that. Increase the cache size, and memory map the database instead. — Shawn
– Shawn, Commented Jul 7, 2019 at 7:38

Ali Bahrami · Accepted Answer · 2019-07-07 07:42:35Z

1

I'm afraid you cannot do that. And I do not recommend that, Why don't you just increase the cache size?

An SQLite database is normally stored in a single ordinary disk file. However, in certain circumstances, the database might be stored in memory.

The most common way to force an SQLite database to exist purely in memory is to open the database using the special filename ":memory:". In other words, instead of passing the name of a real disk file into one of the sqlite3_open(), sqlite3_open16(), or sqlite3_open_v2() functions, pass in the string ":memory:". For example:

And every connection to SQlite creates a seperated new database.

rc = sqlite3_open(":memory:", &db); When this is done, no disk file is opened. Instead, a new database is created purely in memory. The database ceases to exist as soon as the database connection is closed. Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

If you are going to implement such a thing, there are not any official way to do it! But you can:

Open a new in-memory db
Copy all data from file db to memory
Compare and merge new/deleted/updated data from memory to disk

Here is code in C language as an example for opening a SQLite file as an in-memory:

https://www.mail-archive.com/[email protected]/msg15929.html

answered Jul 7, 2019 at 7:42

Ali Bahrami

6,0833 gold badges36 silver badges53 bronze badges

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

2 Comments

Shawn Over a year ago

There actually are several ways to do it (Though a particular language's sqlite bindings might not export the appropriate C API, and at least one of them requires sqlite to be compiled with a specific non-default option, so they're not very portable unless using C or C++ where you can include the sqlite source file directly in your project). It is, however, yes, a bad idea, especially if you want changes to persist in the on-disk copy.

Mario Over a year ago

thanks! I don't know if the cache will help with adding 1.5 million records, then sorting it. (select order by). The disk database is just an empty database, I want to load it into memory then add data from List then sort the data and discard the database.

Collectives™ on Stack Overflow

Sqlite Move Database to Memory in C#

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related