我要评论本文实例讲述了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#程序设计有所帮助。
您可能感兴趣的文章:
如您对本文有疑问或者有任何想说的，请 点击进行留言回复，万千网友为您解惑！
网友评论