学步园

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