I need to create a new sqlite database with all tables on the fly

Question

Hello,

when a user starts my app he can create a new project which means creating a new database with all tables.

I do not want to copy the structure of the tables from an older database/older project because in the meantime due to an update of the programm the tables could have changed too... this would lead to chrashes.

So with every application/update I deliver I should also deliver a scripte file which creates the database + tables right?

How should the script file lool like and how do you call that in c# ?

Answer 1

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.

Answer 2

For some assistance, you could check out the Public Domain project a friend of mine made: www.codeplex.com/publicdomain. Along with tons of other useful utilities, look into the Data.SQLHelper.GenerateCreateTablesQuery(DataTable[] tables). Just use SQL for the database type.