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