GO statements blowing up sql execution in .NET

Question

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:

Incorrect syntax near 'GO'.

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 

The relevant section of my code looks like this:

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

Any ideas?

Answer 1

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.

Here is the method I use:

private static IEnumerable<string> SplitSqlStatements(string sqlScript) {     // Make line endings standard to match RegexOptions.Multiline     sqlScript = Regex.Replace(sqlScript, @"(\r\n|\n\r|\n|\r)", "\n");      // Split by "GO" statements     var statements = Regex.Split(             sqlScript,             @"^[\t ]*GO[\t ]*\d*[\t ]*(?:--.*)?$",             RegexOptions.Multiline |             RegexOptions.IgnorePatternWhitespace |             RegexOptions.IgnoreCase);      // Remove empties, trim, and return     return statements         .Where(x => !string.IsNullOrWhiteSpace(x))         .Select(x => x.Trim(' ', '\n')); } 

Answer 2

If you want to be able to use GO you will need to reference to the following dlls

Microsoft.SqlServer.ConnectionInfo.dll
Microsoft.SqlServer.Management.Sdk.Sfc.dll
Microsoft.SqlServer.Smo.dll Microsoft.SqlServer.SqlEnum.dll

Then execute like so

 using (SqlConnection conn = new SqlConnection(connection))  {      Server db = new Server(new ServerConnection(conn));      string script = File.ReadAllText(scriptPath);      db.ConnectionContext.ExecuteNonQuery(script);        }