Executing SQL Server Script files from C#

[dreynglar]

Hi,

I am attempting to execute SQL Server script files from C#. Some of the scripts create stored procedures, tables, views etc - this C# program is intended to be able to create a "clean" database from scripts kept in our version control system.

Most of our scripts attempt to determine if the object in the script file exists by doing:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[foo]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
DROP PROCEDURE [dbo].[foo]
END
GO
CREATE PROCEDURE foo
.
.
.

I read the contents of the script file into the SqlCommand.CommandText and then use ExecuteNonQuery.

But, when I do that I am throwing an execption:

Line 1: Incorrect syntax near 'GO'.
'CREATE PROCEDURE' must be the first statement in a query batch.

If I run the same script directly in Query Analyzer, the script runs without error.

If anyone could explain to me what I am doing wrong, I would be most appreciative.

Thanks in advance for any help.

/dave

 

[oppcos]

My understanding of ExecuteNonQuery is that you should not execute more than one command per call. In other words, where Query Analyzer knows that GO means to execute the last command and get ready for the next one, you need to parse the commands out between GOs yourself. One easy way to do that, if you have read the command in as a string, is to use C#'s string split command.

string[] parsed, query = @"IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[foo]') AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
BEGIN
   DROP PROCEDURE [dbo].[foo]
END
GO
CREATE PROCEDURE foo
...
"

...

/* Parse out GO\n from the SQL commands by replacing them with an unlikly to be used character. */
parsed = (query.Replace("GO\n", "`")).Split('`'); 
foreach(int i in parsed) // ...

I didn't try compiling that, so watch for typos and such, but hope that helps.