执行包含在C＃中陈述GO SQL批处理 [英] Executing SQL batch containing GO statements in C#

问题描述

我想建立其执行SQL语句在批处理与错误处理
（所以我不使用SMO）的程序。

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

问题该开始是不是SQL的组成部分，并使用.NET执行它以错误结束了陈述（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我GOOGLE了它，但有在那边没有解决）

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

推荐答案

的最简单的解决方案（和最健壮）是使用T-SQL分析器。好消息是，你不必把它写，只需添加引用：

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

• 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管理对象

添加引用：

SQL Server Management Objects

Add references to:

• Microsoft.SqlServer.Smo


• Microsoft.SqlServer.ConnectionInfo


• Microsoft.SqlServer.Management.Sdk .Sfc

• 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运行时数据库有一个小的SQL文件分析器。它不会处理复杂的案件，但例如意见的支持。

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

或者多simplier

Or much simplier

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

这篇关于执行包含在C＃中陈述GO SQL批处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！