Database table initialization

So how do you create, update and initialize the tables and data in your application's database. The product I currently work on supports both being installed locally by the customer or being hosted by us as a SaaS offerring. Additionally we support a customer using either Oracle or SQL Server as the backend database so we wanted our database initialization routines to be as database agnostic as possible (rather than using SQL scripts).

We've created classes and objects in C# that represent the tables and columns that the application uses. These classes generate the appropriate SQL needed to setup the database. The db setup code can be run to initialize a database from scratch or update an existing install to the lastest version. It also creates indexes and seeds the tables with the shipped data. To create a table we have code like:

1 2 3 4 5 6 7 8 9 10 11 12

// Create a basic users table. DbTableBuilder table = new DbTableBuilder(dbType, "USERS", setupLog); table.AddKeyColumn("userid"); table.AddCharColumn("username", 255); table.AddCharColumn("password", 50); table.AddCharColumn("email", 100); table.AddCharColumn("name", 100); table.CreateTable(connection);   // Create an index on the username. index = new DbIndexStatement("IDX_USERS_NAME", "USERS", "username"); index.CreateIndex(connection);

I've started experimenting with LINQ to SQL and the Entity Framework. It's nice how it will auto-create the tables (at least in SQL Server) but not sure the best way to seed the tables with shipped data or recreate both production and testing databases. I also ran across an open source project (but can't now find it again) that uses a JSON formatted file to specify the data table structure and used that to create the tables.

For now I think we will stick with the C# code but keep looking for other ideas.