How to dump SQLite in-memory database into file wi

Question

I am using System.Data.SQLite.dll to make use of the SQLite in-memory database. After the program finishes, I would like to dump the in-memory database into a .db3 file for next use. How can I achieve this in C#?

Answer 1

To the best of my knowledge there is not built-in functionality to accomplish this in System.Data.SQLite.dll. The functionality does however exist in the sqlite3.exe client maintained along with the SQLite core.

This is how I would do it with system.data.sqlite.dll:

1. Obtain SQL statements to create new database structure.

   select sql from sqlite_master where name not like 'sqlite_%';
   

2. Obtain names of all user tables.

   select name 
   from sqlite_master 
   where type='table' and name not like 'sqlite_%';
   

3. Create the on-disk database in some new SQLiteConnection.

4. Execute all previously obtained SQL statements to create the database structure in the on-disk database.

5. Close the separate connection to the on-disk database.

6. Attach the on-disk database to the in-memory database.

   attach 'ondisk.db3' as 'ondisk';
   

7. For each user table obtained earlier, copy the content from the in-memory to the on-disk database.

   insert into ondisk.TableX select * from main.TableX;
   insert into ondisk.TableY select * from main.TableY;