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.