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

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  = O开发者_JS百科FF, 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.

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);

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.

I find it strange that you're getting an EntitySqlException ...

Another solution that you can choose, is to execute this script via the osql command-line tool. You could create a System.Diagnostics.Process instance, and use this process to call osql which in turn executes the script.

System.Diagnostics.Process p = new System.Diagnostics.Process ();

p.StartInfo.FileName = Environment.GetEnvironmentVariable ("COMSPEC");
p.StartInfo.UseShellExecute = false;
p.StartInfo.ErrorDialog = false;

p.StartInfo.CreateNoWindow = true;
p.StartInfo.RedirectStandardInput = true;
p.StartInfo.RedirectStandardError = true;
p.StartInfo.RedirectStandardOutput = true;

p.Start ();

p.StandardInput.WriteLine ("echo off");
string command = @"osql -U -b -e -S " + servername + " -d " + databasename + " -i \'" + filename + "\'";
p.StandardInput.WriteLine (command);
p.StandardInput.WriteLine ("exit");

p.WaitForExit ();

in SQL Server you can concatenate as many queries as you want with a simple space separator but for that you need to remove the "GO"s.
example :

BEGIN TRANSACTION   SET QUOTED_IDENTIFIER ON  SET ARITHABORT ON  SET NUMERIC_ROUNDABORT OFF   SET CONCAT_NULL_YIELDS_NULL ON   SET ANSI_NULLS ON   SET ANSI_PADDING ON   SET ANSI_WARNINGS ON   COMMIT   BEGIN TRANSACTION   $remove this GO here$   CREATE TABLE dbo.Tmp_Tralala(   ERRID numeric (18,0) NOT NULL) ) ON [PRIMARY]   $remove this GO here$   IF EXISTS(SELECT * FROM dbo.Tralala)   EXEC('INSERT INTO ..etc