执行SQL代码
本文关键字:代码 SQL 执行 | 更新日期: 2023-09-27 18:02:16
我有脚本创建数据库和表(大约50个表)。当我复制粘贴脚本到SQL管理工作室它运行良好,但当我试图从我的应用程序运行,我得到一个SQL异常。
下面是我的代码:string enterRecordsScript = "my sql script";
SqlCommand enterRecords = new SqlCommand(enterRecordsScript, connection);
enterRecords.ExecuteNonQuery();
下面是例外:
A first chance exception of type 'System.Data.SqlClient.SqlException' occurred in System.Data.dll
Additional information: Incorrect syntax near 'GO'.
'CREATE VIEW' must be the first statement in a query batch.
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Incorrect syntax near 'GO'.
脚本如下:
CREATE DATABASE TEST3
GO
USE Test3;
CREATE TABLE [SAShoppingInvoice]
(
[Guid] uniqueidentifier PRIMARY KEY NOT NULL ROWGUIDCOL,
[Number] nvarchar(50),
[DateOfShopping] datetime,
[SubTotal] decimal(18,2) DEFAULT 0,
[PSubTotal] decimal(18,2) DEFAULT 0,
[Freight] decimal(18,2) DEFAULT 0,
[Total] decimal(18,2) DEFAULT 0,
[ATotal] decimal(18,2) DEFAULT 0,
[PriceLevel] nvarchar(20),
[SummaryTotal] decimal(18,2) DEFAULT 0,
);
CREATE TABLE [SAShoppingInvoiceDetail]
(
[Guid] uniqueidentifier PRIMARY KEY NOT NULL ROWGUIDCOL,
[Number] nvarchar(50),
[UnitPrice] decimal(18,2) DEFAULT 0,
[Quantity] decimal(18,2) DEFAULT 0,
[Description] nvarchar(100),
);
ALTER TABLE [SAShoppingInvoiceDetail]
ADD ShoppingInvoiceGuid uniqueidentifier
CONSTRAINT FK_SAShoppingInvoice_ShoppingInvoice_To_SAShoppingInvoiceDetail_ShoppingInvoice
FOREIGN KEY (ShoppingInvoiceGuid)
REFERENCES [SAShoppingInvoice](Guid);
GO
CREATE VIEW SAShoppingInvoice_SummaryTotal WITH SCHEMABINDING AS SELECT ShoppingInvoiceGuid, SUM(ISNULL(Quantity,0)*ISNULL(UnitPrice,0)) as SummaryTotal, COUNT_BIG(*) as CountBig
FROM dbo.SAShoppingInvoiceDetail Group By ShoppingInvoiceGuid;
GO
CREATE UNIQUE CLUSTERED INDEX idx_SAShoppingInvoiceSummaryTotal ON SAShoppingInvoice_SummaryTotal(ShoppingInvoiceGuid);
为什么从我的应用程序运行脚本是一个问题?
GO
不是有效的SQL。一些特定的工具(如SSMS)使用它将一个大文本文件拆分为多个单独的命令。在您自己的程序中,这将成为您的责任。
如果你有文本文件
<>前一个去b去c之前去那么如果您想使用SqlCommand
对象执行此操作,则需要三个命令,一个包含a
,一个包含b
,一个包含c
。这三个都不包括GO