If you’ve ever tried to run a collection of SQL batches via the .NET DbCommand.ExecuteNonQuery method, you probably received an error similar to the following: “Incorrect Syntax near ‘GO’ …”.  DbCommand.ExecuteNonQuery is intended to execute a single SQL batch statement against a database.  The “GO” separator is a command that is used/understood by SQL Server Query Analyzer and Query Editor utilities to separate out SQL batches when ran.

There has been some workarounds that I’ve seen that involve regular expressions and string manipulation/splitting to break out the script with “GO” separators into a collection of SQL statements and run them each individually.  This can work, but you will run into situations where commented areas will contain the “GO” statement and other problems can/will arise.  Such situations would cause the workaround to fail.

The proper way to run scripts with the “GO” separator is to employ SQL Server Management Objects (SMOs).  Microsoft’s SQL Server 2008 Feature Pack contains links to download/install the SMO assemblies (.NET Framework object model) .  Developers can use SMOs to perform SQL Server management routines through their .NET application code.

Steps to Use SMOs:

1) Download and Install SMOs - Download the SQL Server Management Objects found on this page.  Run the installation package.  If you are running SQL Server 2005, you may need to search for the 2005 version as the link I gave is for 2008.

2) Add references - After the SMOs are installed,  we’ll need to add references to them in our project.  The SMO assemblies should appear under your .NET tab when adding references to your project.  If you can’t  find them there, the assemblies were installed to “C:\Program Files\Microsoft SQL Server\100\SDK\Assemblies” for me.  To run scripts with “GO” statement, we’ll need references to the following DLLs:
  a) Microsoft.SqlServer.ConnectionInfo.dll
  b) Microsoft.SqlServer.Management.Sdk.Sfc.dll
  c)  Microsoft.SqlServer.Smo.dll

3) Import the required namespaces and execute your SQL script with “GO” statements like below:

   1:  Imports System.Data.SqlClient

   2:  Imports Microsoft.SqlServer.Management.Smo

   3:  Imports Microsoft.SqlServer.Management.Common

   4:   

   5:  Class Window1

   6:   

   7:      Public Sub ExecuteSqlStatements(ByVal connectionString As String, ByVal sqlStatements As String)

   8:          If Not String.IsNullOrEmpty(sqlStatements) Then

   9:              Using mySqlConnection As SqlConnection = New SqlConnection(connectionString)

  10:                  Dim mySqlServer As Server = New Server(New ServerConnection(mySqlConnection))

  11:                  mySqlServer.ConnectionContext.ExecuteNonQuery(sqlStatements)

  12:              End Using

  13:          End If

  14:      End Sub

  15:   

  16:  End Class

SqlBatchExecNonQuery_Soln.zip (965.09 kb)