GO语句炸毁SQL执行在.NET [英] GO statements blowing up sql execution in .NET

问题描述

我有一个非常简单的C＃命令解释程序的应用程序，执行由SQL Server的脚本架构和数据生成的SQL脚本。它吹起来的GO语句。错误消息：

I have a very simple C# command shell app that executes a sql script generated by SQL Server for scripting schema and data. It's blowing up on the "GO" statements. Error message:

附近有语法错误'GO'。

Incorrect syntax near 'GO'.

下面是完整的SQL脚本：

Here is the full sql script:

/****** Object:  Table [gym].[MembershipStatus]    Script Date: 9/3/2013 9:24:01 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [gym].[MembershipStatus](
    [MembershipStatusID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Name] [varchar](75) NOT NULL,
    [Description] [varchar](400) NOT NULL,
    [AllowCheckin] [bit] NOT NULL,
    [IncludeInCollections] [bit] NOT NULL,
    [ScheduleFutureInvoices] [bit] NOT NULL,
 CONSTRAINT [MembershipStatus_PK] PRIMARY KEY CLUSTERED 
(
    [MembershipStatusID] 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
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [gym].[MembershipStatus] ON 

INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (1, N'Active', N'Active', 1, 1, 1)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (2, N'Cancelled', N'Cancelled', 0, 1, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (3, N'Collection', N'Collection', 0, 0, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (4, N'Deleted', N'Deleted', 0, 0, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (5, N'Expired', N'Expired', 1, 1, 1)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (6, N'Freeze', N'Freeze', 0, 1, 0)
INSERT [gym].[MembershipStatus] ([MembershipStatusID], [Name], [Description], [AllowCheckin], [IncludeInCollections], [ScheduleFutureInvoices]) VALUES (7, N'Inactive', N'Inactive', 0, 1, 1)
SET IDENTITY_INSERT [gym].[MembershipStatus] OFF
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ('') FOR [Name]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ('') FOR [Description]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ((0)) FOR [AllowCheckin]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ((0)) FOR [IncludeInCollections]
GO
ALTER TABLE [gym].[MembershipStatus] ADD  DEFAULT ((0)) FOR [ScheduleFutureInvoices]
GO

我的code中的相关章节如下所示：

The relevant section of my code looks like this:

SqlCommand command = new SqlCommand(script, connection);
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();

任何想法？

推荐答案

正如其他人所提到的， GO 语句拆分的字符串。但要小心，你可能有文字在脚本中的其他部分GO。您可能也有空白之前或GO语句后，你可能对GO语句后还行注释。任何这将是在SSMS有效的，所以你可能想测试它。

As others mentioned, split your string by GO statements. But be careful, you may have the text "GO" in other parts of your script. You might also have whitespace before or after the GO statement, and you might have comments on the line after the GO statement also. Any of that would be valid in SSMS, so you may want to test for it.

下面的方法使用：

private static IEnumerable<string> SplitSqlStatements(string sqlScript)
{
    // Split by "GO" statements
    var statements = Regex.Split(
            sqlScript,
            @"^\s*GO\s* ($ | \-\- .*$)",
            RegexOptions.Multiline |
            RegexOptions.IgnorePatternWhitespace |
            RegexOptions.IgnoreCase);

    // Remove empties, trim, and return
    return statements
        .Where(x => !string.IsNullOrWhiteSpace(x))
        .Select(x => x.Trim(' ', '\r', '\n'));
}

这篇关于GO语句炸毁SQL执行在.NET的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！