C#中实现一次执行多条带GO的sql语句实例
作者:yenange 时间:2023-07-12 10:23:53
本文实例讲述了C#中实现一次执行多条带GO的sql语句。分享给大家供大家参考。具体如下:
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();
}
}
}
}
}
希望本文所述对大家的C#程序设计有所帮助。
标签:C#,sql语句
![](/images/zang.png)
![](/images/jiucuo.png)
猜你喜欢
SpringBoot实现RAS+AES自动接口解密
2022-11-05 04:07:01
![](https://img.aspxhome.com/file/2023/5/119785_0s.jpg)
浅析Java线程的中断机制
2023-11-19 20:11:50
Java最简洁数据结构之冒泡排序快速理解
2023-12-01 06:31:52
![](https://img.aspxhome.com/file/2023/8/60278_0s.png)
spring cloud consul注册的服务报错critical的解决
2021-05-28 14:13:14
![](https://img.aspxhome.com/file/2023/5/64925_0s.jpg)
详解Mybatis中常用的约束文件
2023-11-28 08:02:17
浅谈Java封装、继承、多态特性
2023-10-09 13:59:35
![](https://img.aspxhome.com/file/2023/6/67626_0s.png)
C#中用foreach语句遍历数组及将数组作为参数的用法
2022-03-14 16:25:12
Netty分布式高性能工具类异线程下回收对象解析
2023-07-09 21:02:43
![](https://img.aspxhome.com/file/2023/2/99882_0s.png)
Android中Blade的使用方法
2023-04-25 11:29:54
c#根据文件大小显示文件复制进度条实例
2022-10-10 10:35:16
![](https://img.aspxhome.com/file/2023/5/116575_0s.png)
C语言进制转换代码分享
2021-10-16 15:39:23
详解C# partial 关键字的使用
2023-06-07 20:46:09
![](https://img.aspxhome.com/file/2023/5/98255_0s.png)
OpenFeign设置header的三种方式总结
2023-06-25 19:03:46
![](https://img.aspxhome.com/file/2023/8/79338_0s.jpg)
MyBatis一对多嵌套查询的完整实例
2023-07-12 02:49:56
Java自动添加重写的toString方法详解
2021-11-05 14:29:13
![](https://img.aspxhome.com/file/2023/5/71835_0s.png)
C++共享内存删除的陷阱
2022-04-03 12:30:37
java如何将int数组转化为Integer数组
2021-07-19 04:22:37
SpringBoot数据访问自定义使用Druid数据源的方法
2023-01-10 16:29:38
![](https://img.aspxhome.com/file/2023/1/91081_0s.jpg)
c# 引用类型与值类型的区别详解
2021-05-27 00:34:33
Java多线程:生产者与消费者案例
2021-06-30 05:47:08
![](https://img.aspxhome.com/file/2023/0/89940_0s.png)