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

本文实例讲述了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 = datebak
go 
 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();
    }
   }
  } 
 }
}

希望本文所述对大家的c#程序设计有所帮助。