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) NU…

Accepted Answer

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

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

Solution

Problem

Related problems