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?

Solution 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

Solution 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.

Solution 3

Assuming the use of c#-sqlite,

when you create the connection, using the appropriate API, you actually pass in tbe name of the file to use as a parameter. If the file does not exist, then it is created (by default). e.g.

new SQLiteDatabase("c:\\foo.db")

Of course, because you'll need to escape the backslashes (this is because it's a string). Creating a file beforehand does not work, because the resultant file is not an sqlite database, and therefore is not usable (so, I'm assuming you're getting an error in this case).

Comments

[Joe]

Author is asking how to do it in code using the library, not from the command line util.

[Orn Kristjansson]

Like this you mean ? SQLiteConnection.CreateFile("c:\\mydatabasefile.db3");

[Mike Webb]

I need to use one that builds on ADO.NET libraries. That's why I'm using System.Data.SQLite.

[Mike Webb]

Yes! Like that. Update your answer to what you just commented and I will accept it. That's what I needed.

[Mike Webb]

Thanks. I completely missed the static methods in the SQLiteConnection object.