Dynamic database creation

Hi All,

I have a web site where the user can select from a list of databases (they're actually all the same structure, but have data unique to each client). Each database is a SQL 2005 database held in the App_Data folder. This all works a treat. Each database is simply named after the client it is holding data for, and the connection string for each is stored in a database that is accessed

However, what I need is a form where a database name can be entered, and I can then create a new database with the same structure as the others - including tables, indexes and stored-procedures. I know I can use the copy database wizard in the Management Studio, but that isn't an option to the users of this system (it's to be used on an intranet, and the page I'm trying to create will only be used by project managers - but these people won't have a clue when it comes to configuring databases themselves). I have scripted a database, but have no idea on how I can actually use this script from within Visual Studio and more importantly, don't know how to configue it to have the database name as a parameter.

I've also seen some examples that suggest using SQL-DMO, but haven't been able to work out how to use it and according to Microsoft it shouldn't be used for new developments (http://msdn2.microsoft.com/en-us/library/ms132924.aspx).

Anyway, I really hope someone is able to offer some assistance on this one as I've spent a whole day trawling forums and various web-sites trying to find a solution to this.

Thanks & regards,

Paul

Hi Paul,

As the simplest solution you can run the entire DB creation script through code, for e.g.:

using (SqlConnection cn = new SqlConnection("connectionString from config file"))
{
StringBuilder sql = new StringBuilder();
sql.Append(" use master; create database " + txtDatabase.Text);

SqlCommand cmd = new SqlCommand(sql.ToString(), cn);
cn.Open();
cmd.ExecuteNonQuery();
}

Hope this helps,

Vivek

|||

Hi Vivek,

Thanks for your reply. I haven't seen / used sql.Append before, so that definitely could be a way around the problem. I had hoped that there would be a way of running the .sql script, so that if I make any changes to the database layout then I can simply issue a new script, but it wouldm't be the end of the world if I have to issue a new vb file instead. I'll let you know how I get on over the next few days!

Thanks again,

Paul