Execute a large SQL script with GO commands

Question

I need to execute a large set of SQL statements  creating a bunch of tables  views and stored procedures  from within a C  program   These statements need to be separated by GO statements  but SqlCommand ExecuteNonQuery   does not like GO statements  My solution  which I suppose I ll post for reference  was to split the SQL string on GO lines  and execute each batch separately   Is there an easier better way

User · Answer

For anyone still having the problem  You could use official Microsoft SMO   https   docs microsoft com en-us sql relational-databases server-management-objects-smo overview-smo view sql-server-2017  using  var connection   new SqlConnection connectionString       var server   new Server new ServerConnection connection      server ConnectionContext ExecuteNonQuery sql

User · Answer

I accomplished this today by loading my SQL from a text file into one string  I then used the string Split function to separate the string into individual commands which were then sent to the server individually   Simples     Just realised that you need to split on  nGO just in case the letters GO appear in any of your table names etc   Guess I was lucky there

User · Answer

I had the same problem in java and I solved it with a bit of logic and regex  I believe the same logic can be applied First I read from the slq file into memory  Then I apply the following logic  It s pretty much what has been said before however I believe that using regex word bound is safer than expecting a new line char   String pattern      bGO  b   bgo  b    String   splitedSql   sql split pattern   for  String chunk   splitedSql      getJdbcTemplate   update chunk       This basically splits the sql string into an array of sql strings  The regex is basically to detect full  go  words either lower case or upper case  Then you execute the different querys sequentially

User · Answer

If you don t want to install SMO objects you can use gplex tool  see this answer

User · Answer

Use SQL Server Management Objects  SMO  which understands GO separators  See my blog post here  http   weblogs asp net jongalloway Handling- 2200 GO 2200 -Separators-in-SQL-Scripts-2D00-the-easy-way  Sample code   public static void Main                   string scriptDirectory    c   temp  sqltest       string sqlConnectionString    Integrated Security SSPI        Persist Security Info True Initial Catalog Northwind Data Source  local      DirectoryInfo di   new DirectoryInfo scriptDirectory     FileInfo   rgFiles   di GetFiles    sql      foreach  FileInfo fi in rgFiles              FileInfo fileInfo   new FileInfo fi FullName           string script   fileInfo OpenText   ReadToEnd            using  SqlConnection connection   new SqlConnection sqlConnectionString                         Server server   new Server new ServerConnection connection                server ConnectionContext ExecuteNonQuery script                      If that won t work for you  see Phil Haack s library which handles that  http   haacked com archive 2007 11 04 a-library-for-executing-sql-scripts-with-go-separators-and aspx

User · Answer

The  GO  batch separator keyword is actually used by SQL Management Studio itself  so that it knows where to terminate the batches it is sending to the server  and it is not passed to SQL server  You can even change the keyword in Management Studio  should you so desire

User · Answer

If you don t want to go the SMO route you can search and replace  GO  for     and the query as you would  Note that soly the the last result set will be returned

User · Answer

I also faced the same problem  and I could not find any other way but splitting the single SQL operation in separate files  then executing all of them in sequence   Obviously the problem is not with lists of DML commands  they can be executed without GO in between  different story with DDL  create  alter  drop

User · Answer

This is what I knocked together to solve my immediate problem   private void ExecuteBatchNonQuery string sql  SqlConnection conn        string sqlBatch   string Empty      SqlCommand cmd   new SqlCommand string Empty  conn       conn Open        sql      nGO        make sure last batch is executed      try           foreach  string line in sql Split new string 2      n     r     StringSplitOptions RemoveEmptyEntries                 if  line ToUpperInvariant   Trim       GO                     cmd CommandText   sqlBatch                  cmd ExecuteNonQuery                    sqlBatch   string Empty                else                   sqlBatch    line     n                                             finally           conn Close              It requires GO commands to be on their own line  and will not detect block-comments  so this sort of thing will get split  and cause an error   ExecuteBatchNonQuery               GO          conn

User · Answer

I hit this same issue and eventually just solved it by a simple string replace  replacing the word GO with a semi-colon      All seems to be working fine while executing scripts with in-line comments  block comments  and GO commands  public static bool ExecuteExternalScript string filePath        using  StreamReader file   new StreamReader filePath       using  SqlConnection conn   new SqlConnection dbConnStr                 StringBuilder sql   new StringBuilder             string line          while   line   file ReadLine       null                           replace GO with semi-colon             if  line     GO                   sql Append                      remove inline comments             else if  line IndexOf  --    gt  -1                  sql AppendFormat    0     line Split new string      --     StringSplitOptions None  0                   just the line as it is             else                 sql AppendFormat    0     line                     conn Open             SqlCommand cmd   new SqlCommand sql ToString    conn           cmd ExecuteNonQuery               return true

User · Answer

Based on Blorgbeard s solution   foreach  var sqlBatch in commandText Split new      GO     StringSplitOptions RemoveEmptyEntries        sqlCommand CommandText   sqlBatch     sqlCommand ExecuteNonQuery

User · Answer

I look at this a few times at the end decided with EF implementation A bit modified for SqlConnection  public static void ExecuteSqlScript this SqlConnection sqlConnection  string sqlBatch                           Handle backslash utility statement  see http   technet microsoft com en-us library dd207007 aspx              sqlBatch   Regex Replace sqlBatch        r n  r  n    string Empty                   Handle batch splitting utility statement  see http   technet microsoft com en-us library ms188037 aspx              var batches   Regex Split                  sqlBatch                  string Format CultureInfo InvariantCulture      s   0    t   0-9    0      s       BatchTerminator                   RegexOptions IgnoreCase   RegexOptions Multiline                for  int i   0  i  lt  batches Length    i                                   Skip batches that merely contain the batch terminator                 if  batches i  StartsWith BatchTerminator  StringComparison OrdinalIgnoreCase                          i    batches Length - 1  amp  amp  string IsNullOrWhiteSpace batches i                                           continue                                        Include batch terminator if the next element is a batch terminator                 if  batches Length  gt  i   1  amp  amp                      batches i   1  StartsWith BatchTerminator  StringComparison OrdinalIgnoreCase                                         int repeatCount   1                          Handle count parameter on the batch splitting utility statement                     if   string Equals batches i   1   BatchTerminator  StringComparison OrdinalIgnoreCase                                                 repeatCount   int Parse Regex Match batches i   1       0-9      Value  CultureInfo InvariantCulture                                              for  int j   0  j  lt  repeatCount    j                                               var command   sqlConnection CreateCommand                           command CommandText   batches i                          command ExecuteNonQuery                                                            else                                       var command   sqlConnection CreateCommand                        command CommandText   batches i                       command ExecuteNonQuery

User · Answer

If you don t want to use SMO  for example because you need to be cross-platform  you can also use the ScriptSplitter class from SubText   Here s the implementation in C   amp  VB NET   Usage       string strSQL      SELECT   FROM INFORMATION SCHEMA columns GO SELECT   FROM INFORMATION SCHEMA views         foreach string Script in new Subtext Scripting ScriptSplitter strSQL                  Console WriteLine Script           If you have problems with multiline c-style comments  remove the comments with regex   static string RemoveCstyleComments string strInput        string strPattern           w d s               strPattern                    Doesn t work       strPattern                     Doesn t work       strPattern                r n               r n                Doesn t work       strPattern                r n               r n                Doesn t work         http   stackoverflow com questions 462843 improving-fixing-a-regex-for-c-style-block-comments     strPattern           gt       gt                            Works        string strOutput   System Text RegularExpressions Regex Replace strInput  strPattern  string Empty  System Text RegularExpressions RegexOptions Multiline       Console WriteLine strOutput       return strOutput       End Function RemoveCstyleComments   Removing single-line comments is here   https   stackoverflow com questions 9842991 regex-to-remove-single-line-sql-comments

User · Answer

To avoid third parties  regexes  memory overheads and fast work with large scripts I created my own stream-based parser  It   checks syntax before can recognize comments with -- or       -- some commented text     drop table Users  GO        can recognize string literals with   or    set  s        create table foo           GO     create index        preserves LF and CR formatting preserves comments block in object bodies  stored procedures  views etc   and other constructions such as        gO -- commented text    How to use      try               using  SqlConnection connection   new SqlConnection  Integrated Security SSPI Persist Security Info True Initial Catalog DATABASE-NAME Data Source SERVER-NAME                          connection Open                 int rowsAffected   SqlStatementReader ExecuteSqlFile                   C   target-sql-script sql                   connection                     Don t forget to use the correct file encoding                    Encoding Default                     Indefinitely  sec                  0                                       implement your handlers     catch  SqlStatementReader SqlBadSyntaxException          catch  SqlException          catch  Exception        Stream-based SQL script reader  class SqlStatementReader       public class SqlBadSyntaxException   Exception               public SqlBadSyntaxException string description    base description              public SqlBadSyntaxException string description  int line    base OnBase description  line  null               public SqlBadSyntaxException string description  int line  string filePath    base OnBase description  line  filePath               private static string OnBase string description  int line  string filePath                        if  filePath    null                  return string Format  Line   0    1    line  description               else                 return string Format  File   0  r nLine   1    2    filePath  line  description                        enum SqlScriptChunkTypes               InstructionOrUnquotedIdentifier   0          BracketIdentifier   1          QuotIdentifierOrLiteral   2          DblQuotIdentifierOrLiteral   3          CommentLine   4          CommentMultiline   5             StreamReader  sr   null      string  filePath   null      int  lineStart   1      int  lineEnd   1      bool  isNextChar   false      char  nextChar     0        public SqlStatementReader StreamReader sr                if  sr    null              throw new ArgumentNullException  StreamReader can t be null              if  sr BaseStream is FileStream               filePath     FileStream sr BaseStream  Name            sr   sr             public SqlStatementReader StreamReader sr  string filePath                if  sr    null              throw new ArgumentNullException  StreamReader can t be null               sr   sr           filePath   filePath             public int LineStart   get   return  lineStart          public int LineEnd   get   return  lineEnd    1    lineEnd    lineEnd - 1           public void LightSyntaxCheck                 while  ReadStatementInternal true     null               public string ReadStatement                 for  string s   ReadStatementInternal false   s    null  s   ReadStatementInternal false                            skip empty             for  int i   0  i  lt  s Length  i                                  switch  s i                                         case      continue                      case   t   continue                      case   r   continue                      case   n   continue                      default                          return s                                                    return null             string ReadStatementInternal bool syntaxCheck                if   isNextChar    false  amp  amp   sr EndOfStream              return null           StringBuilder allLines   new StringBuilder            StringBuilder line   new StringBuilder            SqlScriptChunkTypes nextChunk   SqlScriptChunkTypes InstructionOrUnquotedIdentifier          SqlScriptChunkTypes currentChunk   SqlScriptChunkTypes InstructionOrUnquotedIdentifier          char ch     0           int lineCounter   0          int nextLine   0          int currentLine   0          bool nextCharHandled   false          bool foundGO          int go   1           while  ReadChar out ch                         if  nextCharHandled    false                                currentChunk   nextChunk                  currentLine   nextLine                   switch  currentChunk                                        case SqlScriptChunkTypes InstructionOrUnquotedIdentifier                           if  ch                                                               currentChunk   nextChunk   SqlScriptChunkTypes BracketIdentifier                              currentLine   nextLine   lineCounter                                                    else if  ch                                                               currentChunk   nextChunk   SqlScriptChunkTypes DblQuotIdentifierOrLiteral                              currentLine   nextLine   lineCounter                                                    else if  ch                                                                currentChunk   nextChunk   SqlScriptChunkTypes QuotIdentifierOrLiteral                              currentLine   nextLine   lineCounter                                                    else if  ch     -   amp  amp    isNextChar  amp  amp   nextChar     -                                                          nextCharHandled   true                              currentChunk   nextChunk   SqlScriptChunkTypes CommentLine                              currentLine   nextLine   lineCounter                                                    else if  ch         amp  amp    isNextChar  amp  amp   nextChar                                                                nextCharHandled   true                              currentChunk   nextChunk   SqlScriptChunkTypes CommentMultiline                              currentLine   nextLine   lineCounter                                                    else if  ch                                                               throw new SqlBadSyntaxException  Incorrect syntax near         lineEnd   lineCounter   filePath                                                     else if  ch         amp  amp    isNextChar  amp  amp   nextChar                                                                throw new SqlBadSyntaxException  Incorrect syntax near         lineEnd   lineCounter   filePath                                                     break                       case SqlScriptChunkTypes CommentLine                           if  ch      r   amp  amp    isNextChar  amp  amp   nextChar      n                                                          nextCharHandled   true                              currentChunk   nextChunk   SqlScriptChunkTypes InstructionOrUnquotedIdentifier                              currentLine   nextLine   lineCounter                                                    else if  ch      n     ch      r                                                         currentChunk   nextChunk   SqlScriptChunkTypes InstructionOrUnquotedIdentifier                              currentLine   nextLine   lineCounter                                                    break                       case SqlScriptChunkTypes CommentMultiline                           if  ch         amp  amp    isNextChar  amp  amp   nextChar                                                                nextCharHandled   true                              nextChunk   SqlScriptChunkTypes InstructionOrUnquotedIdentifier                              nextLine   lineCounter                                                    else if  ch         amp  amp    isNextChar  amp  amp   nextChar                                                                throw new SqlBadSyntaxException  Missing end comment mark          lineEnd   currentLine   filePath                                                     break                       case SqlScriptChunkTypes BracketIdentifier                           if  ch                                                               nextChunk   SqlScriptChunkTypes InstructionOrUnquotedIdentifier                              nextLine   lineCounter                                                    break                       case SqlScriptChunkTypes DblQuotIdentifierOrLiteral                           if  ch                                                               if   isNextChar  amp  amp   nextChar                                                                       nextCharHandled   true                                                            else                                                               nextChunk   SqlScriptChunkTypes InstructionOrUnquotedIdentifier                                  nextLine   lineCounter                                                                                  break                       case SqlScriptChunkTypes QuotIdentifierOrLiteral                           if  ch                                                                if   isNextChar  amp  amp   nextChar                                                                        nextCharHandled   true                                                            else                                                               nextChunk   SqlScriptChunkTypes InstructionOrUnquotedIdentifier                                  nextLine   lineCounter                                                                                  break                                              else                 nextCharHandled   false               foundGO   false              if  currentChunk    SqlScriptChunkTypes InstructionOrUnquotedIdentifier    go  gt   5     go    4  amp  amp  currentChunk    SqlScriptChunkTypes CommentLine                                    go   0 - break  1 - begin of the string  2 - spaces after begin of the string  3 - G or g  4 - O or o  5 - spaces after GO  6 - line comment after valid GO                 switch  go                                        case 0                          if  ch      r     ch      n                               go   1                          break                      case 1                          if  ch           ch      t                               go   2                          else if  ch     G     ch     g                               go   3                          else if  ch      n   amp  amp  ch      r                               go   0                          break                      case 2                          if  ch     G     ch     g                               go   3                          else if  ch      n     ch      r                               go   1                          else if  ch         amp  amp  ch      t                               go   0                          break                      case 3                          if  ch     O     ch     o                               go   4                          else if  ch      n     ch      r                               go   1                          else                             go   0                          break                      case 4                          if  ch      r   amp  amp    isNextChar  amp  amp   nextChar      n                                go   5                          else if  ch      n     ch      r                               foundGO   true                          else if  ch           ch      t                               go   5                          else if  ch     -   amp  amp    isNextChar  amp  amp   nextChar     -                                go   6                          else                             go   0                          break                      case 5                          if  ch      r   amp  amp    isNextChar  amp  amp   nextChar      n                                go   5                          else if  ch      n     ch      r                               foundGO   true                          else if  ch     -   amp  amp    isNextChar  amp  amp   nextChar     -                                go   6                          else if  ch         amp  amp  ch      t                               throw new SqlBadSyntaxException  Incorrect syntax was encountered while parsing go     lineEnd   lineCounter   filePath                           break                      case 6                          if  ch      r   amp  amp    isNextChar  amp  amp   nextChar      n                                go   6                          else if  ch      n     ch      r                               foundGO   true                          break                      default                          go   0                          break                                              else                 go   0               if  foundGO                                if  ch      r     ch      n                                           lineCounter                                       clear GO                 string s   line Append ch  ToString                    for  int i   0  i  lt  s Length  i                                          switch  s i                                                 case      continue                          case   t   continue                          case   r   continue                          case   n   continue                          default                               lineStart    lineEnd                               lineEnd    lineCounter                              return allLines Append s Substring 0  i   ToString                                                            return string Empty                                accumulate by string             if  ch      r   amp  amp    isNextChar    false     nextChar      n                                    lineCounter                  if  syntaxCheck    false                      allLines Append line Append   r   ToString                     line Clear                              else if  ch      n                                   lineCounter                  if  syntaxCheck    false                      allLines Append line Append   n   ToString                     line Clear                              else                               if  syntaxCheck    false                      line Append ch                                       this is the end of the stream  return it without GO  if GO exists         switch  currentChunk                        case SqlScriptChunkTypes InstructionOrUnquotedIdentifier              case SqlScriptChunkTypes CommentLine                  break              case SqlScriptChunkTypes CommentMultiline                  if  nextChunk    SqlScriptChunkTypes InstructionOrUnquotedIdentifier                      throw new SqlBadSyntaxException  Missing end comment mark          lineEnd   currentLine   filePath                   break              case SqlScriptChunkTypes BracketIdentifier                  if  nextChunk    SqlScriptChunkTypes InstructionOrUnquotedIdentifier                      throw new SqlBadSyntaxException  Unclosed quotation mark       lineEnd   currentLine   filePath                   break              case SqlScriptChunkTypes DblQuotIdentifierOrLiteral                  if  nextChunk    SqlScriptChunkTypes InstructionOrUnquotedIdentifier                      throw new SqlBadSyntaxException  Unclosed quotation mark        lineEnd   currentLine   filePath                   break              case SqlScriptChunkTypes QuotIdentifierOrLiteral                  if  nextChunk    SqlScriptChunkTypes InstructionOrUnquotedIdentifier                      throw new SqlBadSyntaxException  Unclosed quotation mark       lineEnd   currentLine   filePath                   break                     if  go  gt   4                        string s   line ToString                for  int i   0  i  lt  s Length  i                                  switch  s i                                         case      continue                      case   t   continue                      case   r   continue                      case   n   continue                      default                           lineStart    lineEnd                           lineEnd    lineCounter   1                          return allLines Append s Substring 0  i   ToString                                                        lineStart    lineEnd           lineEnd    lineCounter   1          return allLines Append line ToString    ToString               bool ReadChar out char ch                if   isNextChar                        ch    nextChar              if   sr EndOfStream                   isNextChar   false              else                  nextChar   Convert ToChar  sr Read                 return true                    else if   sr EndOfStream    false                        ch   Convert ToChar  sr Read                 if   sr EndOfStream    false                                 isNextChar   true                   nextChar   Convert ToChar  sr Read                               return true                    else                       ch     0               return false                       public static int ExecuteSqlFile string filePath  SqlConnection connection  Encoding fileEncoding  int commandTimeout                int rowsAffected   0          using  FileStream fs   new FileStream filePath  FileMode Open  FileAccess Read  FileShare Read                            Simple syntax check  you can comment out these two lines below              new SqlStatementReader new StreamReader fs  fileEncoding   LightSyntaxCheck                fs Seek 0L  SeekOrigin Begin                   Read statements without GO             SqlStatementReader rd   new SqlStatementReader new StreamReader fs  fileEncoding                string stmt              while   stmt   rd ReadStatement       null                                using  SqlCommand cmd   connection CreateCommand                                          cmd CommandText   stmt                      cmd CommandTimeout   commandTimeout                      int i   cmd ExecuteNonQuery                        if  i  gt  0                          rowsAffected    i                                                    return rowsAffected

User · Answer

You can use SQL Management Objects to perform this   These are the same objects that Management Studio uses to execute queries   I believe Server ConnectionContext ExecuteNonQuery   will perform what you need

User · Answer

Too difficult     Create  array of strings str   replacing GO with                     string   str                       USE master         CREATE DATABASE    con str initdir           -- Verify the database files and sizes --SELECT name  size  size 1 0 128 AS  Size in MBs   --SELECT name  --FROM sys master files --WHERE name   N     con str initdir        --GO  USE     con str initdir             SET ANSI NULLS ON      SET QUOTED IDENTIFIER ON       IF NOT EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID N  dbo   Customers    AND type in  N U    BEGIN CREATE TABLE  dbo   Customers        CustomerID   int  IDENTITY 1 1  NOT NULL       CustomerName   nvarchar  50  NULL   CONSTRAINT  PK Customers  PRIMARY KEY CLUSTERED         CustomerID  ASC  WITH  PAD INDEX    OFF  IGNORE DUP KEY   OFF  ON  PRIMARY    ON  PRIMARY  END         SET ANSI NULLS ON      SET QUOTED IDENTIFIER ON      IF NOT EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID N  dbo   GOODS    AND type in  N U    BEGIN CREATE TABLE  dbo   GOODS        GoodsID   int  IDENTITY 1 1  NOT NULL       GoodsName   nvarchar  50  NOT NULL       GoodsPrice   float  NOT NULL   CONSTRAINT  PK GOODS  PRIMARY KEY CLUSTERED         GoodsID  ASC  WITH  PAD INDEX    OFF  IGNORE DUP KEY   OFF  ON  PRIMARY    ON  PRIMARY  END      SET ANSI NULLS ON      SET QUOTED IDENTIFIER ON      IF NOT EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID N  dbo   Orders    AND type in  N U    BEGIN CREATE TABLE  dbo   Orders        OrderID   int  IDENTITY 1 1  NOT NULL       CustomerID   int  NOT NULL       Date   smalldatetime  NOT NULL   CONSTRAINT  PK Orders  PRIMARY KEY CLUSTERED         OrderID  ASC  WITH  PAD INDEX    OFF  IGNORE DUP KEY   OFF  ON  PRIMARY    ON  PRIMARY  END      SET ANSI NULLS ON      SET QUOTED IDENTIFIER ON      IF NOT EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID N  dbo   OrderDetails    AND type in  N U    BEGIN CREATE TABLE  dbo   OrderDetails        OrderID   int  NOT NULL       GoodsID   int  NOT NULL       Qty   int  NOT NULL       Price   float  NOT NULL   CONSTRAINT  PK OrderDetails  PRIMARY KEY CLUSTERED         OrderID  ASC       GoodsID  ASC  WITH  PAD INDEX    OFF  IGNORE DUP KEY   OFF  ON  PRIMARY    ON  PRIMARY  END       SET ANSI NULLS ON      SET QUOTED IDENTIFIER ON      IF NOT EXISTS  SELECT   FROM sys objects WHERE object id   OBJECT ID N  dbo   InsertCustomers    AND type in  N P   N PC    BEGIN EXEC dbo sp executesql  statement   N --                                               -- Author        lt Author  Name gt  -- Create date   lt Create Date   gt  -- Description   lt Description   gt  --                                               create PROCEDURE  dbo   InsertCustomers    CustomerName nvarchar 50     Identity int OUT AS INSERT INTO Customers  CustomerName  VALUES  CustomerName  SET  Identity   SCOPE IDENTITY       END      IF NOT EXISTS  SELECT   FROM sys foreign keys WHERE object id   OBJECT ID N  dbo   FK Orders Customers    AND parent object id   OBJECT ID N  dbo   Orders     ALTER TABLE  dbo   Orders   WITH CHECK ADD  CONSTRAINT  FK Orders Customers  FOREIGN KEY  CustomerID   REFERENCES  dbo   Customers    CustomerID   ON UPDATE CASCADE      ALTER TABLE  dbo   Orders  CHECK CONSTRAINT  FK Orders Customers       IF NOT EXISTS  SELECT   FROM sys foreign keys WHERE object id   OBJECT ID N  dbo   FK OrderDetails GOODS    AND parent object id   OBJECT ID N  dbo   OrderDetails     ALTER TABLE  dbo   OrderDetails   WITH CHECK ADD  CONSTRAINT  FK OrderDetails GOODS  FOREIGN KEY  GoodsID   REFERENCES  dbo   GOODS    GoodsID   ON UPDATE CASCADE      ALTER TABLE  dbo   OrderDetails  CHECK CONSTRAINT  FK OrderDetails GOODS       IF NOT EXISTS  SELECT   FROM sys foreign keys WHERE object id   OBJECT ID N  dbo   FK OrderDetails Orders    AND parent object id   OBJECT ID N  dbo   OrderDetails     ALTER TABLE  dbo   OrderDetails   WITH CHECK ADD  CONSTRAINT  FK OrderDetails Orders  FOREIGN KEY  OrderID   REFERENCES  dbo   Orders    OrderID   ON UPDATE CASCADE ON DELETE CASCADE      ALTER TABLE  dbo   OrderDetails  CHECK CONSTRAINT  FK OrderDetails Orders                                      for int i  0  i lt str Length i                                       myCommand CommandText str i                   try                                   myCommand ExecuteNonQuery                                      catch  SystemException ee                                        MessageBox Show  Error     ee ToString                                        That s all  enjoy

User · Answer

use the following method to split the string and execute batch by batch  using System  using System IO  using System Text RegularExpressions  namespace RegExTrial       class Program               static void Main string   args                        string sql   String Empty              string path   D  temp sample sql               using  StreamReader reader   new StreamReader path                     sql   reader ReadToEnd                                            Select any GO  ignore case  that starts with at least                one white space such as tab  space new line  verticle tab etc             string pattern     s   i GO  -i                 Regex matcher   new Regex pattern  RegexOptions Compiled               int start   0              int end   0              Match batch matcher Match sql               while  batch Success                    end   batch Index                  string batchQuery   sql Substring start  end - start  Trim                      execute the batch                 ExecuteBatch batchQuery                   start   end   batch Length                  batch   matcher Match sql start                                     private static void ExecuteBatch string command                           execute your query here

User · Answer

If you don t want to use SMO  which is better than the solution below  but i want to give an alternative     you can split your query with this function   It is    Comment proof  example --GO or    GO     Only works on a new line  just as in SSMS  example    test    GO works and select 1 as go not String proof  example print  no go     private List lt string gt  SplitScriptGo string script        var result   new List lt string gt         int pos1   0      int pos2   0      bool whiteSpace   true      bool emptyLine   true      bool inStr   false      bool inComment1   false      bool inComment2   false       while  true                while  pos2  lt  script Length  amp  amp  Char IsWhiteSpace script pos2                          if  script pos2       r     script pos2       n                                 emptyLine   true                  inComment1   false                             pos2                       if  pos2    script Length              break           bool min2    pos2   1   lt  script Length          bool min3    pos2   2   lt  script Length           if   inStr  amp  amp   inComment2  amp  amp  min2  amp  amp  script Substring pos2  2      --               inComment1   true           if   inStr  amp  amp   inComment1  amp  amp  min2  amp  amp  script Substring pos2  2                       inComment2   true           if   inComment1  amp  amp   inComment2  amp  amp  script pos2                       inStr    inStr           if   inStr  amp  amp   inComment1  amp  amp   inComment2  amp  amp  emptyLine              amp  amp   min2  amp  amp  script Substring pos2  2  ToLower       go                amp  amp    min3    char IsWhiteSpace script pos2   2      script Substring pos2   2  2      --     script Substring pos2   2  2                                  if   whiteSpace                  result Add script Substring pos1  pos2 - pos1                 whiteSpace   true              emptyLine   false              pos2    2              pos1   pos2                    else                       pos2                whiteSpace   false               if   inComment2                  emptyLine   false                     if   inStr  amp  amp  inComment2  amp  amp  pos2  gt  1  amp  amp  script Substring pos2 - 2  2                       inComment2   false             if   whiteSpace          result Add script Substring pos1         return result

[c#] Execute a large SQL script (with GO commands)

Examples related to c#

Examples related to sql-server