如何从c＃运行多个Sql命令 [英] How to run multiple Sql command from c#

问题描述

我有以下脚本



CREATE TABLE [dbo]。[ErrorLog]（[ErrorLogID] [int] IDENTITY（1,1）NOT NULL ，[ErrorTime] [datetime] NULL，[UserName] [nvarchar]（128）NULL，[ErrorNumber] [int] NOT NULL，[ErrorSeverity] [int] NULL，[ErrorState] [int] NULL，[ErrorProcedure] [nvarchar ]（126）NULL，[ErrorLine] [int] NULL，[ErrorMessage] [nvarchar]（4000）NOT NULL）ON [PRIMARY]

Go

CREATE TABLE [ dbo]。[ErrorLog1]（[ErrorLogID] [int] IDENTITY（1,1）NOT NULL，[ErrorTime] [datetime] NULL，[UserName] [nvarchar]（128）NULL，[ErrorNumber] [int] NOT NULL， [ErrorSeverity] [int] NULL，[ErrorState] [int] NULL，[ErrorProcedure] [nvarchar]（126）NULL，[ErrorLine] [int] NULL，[ErrorMessage] [nvarchar]（4000）NOT NULL）ON [PRIMARY] ];

Go

更改表格ErrorLog1添加col1 varchar（80）

Go



CREATE TABLE [dbo]。[ErrorLog22]（[ErrorLogID] [int] IDENTITY（1,1）NOT NULL，[ErrorTime] [datetime] NULL，[UserName] [nvarchar]（128）NULL，[ErrorNumbe r] [int] NOT NULL，[ErrorSeverity] [int] NULL，[ErrorState] [int] NULL，[ErrorProcedure] [nvarchar]（126）NULL，[ErrorLine] [int] NULL，[ErrorMessage] [nvarchar]（ 4000）NOT NULL）ON [PRIMARY];

Go



现在我想从我的C＃代码执行它们并找出所有的表名失败，哪些成功，这是我正在使用的代码：



scriptText = File.ReadAllText（lblFilePath.Text）;

sqlConnection = new SqlConnection（connectionString）;

svrConnection = new ServerConnection（sqlConnection）;

server = new Server（svrConnection）;

server.ConnectionContext.ExecuteNonQuery（scriptText）;



sqlConnection.Close（）;

svrConnection.Disconnect（）;

Hi, I have the following script

CREATE TABLE [dbo].[ErrorLog]([ErrorLogID] [int] IDENTITY(1,1) NOT NULL,[ErrorTime] [datetime] NULL, [UserName] [nvarchar](128) NULL,[ErrorNumber] [int] NOT NULL,[ErrorSeverity] [int] NULL,[ErrorState] [int] NULL,[ErrorProcedure] [nvarchar](126) NULL,[ErrorLine] [int] NULL,[ErrorMessage] [nvarchar](4000) NOT NULL) ON [PRIMARY]
Go
CREATE TABLE [dbo].[ErrorLog1]([ErrorLogID] [int] IDENTITY(1,1) NOT NULL,[ErrorTime] [datetime] NULL, [UserName] [nvarchar](128) NULL,[ErrorNumber] [int] NOT NULL,[ErrorSeverity] [int] NULL,[ErrorState] [int] NULL,[ErrorProcedure] [nvarchar](126) NULL,[ErrorLine] [int] NULL,[ErrorMessage] [nvarchar](4000) NOT NULL) ON [PRIMARY];
Go
Alter Table ErrorLog1 add col1 varchar(80)
Go

CREATE TABLE [dbo].[ErrorLog22]([ErrorLogID] [int] IDENTITY(1,1) NOT NULL,[ErrorTime] [datetime] NULL, [UserName] [nvarchar](128) NULL,[ErrorNumber] [int] NOT NULL,[ErrorSeverity] [int] NULL,[ErrorState] [int] NULL,[ErrorProcedure] [nvarchar](126) NULL,[ErrorLine] [int] NULL,[ErrorMessage] [nvarchar](4000) NOT NULL) ON [PRIMARY];
Go

Now I want to execute them from my C# code and find out all of the table name which are fail and which are success, here are the code which I am using:

scriptText = File.ReadAllText(lblFilePath.Text);
sqlConnection = new SqlConnection(connectionString);
svrConnection = new ServerConnection(sqlConnection);
server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(scriptText);

sqlConnection.Close();
svrConnection.Disconnect();

推荐答案

如果脚本中的每个DDL-Statement之后都有一个Go-Statement，那么：

- 拆分 [ ^ ]将您的脚本字符串（scriptText）转换为单个DDL语句，使用Go作为分隔符，

- 迭代带有循环的结果字符串数组

- 在try-catch-block中单独执行每个DDL-Statement，一个捕获的异常会告诉你它失败了，为什么

Provided there is a "Go"-Statement after each DDL-Statement in your script, then:
- split[^] your script-string (scriptText) into single DDL-Statements using "Go" as the delimiter,
- iterate over the resulting string-array with a loop
- execute each DDL-Statement separately in a try-catch-block, a caught exception will tell you that it failed and why

创建一个Store过程并将所有SQL代码放入其中。

然后你可以用C＃代码调用它。

Create a Store procedure and put all the SQL code in that.
Then you can call it from C# code.

这篇关于如何从c＃运行多个Sql命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！