C#中 如何执行带GO的sql 语句

C#中是不允许执行带GO的sql 语句的， 如何做呢？

思路就是将带GO的sql语句转化为分段执行， 但在同一事务内执行。

扩展方法是个很不错的主意， 但是尽量不要影响原来的cmd的一些东东， 如 connection， 故只借用原来的connectionstring和sql , 而 connection 是重新建立的。

1. 预备数据：

 

--删除表
IF( OBJECT_ID('test') IS NOT NULL )
BEGIN
	DROP TABLE test
END
GO
--创建表
CREATE TABLE test(
	id INT IDENTITY(1,1),
	[name] VARCHAR(MAX),
	flag INT
)
GO
--加测试数据
INSERT INTO test VALUES ('init',0)
--
SELECT * FROM test
--id          name       flag
----       ---------     -----
--1           first        0

2. 测试代码：

 

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Collections;
using System.Data;

namespace ConsoleApplication1
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectString = "Data Source=leaf-home\\sqlserver2005;Initial Catalog=managecenter2005;Persist Security Info=True;User ID=site_dev;Password=site_devsite_dev";
            using (SqlConnection conn = new SqlConnection(connectString))
            {
                conn.Open();
                Console.WriteLine("1. 初次能正常执行");
                string sql = "update test set [name]='1st',flag=1";
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                OutputAllInfo(conn);

                Console.WriteLine("2. 执行带GO, 注：修改是分两次");
                sql = "update test set [name]='2nd';GO;update test set [flag]=2";
                cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQueryWithGo();
                OutputAllInfo(conn);

                Console.WriteLine("3. cmd再次执行");
                sql = "update test set [name]='3rd',[flag]=3";
                cmd = new SqlCommand(sql, conn);
                cmd.ExecuteNonQuery();
                OutputAllInfo(conn);
            }

            Console.Read();
        }//end of Main

        public static void OutputAllInfo(SqlConnection conn)
        {
            string sql = "select * from test";
            SqlCommand cmd = new SqlCommand(sql,conn);
            SqlDataAdapter sda = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            sda.Fill(dt);

            foreach (DataColumn dc in dt.Columns) 
            {
                Console.Write(dc.ColumnName+"\t");            
            }
            Console.WriteLine();
            foreach (DataRow dr in dt.Rows) 
            {
                Console.WriteLine(dr["id"].ToString()+"\t"+dr["name"].ToString()+"\t"+dr["flag"].ToString());
            }
        }
    }//end of class

    public static class ExtMethods 
    {
        /// <summary>
        /// 执行带GO的SQL，返回最后一条SQL的受影响行数
        /// </summary>
        /// <param name="sql"></param>
        /// <returns>返回最后一条SQL的受影响行数</returns>
        public static int ExecuteNonQueryWithGo(this SqlCommand oldCmd)
        {
            int result = 0;
            string[] arr = System.Text.RegularExpressions.Regex.Split(oldCmd.CommandText, "GO");
            using (SqlConnection conn = new SqlConnection(oldCmd.Connection.ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = conn;
                SqlTransaction tx = conn.BeginTransaction();
                cmd.Transaction = tx;
                try
                {
                    for (int n = 0; n < arr.Length; n++)
                    {
                        string strsql = arr[n];
                        if (strsql.Trim().Length > 1)
                        {
                            cmd.CommandText = strsql;
                            result = cmd.ExecuteNonQuery();
                        }
                    }
                    tx.Commit();
                }
                catch (System.Data.SqlClient.SqlException E)
                {
                    tx.Rollback();
                    //return -1;
                    throw new Exception(E.Message);
                }
                finally 
                {
                    if (conn.State != ConnectionState.Closed) 
                    {
                        conn.Close();
                        conn.Dispose();
                    }
                }
            }
            return result;
        }
    }//end of class
}//end of namespace