本文实例讲述了C#中实现一次执行多条带GO的sql语句。分享给大家供大家参考。具体如下：
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
|
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 = DateBakgo 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#程序设计有所帮助。
