C# · 12月 27, 2021

c# – 如何使用SqlConnection执行SQL注释和GO语句?

我似乎无法执行使用DbCommand对象创建数据库的sql.我究竟做错了什么?这是我的代码: DbConnection connection; // initialized and opened elsewhereDbCommand cmd = connection.CreateCommand();cmd.CommandText = sql;cmd.ExecuteNonQuery();

这是错误:

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
‘/’,column 2.

这是文件的第一部分.关于第一行的注释,抛出了异常:

/****** Object: Table [dbo].[User] Script Date: 10/08/2009 12:14:29 ******/SET ANSI_NULLS ONGOSET QUOTED_IDENTIFIER ONGOCREATE 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执行得很好(实际上该应用程序生成了这个脚本!).它只是Visual Studio自己的服务器资源管理器查询视图和我自己的代码似乎失败.

解决方法 您需要使用sql管理类而不是正常的sqlCommand. This page shows you how to do it.如果你尝试自己解析sql,那么总是会有你错过的边缘情况.例如,如果代码中的字符串包含带有前导和后面回车的单词“GO”呢?

添加这些参考:

> Microsoft.sqlServer.Smo
> Microsoft.sqlServer.ConnectionInfo
> Microsoft.sqlServer.Management.Sdk.Sfc(编辑:不需要此引用)

那么你可以使用这段代码:

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