Saturday, April 14, 2012

[C#] ExecuteNonQuery script to create database with GO word

You develop a business application, it use SQL server database. So before deliver you need to create setup package, it will auto run sql create database.
Then, you found ExecuteNonQuery statement to use to run sql script. But we have some issue in here. It can run only one sql statement in each times, I mean if you use GO, it will false to run. OK, here is a simple way to solve it.


   1:                  string script = File.ReadAllText(txtPath.Text);
   2:                  var conn = new SqlConnection(txtConnectionString.Text);
   3:                  var comm = new SqlCommand(string.Empty, conn);
   4:                  conn.Open();
   5:   
   6:                  string[] commands = script.Split(new[] {Environment.NewLine + "GO" + Environment.NewLine},
   7:                                                   StringSplitOptions.RemoveEmptyEntries);
   8:   
   9:                  foreach (var command in commands)
  10:                  {
  11:                      comm.CommandText = command;
  12:                      comm.ExecuteNonQuery();
  13:                  }
  14:   
  15:                  conn.Close();

In this sample code txtPath.Text is path to your sql script file. And txtConnectionString.Text store you connection string. OK, this is easy to understand.

Hope you enjoy!

No comments:

Post a Comment