SQL执行中的GO'GO附近的语法错误' [英] GO 'syntax error near GO' in SQL execution

问题描述

我遇到的问题与 GO语句爆炸一样.NET 中的sql执行，并尝试实施Matt Johnson提供的解决方案，并正在寻求帮助以使其正常工作。我已经在C＃工作了几个月，所以我还是很环保的。

I'm encountering the same issue as in GO statements blowing up sql execution in .NET and tried implementing solution offered by Matt Johnson and am asking for assistance in getting it to work. I've been working with C# for a couple months so I'm still very green.

1. 这怎么称呼？我尝试过

1. How is this called? I tried

var cmd = SplitSqlStatements(cmd);

但出现错误：

'无法将类型'System.Collections.Generic.IEnumerable'隐式转换为'string'

'Cannot implicitly convert type 'System.Collections.Generic.IEnumerable' to 'string'

我不太了解Regex.Split，但是我的GO字符串是 \r\nGO\r\n，所以我会更改 @ ^ \s * GO\的分割s *（$ | \-\-。* $） 到 @ ^ \r\nGO\r\n $ | \- \-。* $） ？我不知道 |是什么\-\-。* $ 确实。

代码：

private static IEnumerable<string> SplitSqlStatements(string sqlScript)
{
    // Split by "GO" statements
    var statements = Regex.Split(
        sqlScript,
        @"^\s*GO\s* ($ | \-\- .*$)",
        RegexOptions.Multiline | RegexOptions.IgnorePatternWhitespace | RegexOptions.IgnoreCase);

    // Remove empties, trim, and return
    return statements
        .Where(x => !string.IsNullOrWhiteSpace(x))
        .Select(x => x.Trim(' ', '\r', '\n'));
}

我的字符串示例：

string sqlHeader = " Use " + "R04_FDW  " + "\r\nGO\r\n" + "SET ANSI_NULLS ON" + "\r\nGO\r\n" + "SET QUOTED_IDENTIFIER ON" + "\r\nGO\r\n" + "SET ANSI_PADDING ON" + "\r\nGO\r\n";
string sqlName = "CREATE TABLE " + dwSchema + "." + dwTN + "  (" + '\n';
string sqlNonCluster = "CREATE UNIQUE NONCLUSTERED INDEX [ak_" + dwTN + "__Sta3n_PrimaryKeys]" + "  ON [" + dwSchema + "].[" + dwTN + "]" + '\n' + "(" + '\n';
SqlCommand cmd = new SqlCommand(sqlHeader + sqlName + sqlColumns + sqlFoot + sqlCluster + sqlNonCluster, vx130);

推荐答案

1. 您正在将相同的变量用于输入作为结果，因此
   可能无法工作，请使用 var cmd = SplitSqlStatements（sqlAll）
   或类似的方法。 $ c> var 声明一个新变量，其类型由用途推断为
   。


2. 如果您在这些行上从来没有SQL注释，则只需使用 ^ \s * GO\s * $
   作为您的模式。其余就是处理这些注释。


3. 否。@只是减少字符串文字转义的一种方法；它不会影响您使用字符串（文字或其他方式）的方式。


4. 如果需要，请交换它们，但除非您通过li到SQL的两个字符的 \n 序列没关系：它们只是空格。

1. You're reusing the same variable for input as for the result, so that can't possibly work. Use var cmd = SplitSqlStatements(sqlAll) or similar instead. var declares a new variable with a type inferred by usage.
2. If you never have SQL comments on those lines, just use "^\s*GO\s*$" as your pattern. The rest is to handle those comments.
3. No. @"" is just a way to reduce escaping in string literals; it doesn't affect the way you use strings (literals or otherwise).
4. Swap them if you want, but unless you're passing the literal two-character sequence \n to SQL it doesn't matter much: they're just whitespace.

这篇关于SQL执行中的GO'GO附近的语法错误'的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！