How can I create a new SQLite database, with all tables, on the fly? [duplicate]

Question

When a user starts my app he or she can create a new project, which means creating a new database with all tables.

I don't want to copy the structure of the tables from an older database/project because in the meantime, the tables could have changed due to an update by the program. This would lead to crashes.

So with every application update I deploy, I should also deploy a script file which creates the database and tables, right?

What should the script file look like and how would you call it in C#?

Answer 1

Maybe this will help:

SQLiteConnection.CreateFile("c:\\Ik.db");

Create a new database on the fly and execute a query on it. The query should be something like this:

CREATE TABLE IF NOT EXISTS [Setting](
[ID] INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
[Key] NVARCHAR(2048) NULL,
[Value] NVARCHAR(2048) NULL
);

Answer 2

You could write a script to do it all, but you could also have a unit test project that tests your database interface. If you do that, then you'll want to be able to create a test database with dummy data for tests only. Basically, my point is that if you use a script file to create the application data and if you want to add unit tests, then you'll need to either have another script file for them, or you will want to create your database in code.

I would advocate the authoring of a class in your code that can create the database schema on the fly for the application and unit tests. Then if you change the schema, you only have to worry about it in one place and not have to deal with things getting out of sync (hopefully).

Since you're using SQLite and C#, I recommend giving SQLite.NET a try. It has made my life a lot easier, and I've been very happy with it thus far.

With SQLite.NET, you would create an SQLiteCommand, set its CommandText to "CREATE TABLE (your schema here)", and then call ExecuteNonQuery to generate the table.