C#中实现一次执行多条带GO的sql语句实例

using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.Collections;using System.Text.RegularExpressions;using System.Data.SqlClient; namespace ConsoleApplication1{ class Program {  //注： 在cmd.ExecuteNonQuery()是不允许语句中有GO出现的， 有则出错。  static string connectionString = "server=20111011-2204//SQLSERVER2008;uid=ecuser;pwd=1234;database=Stu;";  static void Main(string[] args)  {   string sql = @"Alter table Student add DateBak varchar(16)   go  Update Student set DateBak = convert(char,getdate(),101) go  Update Student set Memo = DateBakgo  Alter table Student drop column DateBak go ";   Console.WriteLine("1. 不用事务：");   ExecuteSqlWithGo(sql);   Console.WriteLine("2. 用事务：");   ExecuteSqlWithGoUseTran(sql);   Console.ReadLine();  }  public static void ExecuteSqlWithGo(String sql)  {   int effectedRows = 0;   using (SqlConnection conn = new SqlConnection(connectionString))   {    conn.Open();    SqlCommand cmd = new SqlCommand();    cmd.Connection = conn;    try    {     //注： 此处以 换行_后面带0到多个空格_再后面是go 来分割字符串     String[] sqlArr = Regex.Split(sql.Trim(), "/r/n//s*go", RegexOptions.IgnoreCase);      foreach (string strsql in sqlArr)     {      if (strsql.Trim().Length > 1 && strsql.Trim()!="/r/n")      {       cmd.CommandText = strsql;       effectedRows = cmd.ExecuteNonQuery();      }     }    }    catch (System.Data.SqlClient.SqlException E)    {     throw new Exception(E.Message);    }    finally    {     conn.Close();    }   }  }  public static void ExecuteSqlWithGoUseTran(String sql)  {   using (SqlConnection conn = new SqlConnection(connectionString))   {    conn.Open();    SqlCommand cmd = new SqlCommand();    cmd.Connection = conn;    SqlTransaction tx = conn.BeginTransaction();    cmd.Transaction = tx;    try    {     //注： 此处以 换行_后面带0到多个空格_再后面是go 来分割字符串     String[] sqlArr = Regex.Split(sql.Trim(), "/r/n//s*go", RegexOptions.IgnoreCase);      foreach (string strsql in sqlArr)     {      if (strsql.Trim().Length > 1 && strsql.Trim() != "/r/n")      {       cmd.CommandText = strsql;       cmd.ExecuteNonQuery();      }     }     tx.Commit();    }    catch (System.Data.SqlClient.SqlException E)    {     tx.Rollback();     throw new Exception(E.Message);    }    finally    {     conn.Close();    }   }  }  }}