执行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);

为什么从我的应用程序运行脚本是一个问题?

执行SQL代码

GO不是有效的SQL。一些特定的工具(如SSMS)使用它将一个大文本文件拆分为多个单独的命令。在您自己的程序中,这将成为您的责任。

如果你有文本文件

<>前一个去b去c之前去

那么如果您想使用SqlCommand对象执行此操作,则需要三个命令,一个包含a,一个包含b,一个包含c。这三个都不包括GO