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#程序设计有所帮助。