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)
{
sqltransaction什么意思conn.Close();
conn.Dispose();
}
}
}
return result;
}
}//end of class
}//end of namespace

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。