populate database from sql server generated script

Question

So I'm developing a web application where each user has it's own database. What i want is to automate the process of create the user specific database when he registers.

My idea is to create a new empty database with a custom name and then run a pre-made sql script to generate the tables and default values for this database.

Edit I think I haven't been clear before.

I create the database as follows:

using (var conn = new SqlConnection("data source=MySource; uid=MyUser; pwd=MyPassword;"))
   {
      using (var cmd = new SqlCommand())
      {
         conn.Open();
         cmd.Connection = conn;
         cmd.CommandText = "Create Database MYNewDB;";
         cmd.ExecuteNonQuery();
      }
   }

Is there a way to (after this) execute the scripts saved in .sql file to create the tables and values for this new data base?

How many users do you anticipate? Have you thought about update scripts? This sounds like a very painful architecture to me. Would be much simpler to add a column for UserID to every table and make sure that every query has that additional check. — Sean Lange, Jan 20 '15 at 15:37
Side note: "searched alot" is not useful information in the post. Please either show what approaches you've found that did not work (i.e. link + one sentence description would be perfect) or don't add such text at all. I.e. "tried [Create DB programmatically](http://stackoverflow.com/questions/9015142/creating-a-database-programmatically-in-sql-server) but XXXX prevents me from doing it") — Alexei Levenkov, Jan 20 '15 at 15:38
@SeanLange this is for a web version of a software developed by my company. If only 20% of our clients adhere we're talking about 350 to 450 clients wich can have more than 1 different user and in the tables they'll include their own clients. I think it's for the best to keep a database for each client. — FabioG, Jan 20 '15 at 15:44
So when you update the software you have to update 350-450 databases. That does not sound like fun to me. — Sean Lange, Jan 20 '15 at 15:45
to answer your question you would create a script to CREATE DATABASE xxx etc — Sean Lange, Jan 20 '15 at 15:47
I think i didn't explain myself well, i'll edit the question and try to be clearer — FabioG, Jan 20 '15 at 16:11

score 0 · Accepted Answer · edited May 23 '17 at 12:28

found the solution on this answer.

using (var conn = new SqlConnection("data source=MySource; initial catalog=MYNewDB;persist security info=True;user id=MyUser;password=MyPassword;multipleactiveresultsets=True; "))
{
   string script = File.ReadAllText(@"C:\Somewhere\...\script.sql");

   // split script on GO command
   IEnumerable<string> commandStrings = Regex.Split(script, @"^\s*GO\s*$", RegexOptions.Multiline | RegexOptions.IgnoreCase);

   conn.Open();
   foreach (string commandString in commandStrings)
   {
      if (commandString.Trim() != "")
      {
         using (var command = new SqlCommand(commandString, conn))
         {
            command.ExecuteNonQuery();
         }
      }
   }
   conn.Close();
}

populate database from sql server generated script

1 Answers1