Create Databases in a C# application

Question

Hi There,

What is the best way to create databases on C# application, I mean by a click of a button, it will create a database with a different name but the same model?

Currently, I script TSQL onto a C# application, so when I click a button, a new database will be created with the name I defined, it works well, however, it is extremely difficult to maintain, is there a better way to create database on C#?

Regards

Answer 1

One way is to just have a copy of your database file (mdf) available for pushing onto a new server. This similiar to what microsoft does with it's Model database. Whenever you create a new database it starts by making a copy of that one.

Your button click could copy the database file to the desired location, rename it as appropriate, then attach it to the running sql server instance. Whenever you need to update the database with a new schema, just copy the mdf to your deployment directory.

Answer 2

You can use SMO to create databases. See MSDN sample.

Answer 3

Sql Server 2008 Management Studio can generate SQL script for database generation. Save script in text file and later spool it to RDBMS and create database.

This is not too hard to maintain.

To generate script in SqlServer 2008 Management Studio: right click on database -> Tasks -> Create Scripts, click few times and save to file.