Execute a large SQL script (with GO commands)

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?

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

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

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.

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 < batches.Length; ++i)
            {
                // Skip batches that merely contain the batch terminator
                if (batches[i].StartsWith(BatchTerminator, StringComparison.OrdinalIgnoreCase) ||
                    (i == batches.Length - 1 && string.IsNullOrWhiteSpace(batches[i])))
                {
                    continue;
                }

                // Include batch terminator if the next element is a batch terminator
                if (batches.Length > i + 1 &&
                    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 < repeatCount; ++j)
                    {
                       var command = sqlConnection.CreateCommand();
                       command.CommandText = batches[i];
                       command.ExecuteNonQuery();
                    }
                }
                else
                {
                    var command = sqlConnection.CreateCommand();
                    command.CommandText = batches[i];
                    command.ExecuteNonQuery();
                }
            }
        }

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.

Based on Blorgbeard's solution.

foreach (var sqlBatch in commandText.Split(new[] { "GO" }, StringSplitOptions.RemoveEmptyEntries))
{
   sqlCommand.CommandText = sqlBatch;
   sqlCommand.ExecuteNonQuery();
}

If you don't want to install SMO objects you can use gplex tool (see this 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# & 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 = @"/\*(?>(?:(?>[^*]+)|\*(?!/))*)\*/";  // 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

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...)

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.

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!

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<string> SplitScriptGo(string script)
   {
       var result = new List<string>();
       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 < script.Length && 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) < script.Length;
           bool min3 = (pos2 + 2) < script.Length;
  
           if (!inStr && !inComment2 && min2 && script.Substring(pos2, 2) == "--")
               inComment1 = true;
  
           if (!inStr && !inComment1 && min2 && script.Substring(pos2, 2) == "/*")
               inComment2 = true;
  
           if (!inComment1 && !inComment2 && script[pos2] == '\'')
               inStr = !inStr;
  
           if (!inStr && !inComment1 && !inComment2 && emptyLine
               && (min2 && script.Substring(pos2, 2).ToLower() == "go")
               && (!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 && inComment2 && pos2 > 1 && script.Substring(pos2 - 2, 2) == "*/")
               inComment2 = false;
       }
  
       if (!whiteSpace)
           result.Add(script.Substring(pos1));
  
       return result;
   }
  

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
        }

    }
}

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 < 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 && _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 == '-' && (_isNextChar && _nextChar == '-'))
						{
							nextCharHandled = true;
							currentChunk = nextChunk = SqlScriptChunkTypes.CommentLine;
							currentLine = nextLine = lineCounter;
						}
						else if (ch == '/' && (_isNextChar && _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 == '*' && (_isNextChar && _nextChar == '/'))
						{
							throw new SqlBadSyntaxException("Incorrect syntax near '*'.", _lineEnd + lineCounter, _filePath);
						}
						break;

					case SqlScriptChunkTypes.CommentLine:

						if (ch == '\r' && (_isNextChar && _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 == '*' && (_isNextChar && _nextChar == '/'))
						{
							nextCharHandled = true;
							nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
							nextLine = lineCounter;
						}
						else if (ch == '/' && (_isNextChar && _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 && _nextChar == '"')
							{
								nextCharHandled = true;
							}
							else
							{
								nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
								nextLine = lineCounter;
							}
						}
						break;

					case SqlScriptChunkTypes.QuotIdentifierOrLiteral:

						if (ch == '\'')
						{
							if (_isNextChar && _nextChar == '\'')
							{
								nextCharHandled = true;
							}
							else
							{
								nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;
								nextLine = lineCounter;
							}
						}
						break;
				}
			}
			else
				nextCharHandled = false;

			foundGO = false;
			if (currentChunk == SqlScriptChunkTypes.InstructionOrUnquotedIdentifier || go >= 5 || (go == 4 && 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' && 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 != ' ' && 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' && (_isNextChar && _nextChar == '\n'))
							go = 5;
						else if (ch == '\n' || ch == '\r')
							foundGO = true;
						else if (ch == ' ' || ch == '\t')
							go = 5;
						else if (ch == '-' && (_isNextChar && _nextChar == '-'))
							go = 6;
						else
							go = 0;
						break;
					case 5:
						if (ch == '\r' && (_isNextChar && _nextChar == '\n'))
							go = 5;
						else if (ch == '\n' || ch == '\r')
							foundGO = true;
						else if (ch == '-' && (_isNextChar && _nextChar == '-'))
							go = 6;
						else if (ch != ' ' && ch != '\t')
							throw new SqlBadSyntaxException("Incorrect syntax was encountered while parsing go.", _lineEnd + lineCounter, _filePath);
						break;
					case 6:
						if (ch == '\r' && (_isNextChar && _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 < 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' && (_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 >= 4)
		{
			string s = line.ToString();
			for (int i = 0; i < 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 > 0)
						rowsAffected += i;
				}
			}
		}
		return rowsAffected;
	}
}

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.

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

You can just use ; at the end of each statement as it worked for me. Really don't know if there are any drawbacks to it.

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

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:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
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<str.Length;i++)     
            {
                myCommand.CommandText=str[i];
                try
                {
                myCommand.ExecuteNonQuery();
                }
                catch (SystemException ee)
                {
                    MessageBox.Show("Error   "+ee.ToString());
                }
                
            }

That's all, enjoy.