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

问题描述

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

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的执行过程中发生未处理的异常
$ 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：在
查询语法是无效的，短期内
'/'，第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管理Studio Express的就好了（其实该应用程序生成的这个脚本！）。这只是Visual Studio的自己的服务器资源管理器的查询视图，然后从我自己的代码，似乎要失败的。

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


• 的微软.SqlServer.Management.Sdk.Sfc 的（编辑：不需要此参考）

• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.ConnectionInfo
• Microsoft.SqlServer.Management.Sdk.Sfc ( 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);

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