如何使用脚本对c#中的SQL数据库执行DDL ?
本文关键字:数据库 执行 DDL SQL 中的 何使用 脚本 | 更新日期: 2023-09-27 18:05:43
我以前使用过下面的脚本,运行良好:
public const string application =
@"SET IDENTITY_INSERT Application ON
INSERT INTO Application(ApplicationId, Name, ModifiedDate) VALUES (1, 'x', getdate() );
INSERT INTO Application(ApplicationId, Name, ModifiedDate) VALUES (2, 'y', getdate() );
INSERT INTO Application(ApplicationId, Name, ModifiedDate) VALUES (3, 'z', getdate() );
SET IDENTITY_INSERT Application OFF
DBCC CHECKIDENT( 'Application', RESEED )";
现在我想执行DDL,但我得到一个错误消息:
public const string dropCreate =
@"
GO
ALTER TABLE [dbo].[TestAccount] DROP CONSTRAINT [TestAccount_Application]
GO
DROP TABLE [dbo].[TestAccount]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TestAccount](
[TestAccountId] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](35) NOT NULL,
[ApplicationId] [int] NOT NULL,
[RowVersion] [timestamp] NOT NULL,
[ModifiedDate] [datetime] NOT NULL,
PRIMARY KEY CLUSTERED
(
[TestAccountId] 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
ALTER TABLE [dbo].[TestAccount] WITH CHECK ADD CONSTRAINT [TestAccount_Application] FOREIGN KEY([ApplicationId])
REFERENCES [dbo].[Application] ([ApplicationId])
ON DELETE CASCADE
GO
ALTER TABLE [dbo].[TestAccount] CHECK CONSTRAINT [TestAccount_Application]
GO";
}
HResult=-2146232060
Message=Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
Incorrect syntax near 'GO'.
我可以从c#执行DDL吗?我使用的代码看起来像这样:
context.Database.ExecuteSqlCommand(TestAccountSQL.dropCreate);
GO
不是SQL关键字;它是由SQL Server Management Studio解析的
你需要发送每批作为一个单独的ExecuteSql
调用,或使用SQL Server管理对象(SMO)。
请参考我的相关问题