How do I create and persist a SQLite DB from scratch in code using System.Data.SQLite and C#?

Question

I have a database creation tool and am creating a database from scratch. This is done the same way as mentioned Here. It creates a temporary DB and I can add data to it.

When I close the database it is deleted, which is the expected behavior, but I do not want it deleted.

I cannot find a Save or Create method in System.Data.SQLite and creating one with System.IO.File.Create(myDBFilename); creates a file that SQLite cannot connect to.

How do I create and persist a database in code from scratch?

Answer 1

Try this one, SQLiteConnection.CreateFile("c:\mydatabasefile.db3");

if all else fails, here is command line stuff as well

sqlite3 test.db
sqlite>CREATE TABLE cars ( id INTEGER PRIMARY KEY AUTOINCREMENT, model text, year integer );
sqlite>insert into cars( model, year ) values( “Ford 350″, 2007 );
sqlite>insert into cars( model, year ) values( “Buick Skylark”, 1953 );
sqlite>insert into cars( model, year ) values( “Honda Civic”, 2002 );
sqlite>Select * from cars;
1|Ford 350|2007
2|Buick Skylark|1953
3|Honda Civic|2002
sqlite>.quit

Answer 2

You don't need to call the SQLiteConnection.CreateFile method.

Assuming you are opening a connection like this:

using (var connection = new SQLiteConnection("Data Source=C:\\database.db"))
{
    // foo
}

It will try to open the database file C:\\database.db if it exists, and if it doesn't exist, it will be created.