Executing an SQL package containing GO statements in C #

Question

I am trying to create a program that executes sql commands in a package with error handling (therefore, I do not use SMO).

the problem is that GO is not part of SQL and when using .NET to execute statements, it fails (SMO processes it, but does not give any indication of whether execution has completed).

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

using the lines above does not solve my problem due to the fact that the GO keyword may also appear in comments (I do not want to remove comments from statements) and comments may appear inside / ** / or after two dashes -
for example, I I would not want the following code to be processed:

/*
GO
*/

(ofc google, but there was no solution)

Answer 1

ScriptDom

( ) - T-SQL. , , :

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

:

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

:

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

:

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

CodeFluent Runtime Database sql. , , , .

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

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

Answer 2

, "GO" , :

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

Answer 3

, , , SSMS . sql, . SSMS SMO , .

, , . , /* */... , , -- \n|\r\n

( , ):

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"]

, , - . , , /* */, STRING insert. ...