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#?
-
I would like to code like: <code> using (var conn = new SQLiteConnection("Data Source=:memory:") { conn.Open(); // do insert, update... // how to dump data into a file? conn.Close(); } </code>xiagao1982– xiagao19822012-08-31 08:55:31 +00:00Commented Aug 31, 2012 at 8:55
Add a comment
|
1 Answer
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:
Obtain SQL statements to create new database structure.
select sql from sqlite_master where name not like 'sqlite_%';Obtain names of all user tables.
select name from sqlite_master where type='table' and name not like 'sqlite_%';Create the on-disk database in some new SQLiteConnection.
Execute all previously obtained SQL statements to create the database structure in the on-disk database.
Close the separate connection to the on-disk database.
Attach the on-disk database to the in-memory database.
attach 'ondisk.db3' as 'ondisk';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;