Executing SQL batch containing GO statements in C#

Question

I am trying to build a program which execute sql statements in batch with error handling (therefore I am not using SMO).

the problem is that GO is not a part of SQL and when using .NET to execute the statements it ends up with an error (SMO handles it but does not give any indication whether the execution failed).

string statements = File.ReadAllText("c:\\test.sql");
string[] splitted = statements.split("GO");

using the above lines do not solve my problem due to the fact that the GO keyword can also come inside a comment (I don't want to remove comments from the statements) and comments can come inside /**/ or after the two dashes --
for example I wouldn't like the following code to be parsed:

/*
GO
*/

(ofc i googled it but there was no solution over there)

Answer 1

ScriptDom

The easiest solution (and the most robust) is to use a T-SQL parser. The good news is that you don't have to write it, just add reference to:

• Microsoft.Data.Schema.ScriptDom
• Microsoft.Data.Schema.ScriptDom.Sql

Then use the code:

static void Main(string[] args)
{
    string sql = @"
/* 
GO
*/ 
SELECT * FROM [table]

GO

SELECT * FROM [table]
SELECT * FROM [table]

GO

SELECT * FROM [table]";

    string[] errors;
    var scriptFragment = Parse(sql, SqlVersion.Sql100, true, out errors);
    if (errors != null)
    {
        foreach (string error in errors)
        {
            Console.WriteLine(error);
            return;
        }
    }

    TSqlScript tsqlScriptFragment = scriptFragment as TSqlScript;
    if (tsqlScriptFragment == null)
        return;

    var options = new SqlScriptGeneratorOptions { SqlVersion = SqlVersion.Sql100, KeywordCasing = KeywordCasing.PascalCase };

    foreach (TSqlBatch batch in tsqlScriptFragment.Batches)
    {
        Console.WriteLine("--");
        string batchText = ToScript(batch, options);
        Console.WriteLine(batchText);                
    }
}

public static TSqlParser GetParser(SqlVersion level, bool quotedIdentifiers)
{
    switch (level)
    {
        case SqlVersion.Sql80:
            return new TSql80Parser(quotedIdentifiers);
        case SqlVersion.Sql90:
            return new TSql90Parser(quotedIdentifiers);
        case SqlVersion.Sql100:
            return new TSql100Parser(quotedIdentifiers);
        case SqlVersion.SqlAzure:
            return new TSqlAzureParser(quotedIdentifiers);
        default:
            throw new ArgumentOutOfRangeException("level");
    }
}

public static IScriptFragment Parse(string sql, SqlVersion level, bool quotedIndentifiers, out string[] errors)
{
    errors = null;
    if (string.IsNullOrWhiteSpace(sql)) return null;
    sql = sql.Trim();
    IScriptFragment scriptFragment;
    IList<ParseError> errorlist;
    using (var sr = new StringReader(sql))
    {
        scriptFragment = GetParser(level, quotedIndentifiers).Parse(sr, out errorlist);
    }
    if (errorlist != null && errorlist.Count > 0)
    {
        errors = errorlist.Select(e => string.Format("Column {0}, Identifier {1}, Line {2}, Offset {3}",
                                                        e.Column, e.Identifier, e.Line, e.Offset) +
                                            Environment.NewLine + e.Message).ToArray();
        return null;
    }
    return scriptFragment;
}

public static SqlScriptGenerator GetScripter(SqlScriptGeneratorOptions options)
{
    if (options == null) return null;
    SqlScriptGenerator generator;
    switch (options.SqlVersion)
    {
        case SqlVersion.Sql80:
            generator = new Sql80ScriptGenerator(options);
            break;
        case SqlVersion.Sql90:
            generator = new Sql90ScriptGenerator(options);
            break;
        case SqlVersion.Sql100:
            generator = new Sql100ScriptGenerator(options);
            break;
        case SqlVersion.SqlAzure:
            generator = new SqlAzureScriptGenerator(options);
            break;
        default:
            throw new ArgumentOutOfRangeException();
    }
    return generator;
}

public static string ToScript(IScriptFragment scriptFragment, SqlScriptGeneratorOptions options)
{
    var scripter = GetScripter(options);
    if (scripter == null) return string.Empty;
    string script;
    scripter.GenerateScript(scriptFragment, out script);
    return script;
}

SQL Server Management Objects

Add references to:

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

You can then use this code:

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);
}

CodeFluent Runtime

CodeFluent Runtime Database has a small sql file parser. It does not handle complex cases but for instance comments are supported.

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);
    }
}

Or much simplier

new CodeFluent.Runtime.Database.Management.SqlServer.Database("connection string")
      .RunScript("path", StatementReaderOptions.Default);

Answer 2

ScriptDom (updated 2023)

The edit queue for the answer by @meziantou is full (and at a size of 500 suggestions it doesn't seem that anybody with edit priviledge is ever going to go through it), so I'm adding this updated answer as a community wiki. ScriptDom now lives at nuget, github

ScriptDom is a T-SQL parser with support for batch files. Just add a reference to the nuget package Microsoft.SqlServer.TransactSql.ScriptDom. Example:

using Microsoft.SqlServer.TransactSql.ScriptDom;

namespace ScriptDomTest;

internal class Program
{
    static void Main(string[] args)
    {
        string sql = @"
    /* 
    GO
    */ 
    SELECT * FROM [table]

    GO

    SELECT * FROM [table]
    SELECT * FROM [table]

    GO

    SELECT * FROM [table]";

        string[]? errors;
        var scriptFragment = Parse(sql, SqlVersion.Sql100, true, out errors);
        if (errors != null)
        {
            foreach (string error in errors)
            {
                Console.WriteLine(error);
                return;
            }
        }

        TSqlScript? tsqlScriptFragment = scriptFragment as TSqlScript;
        if (tsqlScriptFragment == null)
            return;

        var options = new SqlScriptGeneratorOptions { SqlVersion = SqlVersion.Sql100, KeywordCasing = KeywordCasing.PascalCase };

        foreach (TSqlBatch batch in tsqlScriptFragment.Batches)
        {
            Console.WriteLine("--");
            string batchText = ToScript(batch, options);
            Console.WriteLine(batchText);                
        }
    }

    public static TSqlParser GetParser(SqlVersion level, bool quotedIdentifiers)
    {
        return level switch
        {
            SqlVersion.Sql80 => new TSql80Parser(quotedIdentifiers),
            SqlVersion.Sql90 => new TSql90Parser(quotedIdentifiers),
            SqlVersion.Sql100 => new TSql100Parser(quotedIdentifiers),
            SqlVersion.Sql110 => new TSql110Parser(quotedIdentifiers),
            SqlVersion.Sql120 => new TSql120Parser(quotedIdentifiers),
            SqlVersion.Sql130 => new TSql130Parser(quotedIdentifiers),
            SqlVersion.Sql140 => new TSql140Parser(quotedIdentifiers),
            SqlVersion.Sql150 => new TSql150Parser(quotedIdentifiers),
            SqlVersion.Sql160 => new TSql160Parser(quotedIdentifiers),
            _ => throw new ArgumentOutOfRangeException(nameof(level)),
        };
    }

    public static TSqlFragment? Parse(string sql, SqlVersion level, bool quotedIndentifiers, out string[]? errors)
    {
        errors = null;
        if (string.IsNullOrWhiteSpace(sql)) return null;
        sql = sql.Trim();
        TSqlFragment scriptFragment;
        IList<ParseError> errorlist;
        using (var sr = new StringReader(sql))
        {
            scriptFragment = GetParser(level, quotedIndentifiers).Parse(sr, out errorlist);
        }
        if (errorlist != null && errorlist.Count > 0)
        {
            errors = errorlist.Select(e => string.Format("Column {0}, Number {1}, Line {2}, Offset {3}",
                                                            e.Column, e.Number, e.Line, e.Offset) +
                                                Environment.NewLine + e.Message).ToArray();
            return null;
        }
        return scriptFragment;
    }

    public static SqlScriptGenerator? GetScripter(SqlScriptGeneratorOptions options)
    {
        if (options == null) return null;
        return options.SqlVersion switch
        {
            SqlVersion.Sql80 => new Sql80ScriptGenerator(options),
            SqlVersion.Sql90 => new Sql90ScriptGenerator(options),
            SqlVersion.Sql100 => new Sql100ScriptGenerator(options),
            SqlVersion.Sql110 => new Sql110ScriptGenerator(options),
            SqlVersion.Sql120 => new Sql120ScriptGenerator(options),
            SqlVersion.Sql130 => new Sql130ScriptGenerator(options),
            SqlVersion.Sql140 => new Sql140ScriptGenerator(options),
            SqlVersion.Sql150 => new Sql150ScriptGenerator(options),
            SqlVersion.Sql160 => new Sql160ScriptGenerator(options),
            _ => throw new ArgumentOutOfRangeException(nameof(options)),
        };
    }

    public static string ToScript(TSqlFragment scriptFragment, SqlScriptGeneratorOptions options)
    {
        var scripter = GetScripter(options);
        if (scripter == null) return string.Empty;
        string script;
        scripter.GenerateScript(scriptFragment, out script);
        return script;
    }
}

Answer 3

Yes, go is something SSMS has to allow you to break things up. It isn't part of sql, as you mentioned. SSMS uses SMO to do its work, so thats why it works there.

As your comment makes clear, but the question is muddied, you would need to remove all comment blocks before processing. If you don't want to do that, you would need to process the file as a stream and start ignore at /* and stop at */... and probably also -- and \n|\r\n

You can also uses a regex to split split it up (if you read it in as a text blob, not already broken up by lines):

var text = File.ReadAllText("file.txt")
var cleanedText = Regex.Replace(text, @"/\*.*\*/", "", RegexOptions.Singleline)
var parts = Regex.Split(cleanedText, @"^\s*GO.*$", RegexOptions.Multiline);
for(var part in parts) {
    executeBatch(part);
} 

// but this is getting ugly

var str = "what /*\n the \n\n GO \n*/heck\nGO\nand then";
var cleanedText = Regex.Replace(str, @"/\*.*\*/", "\n", RegexOptions.Singleline)
var split = Regex.Split(cleanedText, @"^\s*GO.*$", RegexOptions.Multiline);
// == ["what\nheck", "\nand then"]

And yes, as the comment says, your real answer is to write a parser. Even with what you say about comments, you can still have /* and */ embedded inside a STRING inside an insert. so...

Answer 4

Split only when "GO" stands on a lonely line or with whitespaces, like this:

Regex.Split(statements, @"^\s+GO\s+$");