How to execute SQL with comments and GO statements using SqlConnection?

Question

I can't seem to execute SQL that creates a database using a DbCommand object. What am I doing wrong? Here's my code:

DbConnection connection; // initialized and opened elsewhere
DbCommand cmd = connection.CreateCommand();
cmd.CommandText = sql;
cmd.ExecuteNonQuery();

Here's the error:

The query syntax is not valid., near term '/', line 1, column 2. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.EntitySqlException: The query syntax is not valid., near term '/', line 1, column 2.

Here's the first part of the file. The exception is thrown regarding just the comments on the first line:

/****** Object:  Table [dbo].[User]    Script Date: 10/08/2009 12:14:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [FirstName] [nvarchar](50) NULL,
    [LastName] [nvarchar](50) NULL,
    [EmailAddress] [nvarchar](100) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

This same SQL script executes just fine from SQL Management Studio Express (in fact that app generated this script!). It's just Visual Studio's own Server Explorer query view and from my own code that seems to fail.

Answer 1

You need to use the SQL management classes instead of the normal SqlCommand. This page shows you how to do it. If you try to parse the SQL yourself then there will always be edge cases that you miss. For example, what if a string within the code contains the word "GO" with leading and trailing carriage returns?

Add these references:

• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.ConnectionInfo
• Microsoft.SqlServer.Management.Sdk.Sfc (Edit: This reference isn't needed)

Then you can use this code:

string connectionString, scriptText;
SqlConnection sqlConnection = new SqlConnection(connectionString);
ServerConnection svrConnection = new ServerConnection(sqlConnection);
Server server = new Server(svrConnection);
server.ConnectionContext.ExecuteNonQuery(scriptText);

Answer 2

Here is a code snippet that I posted on my blog some time ago that may solve this problem:

private static void RunScript(SqlConnection connection, string script)
{
    Regex regex = new Regex(@"\r{0,1}\nGO\r{0,1}\n");
    string[] commands = regex.Split(script);

    for (int i = 0; i < commands.Length; i++)
    {
        if (commands[i] != string.Empty)
        {
            using(SqlCommand command = new SqlCommand(commands[i], connection))
            {
                command.ExecuteNonQuery();
                command.Dispose();
            }
        }
    }
}

It splits the SQL script into separate commands and executes each of them. I regularly use this to set up test databases with generated SQL scripts.