How to setup database in VS Setup project?

[Brandon]

I have a .sql script that holds the configuration of a local sql express
database needed for my C# .net forms app. I would like the script to run.
Right now, I have a install_db.cmd file that basically just runs the sql
script from the command line. I made a console app that runs the
install_db.cmd

I added that console app to the application folder and then ran that as a
custom action. The command windows pops up real quick, but then goes away and
the database never actually gets created.

Any ideas how to do this better or make it actually work?

 

[John Saunders [MVP]]

I have a .sql script that holds the configuration of a local sql express
database needed for my C# .net forms app. I would like the script to run.
Right now, I have a install_db.cmd file that basically just runs the sql
script from the command line. I made a console app that runs the
install_db.cmd

I added that console app to the application folder and then ran that as a
custom action. The command windows pops up real quick, but then goes away
and
the database never actually gets created.

Any ideas how to do this better or make it actually work?

You can use something like this:

using (SqlConnection connection = new
SqlConnection(connectionString))

{

using (SqlCommand command = new SqlCommand())

{

command.Connection = connection;

command.CommandText = File.ReadAllText("file.sql");

connection.Open();

command.ExecuteNonQuery();

}

}



The difference is that you need to execute one batch at a time. You can't
execute the whole thing at once. I have used a regular expression and the
..Split method to split the file on the "GO" commands, then execute each
batch one at a time.