python执行多个sql语句_c#教程之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();

}

}

}

}

}