GO statements blowing up sql execution in .NET

by

As others mentioned, split your string by GO statements. But be careful, you may have the text "GO" in other parts of your script. You might also have whitespace before or after the GO statement, and you might have comments on the line after the GO statement also. Any of that would be valid in SSMS, so you may want to test for it.

Here is the method I use:

private static IEnumerable<string> SplitSqlStatements(string sqlScript)
{
    // Make line endings standard to match RegexOptions.Multiline
    sqlScript = Regex.Replace(sqlScript, @"(\r\n|\n\r|\n|\r)", "\n");

    // Split by "GO" statements
    var statements = Regex.Split(
            sqlScript,
            @"^[\t ]*GO[\t ]*\d*[\t ]*(?:--.*)?$",
            RegexOptions.Multiline |
            RegexOptions.IgnorePatternWhitespace |
            RegexOptions.IgnoreCase);

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

More Related Contents:

  1. ExecuteReader requires an open and available Connection. The connection’s current state is Connecting
  2. C# Equivalent of SQL Server DataTypes
  3. How to directly execute SQL query in C#?
  4. How to deploy application with sql server database on clients
  5. How to execute an SSIS package from .NET?
  6. SqlConnection SqlCommand SqlDataReader IDisposable
  7. Bulk Update in C#
  8. How to catch SQLServer timeout exceptions
  9. Code to validate SQL Scripts
  10. Queuing in OneWay WCF Messages using Windows Service and SQL Server
  11. MultipleActiveResultSets=True or multiple connections?
  12. EF 6 – How to correctly perform parallel queries
  13. SqlConnection.Close() inside using statement
  14. How to keep single SQL Server connection instance open for multiple request in C#?
  15. Received an invalid column length from the bcp client for colid 6
  16. Failed to update .mdf database because the database is read-only (Windows application)
  17. Horrible performance using SqlCommand Async methods with large data
  18. Is it better to execute many sql commands with one connection, or reconnect every time?
  19. Is there a .NET equivalent to SQL Server’s newsequentialid()
  20. Know when to retry or fail when calling SQL Server from C#?
  21. How to Specify Primary Key Name in EF-Code-First
  22. Upload CSV file to SQL server
  23. Unable to load SqlServerSpatial.dll
  24. What is the equivalent of bigint in C#?
  25. “The operation is not valid for the state of the transaction” error and transaction scope
  26. Use SQL Server time datatype in C#.NET application?
  27. Should the order of LINQ query clauses affect Entity Framework performance?
  28. How do I connect to a SQL database from C#?
  29. Generating sql code programmatically
  30. Is SqlConnection.Close() need inside a Using statement?

Leave a Comment