C#中实现一次执行多条带GO的sql语句实例
本文实例讲述了C#中实现一次执行多条带GO的sql语句。分享给大家供大家参考。具体如下:
usingSystem; usingSystem.Collections.Generic; usingSystem.Linq; usingSystem.Text; usingSystem.Collections; usingSystem.Text.RegularExpressions; usingSystem.Data.SqlClient; namespaceConsoleApplication1 { classProgram { //注:在cmd.ExecuteNonQuery()是不允许语句中有GO出现的,有则出错。 staticstringconnectionString="server=20111011-2204\\SQLSERVER2008;uid=ecuser;pwd=1234;database=Stu;"; staticvoidMain(string[]args) { stringsql= @"AltertableStudentaddDateBakvarchar(16) go UpdateStudentsetDateBak=convert(char,getdate(),101) go UpdateStudentsetMemo=DateBak go AltertableStudentdropcolumnDateBak go "; Console.WriteLine("1.不用事务:"); ExecuteSqlWithGo(sql); Console.WriteLine("2.用事务:"); ExecuteSqlWithGoUseTran(sql); Console.ReadLine(); } publicstaticvoidExecuteSqlWithGo(Stringsql) { inteffectedRows=0; using(SqlConnectionconn=newSqlConnection(connectionString)) { conn.Open(); SqlCommandcmd=newSqlCommand(); cmd.Connection=conn; try { //注:此处以换行_后面带0到多个空格_再后面是go来分割字符串 String[]sqlArr=Regex.Split(sql.Trim(),"\r\n\\s*go",RegexOptions.IgnoreCase); foreach(stringstrsqlinsqlArr) { if(strsql.Trim().Length>1&&strsql.Trim()!="\r\n") { cmd.CommandText=strsql; effectedRows=cmd.ExecuteNonQuery(); } } } catch(System.Data.SqlClient.SqlExceptionE) { thrownewException(E.Message); } finally { conn.Close(); } } } publicstaticvoidExecuteSqlWithGoUseTran(Stringsql) { using(SqlConnectionconn=newSqlConnection(connectionString)) { conn.Open(); SqlCommandcmd=newSqlCommand(); cmd.Connection=conn; SqlTransactiontx=conn.BeginTransaction(); cmd.Transaction=tx; try { //注:此处以换行_后面带0到多个空格_再后面是go来分割字符串 String[]sqlArr=Regex.Split(sql.Trim(),"\r\n\\s*go",RegexOptions.IgnoreCase); foreach(stringstrsqlinsqlArr) { if(strsql.Trim().Length>1&&strsql.Trim()!="\r\n") { cmd.CommandText=strsql; cmd.ExecuteNonQuery(); } } tx.Commit(); } catch(System.Data.SqlClient.SqlExceptionE) { tx.Rollback(); thrownewException(E.Message); } finally { conn.Close(); } } } } }
希望本文所述对大家的C#程序设计有所帮助。