Run a SQL batch containing "GO" in C#

Recently I answered a question about Stack Overflow. The question is how to execute a SQL batch containing "GO" in C#? For example, how to run this batch:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Company_LoadAll]
AS
SET NOCOUNT ON
SELECT  [Company].[Company_Id]
FROM [Company]
RETURN
GO

When running this script through SQL Server Management Studio, there is no problem. However since a SqlCommand, the exception System.Data.SqlClient.SqlException with the message "Incorrect syntax near 'GO'" is started.

The idea is to split the script with each "GO" and execute the commands one by one. It sounds simple (string.Split) but the SQL language is complex and there are many cases to manage. For example, multi-line comments may contain "GO" that are not command separators:

/*
GO
*/

#ScriptDom

Writing a complete T-SQL parser is a very tedious task, but Microsoft provides one 😃. After referencing Microsoft.SqlServer.TransactSQL.ScriptDom, you can use the following code:

using (TextReader reader = new StringReader(script))
{
    TSqlParser parser = new TSql110Parser(true);
    IList<ParseError> errors;
    TSqlFragment fragment = parser.Parse(reader, out errors);
    if (errors != null && errors.Count > 0)
    {
        foreach (ParseError error in errors)
        {
            Console.WriteLine("Line: {0}, Column: {1}: {2}", error.Line, error.Column, error.Message);
            return;
        }
    }

    SqlScriptGenerator sqlScriptGenerator = new Sql110ScriptGenerator();
    TSqlScript sqlScript = fragment as TSqlScript;
    if (sqlScript == null)
    {
        sqlScriptGenerator.GenerateScript(fragment, Console.Out); // TODO execute the script
    }
    else
    {
        foreach (var sqlBatch in sqlScript.Batches)
        {
            Console.WriteLine("-- ");
            sqlScriptGenerator.GenerateScript(sqlBatch, Console.Out); // TODO execute the script
        }
    }
}

Note that for other needs it is possible to browse the content of the script using a visitor (TSqlFragmentVisitor). The only annoying point with this method is that comments are deleted.

#CodeFluent.Runtime.Database

CodeFluent.Runtime.Database also allows you to run this kind of script. All weird cases are not taken into account but in general, it works well:

using (StatementReader statementReader = new CodeFluent.Runtime.Database.Management.StatementReader("GO", Environment.NewLine, inputStream))
{
    Statement statement;
    while ((statement = statementReader.Read(StatementReaderOptions.Default)) != null)
    {
        Console.WriteLine("-- ");
        Console.WriteLine(statement.Command);
    }
}

The code is short and not specific to SQL Server. It is quite possible to use a separator other than "GO". This requires replacing the first parameter of the constructor of "StatementReader".

#SQL Server Management Objects

One last way is to use SQL Server Management Objects (SMO). First, the following references must be added:

• Microsoft.SqlServer.Smo
• Microsoft.SqlServer.ConnectionInfo
• Microsoft.SqlServer.Management.Sdk.Sfc

Then these few lines are enough:

using (SqlConnection connection = new SqlConnection("Server=(local);Database=Sample;Trusted_Connection=True;"))
{
    ServerConnection svrConnection = new ServerConnection(connection);
    Server server = new Server(svrConnection);
    server.ConnectionContext.ExecuteNonQuery(script);
}

Do you have a question or a suggestion about this post? Contact me!