C#中 一次执行多条带GO的sql语句

C#中 一次执行多条带GO的sql语句

分类： C# 2011-11-22 17:44 186人阅读 评论(0) 收藏 举报

[csharp] view plain copy print ?

1. using System;  
2. using System.Collections.Generic;  
3. using System.Linq;  
4. using System.Text;  
5. using System.Collections;  
6. using System.Text.RegularExpressions;  
7. using System.Data.SqlClient;    
8.   
9. namespace ConsoleApplication1  
10. {  
11.     class Program  
12.     {  
13.         //注： 在cmd.ExecuteNonQuery()是不允许语句中有GO出现的， 有则出错。   
14.         static string connectionString = "server=20111011-2204\\SQLSERVER2008;uid=ecuser;pwd=1234;database=Stu;";  
15.         static void Main(string[] args)  
16.         {  
17.             string sql =   
18. @"Alter table Student add DateBak varchar(16)    
19.       go    
20.    Update Student set DateBak = convert(char,getdate(),101)  
21.  go    
22.    Update Student set Memo = DateBak  
23. go    
24.    Alter table Student drop column DateBak    
25. go    
26.   
27. ";  
28.             Console.WriteLine("1. 不用事务：");  
29.             ExecuteSqlWithGo(sql);  
30.             Console.WriteLine("2. 用事务：");  
31.             ExecuteSqlWithGoUseTran(sql);  
32.             Console.ReadLine();  
33.         }  
34.   
35.         public static void ExecuteSqlWithGo(String sql)  
36.         {  
37.             int effectedRows = 0;  
38.             using (SqlConnection conn = new SqlConnection(connectionString))  
39.             {  
40.                 conn.Open();  
41.                 SqlCommand cmd = new SqlCommand();  
42.                 cmd.Connection = conn;  
43.                 try  
44.                 {  
45.                     //注： 此处以 换行_后面带0到多个空格_再后面是go 来分割字符串   
46.                     String[] sqlArr = Regex.Split(sql.Trim(), "\r\n\\s*go", RegexOptions.IgnoreCase);    
47.                     foreach (string strsql in sqlArr)  
48.                     {  
49.                         if (strsql.Trim().Length > 1 && strsql.Trim()!="\r\n")  
50.                         {  
51.                             cmd.CommandText = strsql;  
52.                             effectedRows = cmd.ExecuteNonQuery();  
53.                         }  
54.                     }  
55.                 }  
56.                 catch (System.Data.SqlClient.SqlException E)  
57.                 {  
58.                     throw new Exception(E.Message);  
59.                 }  
60.                 finally  
61.                 {  
62.                     conn.Close();  
63.                 }  
64.             }  
65.         }  
66.   
67.         public static void ExecuteSqlWithGoUseTran(String sql)  
68.         {  
69.             using (SqlConnection conn = new SqlConnection(connectionString))  
70.             {  
71.                 conn.Open();  
72.                 SqlCommand cmd = new SqlCommand();  
73.                 cmd.Connection = conn;  
74.                 SqlTransaction tx = conn.BeginTransaction();  
75.                 cmd.Transaction = tx;  
76.                 try  
77.                 {  
78.                     //注： 此处以 换行_后面带0到多个空格_再后面是go 来分割字符串   
79.                     String[] sqlArr = Regex.Split(sql.Trim(), "\r\n\\s*go", RegexOptions.IgnoreCase);    
80.                     foreach (string strsql in sqlArr)  
81.                     {  
82.                         if (strsql.Trim().Length > 1 && strsql.Trim() != "\r\n")  
83.                         {  
84.                             cmd.CommandText = strsql;  
85.                             cmd.ExecuteNonQuery();  
86.                         }  
87.                     }  
88.                     tx.Commit();  
89.                 }  
90.                 catch (System.Data.SqlClient.SqlException E)  
91.                 {  
92.                     tx.Rollback();  
93.                     throw new Exception(E.Message);  
94.                 }  
95.                 finally  
96.                 {  
97.                     conn.Close();  
98.                 }  
99.             }  
100.         }    
101.     }  
102. }