实现一次执行多条带GO的sql语句代码,CSharp代码库,德仔网

代码语言

代码分类

【CSharp】实现一次执行多条带GO的sql语句

作者:天远 / 发布于2015/9/28/ 974


	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();
	 }
	 }
	 }
	}
	}

试试其它关键字

　执行多条　

同语言下

可能有用的

天远贡献的其它代码(16)