Click here to Skip to main content
15,068,433 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
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();
Posted

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
   
Comments
Member 10612142 9-Apr-15 1:44am
   
Problem with this approach is:
If I am using multiple sql commands such as create, alter (table, SP, Func, Insert or update command) then how can I find out the name of table,SP,Fun
Sascha Lefèvre 9-Apr-15 2:23am
   
I don't understand why this should be more difficult when executing the statements separately instead of as a single batch? I think it will be easier instead. For instance you could find out the table name by taking the third identifier from the statement if it begins with "create table" or "alter table" (or the fourth, if the third is "dbo").
Create a Store procedure and put all the SQL code in that.
Then you can call it from C# code.
   
Comments
Member 10612142 8-Apr-15 3:27am
   
After putting sql code in sql stored procedure it providing error like this

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'CREATE'.
Msg 102, Level 15, State 1, Line 4
Incorrect syntax near 'Go'.
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near 'Go'.

Where as I need the name of all Table or Stored procedure which are Pass or failed
Code For You 8-Apr-15 4:18am
   
share the code

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900