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();
}
