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!

Great day with English

Today is really great day of my English life :D I talk English in 2 hours, avoid all hesitate.
I really love my friends. They are great students and always hard study. When I meet them I feel relax.
I want to send my warm thank to them.
Tomorrow, I will write some thing about today. Now I have a little tired. I will go to bed soon.