- 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#中 一次执行多条带GO的sql语句
最新推荐文章于 2022-10-19 16:47:53 发布