如何使用SqlConnection执行SQL和注释和GO语句？ [英] How to execute SQL with comments and GO statements using SqlConnection?

问题描述

我似乎无法执行使用DbCommand对象创建数据库的SQL。我究竟做错了什么？这是我的代码：

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

以下是错误：

查询语法无效。
$'$'，第1行第2列。
说明：
执行过程中出现未处理的异常$ b当前Web请求。请查看
堆栈跟踪以获取有关
错误的详细信息以及它在
中的源代码。

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.

异常详细信息：
System.Data.EntitySqlException：
查询语法无效，near term
'/'第1行第2列。

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

这个SQL脚本从SQL Management Studio Express中执行得很好（事实上，应用程序生成了这个脚本！

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.

推荐答案

您需要使用SQL语句管理类而不是普通的SqlCommand。 此页向您显示如何做到。如果你试图自己解析SQL，那么总是会有边缘情况。例如，如果代码中的字符串包含带有前导和尾部回车的单词GO，该怎么办？

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?

添加以下引用：

• Microsoft.SqlServer.Smo


• Microsoft.SqlServer.ConnectionInfo


• Microsoft .SqlServer.Management.Sdk.Sfc （编辑：不需要此引用）

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

代码：

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

这篇关于如何使用SqlConnection执行SQL和注释和GO语句？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！