在C#中执行带有GO的批量sql语句

在C#中执行带有GO的批量sql语句

思海网络 出品()

在用C#中调用ADO执行批量SQL语句的时候,会出现无法识别GO语句的错误。这个时候,我们以GO语句为分割点,把整个批量语句分割成N块独立的SQL语句代码块(不包含GO语句),然后再顺序执行每一块代码。
要是Go很少,可以把整个sql,以go为分割点,拆成N个sql1,sql2,sql3……再用SqlCommand的CommandText=sql1,sql2,sql3……一个个执行!

编程实现方法如下:

System.Data.SqlClient.SqlCommand sqlCommand = new System.Data.SqlClient.SqlCommand();
sqlCommand.Connection = this.Connection;
//这里的 this.Connection 替换成你的SQL数据库连接     
System.Collections.ArrayList al = new System.Collections.ArrayList();      
System.Text.RegularExpressions.Regex reg = new System.Text.RegularExpressions.Regex( @"^(\s*)go(\s*)$", System.Text.RegularExpressions.RegexOptions.IgnoreCase | System.Text.RegularExpressions.RegexOptions.Multiline | System.Text.RegularExpressions.RegexOptions.Compiled | System.Text.RegularExpressions.RegexOptions.ExplicitCapture );
al.AddRange( reg.Split(SQLString) );//SQLString是你的原始SQL批量语句,在这里通过上面的正则表达式进行分割,然后添加到集合(al实例)中。      
foreach( string tmp in al )//循环执行每一个SQL代码块      
{      
  sqlCommand.CommandText = tmp.Trim();     
  if(sqlCommand.CommandText.Length > 0)      
  {       
      sqlCommand.ExecuteNonQuery();     

  }    

 

后者:

需要在C#代码中执行一个SQL文件的内容。遇到了两个问题:

1.       因为SQL文件中有“GO”语句,执行时报错“Incorrect syntax near 'GO'.”。

2.       如果将SQL文件中有“GO”语句全部去掉,则可能出现SQL运行错误。原因是去掉“GO”后,等于整个文件的内容一次执行,但是SQL文件有可能后面的内容需要在前面某些语句已经执行的基础上才能执行的。

 

解 决的办法是:用一个StringBuilder,然后一行一行的读SQL文件,如果该行的内容不是“GO”,就向StringBuilder后 Append。如果是“GO”,就执行StringBuilder的内的SQL语句,然后清空StringBuilder。这样就能达到与直接执行带 “GO”语句的SQL文件一样的效果。

 

代码如下:

private static void ExecuteSQLFile(String sqlFileName)

        {

            SqlConnection connecction = null;

            try

            {

                connecction = new SqlConnection(@"uid=XXXX; pwd=XXXX; server=XXXX; database=XXXX; connection timeout=30");

                SqlCommand command = connecction.CreateCommand();

                connecction.Open();

 

                FileStream stream = new FileStream(sqlFileName, FileMode.Open);

                StreamReader reader = new StreamReader(stream);                             

 

                StringBuilder builder = new StringBuilder();

                String strLine = "";

                while ((strLine = reader.ReadLine()) != null)

                {

                    if (strLine.Trim().ToUpper() != @"GO")

                    {

                        builder.AppendLine(strLine);

                    }

                    else

                    {

                        command.CommandText = builder.ToString();

                        command.ExecuteNonQuery();

                        builder.Remove(0, builder.Length);

                    }

                }

 

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

            }

            finally

            {

                if (connecction != null && connecction.State != ConnectionState.Closed)

                {

                    connecction.Close();

                }

            }

        }

 来自于:http://sammer110.blog.163.com/blog/static/17139643420113200030180/

要么你就用:DbHelper类,但是这个似乎需要System.Activities.Presentation组件~查考以下说明:

http://www.cnblogs.com/wangjunchao/archive/2010/05/23/1742206.html

以下是我在查询分析器中能正常使用的脚本代码。
USE [RadarDataBase]
 2                     GO
 3                     SET ANSI_NULLS ON
 4                     GO
 5                     SET QUOTED_IDENTIFIER ON
 6                     GO
 7                     create proc [dbo].[P_KillConnections]
 8                     @dbname varchar(200)
 9                     as
10                     declare @sql nvarchar(500)
11                     declare @spid nvarchar(20)
12                     declare #tb cursor for
13                     select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
14                     open #tb
15                     fetch next from #tb into @spid
16                     while @@fetch_status=0
17                     begin
18                     exec('kill )
19                     fetch next from #tb into @spid
20                     end close #tb deallocate #tb ";

查询分析器是sql客户端,它可以识别go用来分批提交,但go不是sql语句,sql服务器不能识别所以不能用在程序中。

所以将带有GO的语句分成多条sql语句,执行多条SQL语句,实现数据库事务,代码如下:

 public int CreateDB_KillProc_proc()
 2         {
 3             List<string> strSqls = new List<string>();
 4             int result = 0;
 5             string SqlStr = "";
 6             //组合Sql语句
 7             SqlStr += "USE [RadarDataBase]";
 8             strSqls.Add(SqlStr);
 9             SqlStr = "SET ANSI_NULLS ON ";
10             strSqls.Add(SqlStr);
11             SqlStr = "SET QUOTED_IDENTIFIER ON  ";
12             strSqls.Add(SqlStr);
13             SqlStr = @"create proc [dbo].[P_KillConnections]
14                     @dbname varchar(200)
15                     as
16                     declare @sql nvarchar(500)
17                     declare @spid nvarchar(20)
18                     declare #tb cursor for
19                     select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
20                     open #tb
21                     fetch next from #tb into @spid
22                     while @@fetch_status=0
23                     begin
24                     exec('kill )
25                     fetch next from #tb into @spid
26                     end close #tb deallocate #tb";
27             strSqls.Add(SqlStr);
28             //执行Sql语句
29             try
30             {
31                 result = DbHelperSQL.ExecuteSqlTran(strSqls);//这个函数功能是执行多条sql语句实现数据库事务
32             }
33             catch (Exception e)
34             {
35                 ErrStr = e.Message;
36                 return -2;
37             }
38             return result;
39         }
40

 

转载于:https://www.cnblogs.com/wahaccp/p/3254754.html

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

表情包
插入表情
评论将由博主筛选后显示,对所有人可见 | 还能输入1000个字符
©️2021 CSDN 皮肤主题: 编程工作室 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值