How to run a SQL database installer from your .net application

Even if the question itself is very obvious, I got too many problems while implementing it. Here's the situation: I have a WinForms app (C#), I want to offer the user with creating a SQL database and a couple of database objects (SPs, tables) at the first time he/she runs the application.

The CREATE scripts are in a .sql file. It was generated with SQL Management Studio. If I read the file and pass it over to the SqlCommand object (as a string), it fails. If the file were just to contain CREATE TABLE statements, it would work, but ADO.NET doesn't like the CREATE PROC statements. If it's only CREATE PROC statements, it still fails. So, the principle is that every CREATE PROC has to run seperately. CREATE TABLEs can run together. So, what I did was, I was breaking the script to separate statements and ran them one by one by using the SqlCommand object. GO commands can be used to split up the file to multiple parts if there's no "GO" word in your script. Here it is:

// Break the SQL script to statements
// (delimited by the GO command).
// Without doing this, it doesn't work. NOTE: it's important that
// the SQL script shouldn't contain *ANY* GO word except the ones
// used to delimit, otherwise it won't work. We are using GO
// because OSQL also understands GO - so the script remains
// compatible. Another solution would be to use a
// commented delimiter.
string[] delimitedSqlCommand = databaseScript.Split(
    new string[1] { "GO" }, StringSplitOptions.None);

// Creates the db objects
foreach (string sqlCommand in delimitedSqlCommand)
{
    SqlCommand command = new SqlCommand(
sqlCommand, masterConnection);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}

Comments

• Anonymous
  January 20, 2008
  The comment has been removed

• Anonymous
  February 03, 2008
  Running MS SQL scripts from .Net

• Anonymous
  March 28, 2008
  Running MS SQL scripts from .Net

• Anonymous
  February 19, 2009
  Running MS SQL scripts from .Net

• Anonymous
  November 05, 2009
  remember in using C# sharp. You will also need to deal with newline and backslash character replacements as well. Couldn't get this method to work till I figured that out.