0

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. How is this called? I tried

    var cmd = SplitSqlStatements(cmd);

    but get an error:

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

  2. Don't fully understand Regex.Split but my GO string is "\r\nGO\r\n" so would I change that split from@"^\s*GO\s* ($ | \-\- .*$)" to @"^\r\nGO\r\n$ | \-\- .*$)" ? I have no idea what | \-\- .*$ does.

  3. My strings concatenate with + but I see @" at the beginning. Does this mean I need to get rid of the + concatenate?
  4. My strings have + '\n' + where I was trying to insert a newline, can these be replaced with System.Environment.Newline or should they be removed entirely?

Code:

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

Examples of my strings:

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);
Community
  • 1
  • 1
Thom Ash
  • 221
  • 4
  • 20
  • Once the string is split you need to execute each statement in a foreach loop? Do you have the code of where this happens? – Hylaean Dec 04 '14 at 18:30
  • If you're building the strings by hand why are you bothering to include `GO` statements only to remove them? – Preston Guillot Dec 04 '14 at 18:37
  • Just FYI, none of this SQL requires a "GO" to separate the batches. It can all be run as a single `SqlCommand`. As in from the "USE" to the end. Unless you are also creating procs, functions, or views, this is way over-complicating a single exec. – Solomon Rutzky Dec 04 '14 at 19:15
  • srutzky I suppose that's true but there are other commands like CTE that won't execute properly if they aren't first in batch. but you're right, I'm not doing any of that. Rather than experiment with what I can or can't do I followed the SQL script format. This may be a good time to revisit habit. – Thom Ash Dec 04 '14 at 20:07

1 Answers1

1
  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.
Nathan Tuggy
  • 2,237
  • 27
  • 30
  • 38
  • Thanks, your pointers got me going. One final question. The link cautioned about other GO in the script. It is possible the characters GO might occur in a column name. How can I prevent them being removed? – Thom Ash Dec 04 '14 at 20:08
  • @ThomAsh: the ^ and $ in the regex anchor to the beginning and end of a line respectively, so unless you have a column named GO all on its own line with no comma or anything on either side, that won't be a problem. – Nathan Tuggy Dec 04 '14 at 20:20