<p>I need to execute a large set of SQL statements (creating a bunch of tables, views and stored procedures) from within a C# program.</p>

<p>These statements need to be separated by <code>GO</code> statements, but <code>SqlCommand.ExecuteNonQuery()</code> does not like <code>GO</code> statements. My solution, which I suppose I'll post for reference, was to split the SQL string on <code>GO</code> lines, and execute each batch separately.</p>

<p>Is there an easier/better way?</p>

[{'answer_id': 40824, 'author': 'ila', 'author_id': 1178, 'author_profile': 'https://Stackoverflow.com/users/1178', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n\n<p>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...)</p>\n'}, {'answer_id': 40827, 'author': 'Blorgbeard', 'author_id': 369, 'author_profile': 'https://Stackoverflow.com/users/369', 'pm_score': 5, 'selected': False, 'text': '<p>This is what I knocked together to solve my immediate problem.</p>\n\n<pre><code>private void ExecuteBatchNonQuery(string sql, SqlConnection conn) {\n    string sqlBatch = string.Empty;\n    SqlCommand cmd = new SqlCommand(string.Empty, conn);\n    conn.Open();\n    sql += "\\nGO";   // make sure last batch is executed.\n    try {\n        foreach (string line in sql.Split(new string[2] { "\\n", "\\r" }, StringSplitOptions.RemoveEmptyEntries)) {\n            if (line.ToUpperInvariant().Trim() == "GO") {\n                cmd.CommandText = sqlBatch;\n                cmd.ExecuteNonQuery();\n                sqlBatch = string.Empty;\n            } else {\n                sqlBatch += line + "\\n";\n            }\n        }            \n    } finally {\n        conn.Close();\n    }\n}\n</code></pre>\n\n<p>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:</p>\n\n<pre><code>ExecuteBatchNonQuery(@"\n    /*\n    GO\n    */", conn);\n</code></pre>\n'}, {'answer_id': 40830, 'author': 'Jon Galloway', 'author_id': 5, 'author_profile': 'https://Stackoverflow.com/users/5', 'pm_score': 8, 'selected': True, 'text': '<p>Use SQL Server Management Objects (SMO) which understands GO separators. See my blog post here: <a href="http://weblogs.asp.net/jgalloway/archive/2006/11/07/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-_2D00_-the-easy-way.aspx" rel="noreferrer">http://weblogs.asp.net/jongalloway/Handling-_2200_GO_2200_-Separators-in-SQL-Scripts-<em>2D00</em>-the-easy-way</a></p>\n\n<p>Sample code:</p>\n\n<pre><code>public static void Main()    \n{        \n  string scriptDirectory = "c:\\\\temp\\\\sqltest\\\\";\n  string sqlConnectionString = "Integrated Security=SSPI;" +\n  "Persist Security Info=True;Initial Catalog=Northwind;Data Source=(local)";\n  DirectoryInfo di = new DirectoryInfo(scriptDirectory);\n  FileInfo[] rgFiles = di.GetFiles("*.sql");\n  foreach (FileInfo fi in rgFiles)\n  {\n        FileInfo fileInfo = new FileInfo(fi.FullName);\n        string script = fileInfo.OpenText().ReadToEnd();\n        using (SqlConnection connection = new SqlConnection(sqlConnectionString))\n        {\n            Server server = new Server(new ServerConnection(connection));\n            server.ConnectionContext.ExecuteNonQuery(script);\n        }\n   }\n}\n</code></pre>\n\n<p>If that won\'t work for you, see Phil Haack\'s library which handles that: <a href="http://haacked.com/archive/2007/11/04/a-library-for-executing-sql-scripts-with-go-separators-and.aspx" rel="noreferrer">http://haacked.com/archive/2007/11/04/a-library-for-executing-sql-scripts-with-go-separators-and.aspx</a></p>\n'}, {'answer_id': 40839, 'author': 'tbreffni', 'author_id': 637, 'author_profile': 'https://Stackoverflow.com/users/637', 'pm_score': 4, 'selected': False, 'text': '<p>You can use <a href="http://msdn.microsoft.com/en-us/library/ms162557.aspx" rel="nofollow noreferrer">SQL Management Objects</a> to perform this.  These are the same objects that Management Studio uses to execute queries.  I believe <code>Server.ConnectionContext.ExecuteNonQuery()</code> will perform what you need.</p>\n'}, {'answer_id': 40850, 'author': 'John Hubert', 'author_id': 4286, 'author_profile': 'https://Stackoverflow.com/users/4286', 'pm_score': 3, 'selected': False, 'text': '<p>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.</p>\n'}, {'answer_id': 40861, 'author': 'jason saldo', 'author_id': 1293, 'author_profile': 'https://Stackoverflow.com/users/1293', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n'}, {'answer_id': 9892659, 'author': 'Natalya', 'author_id': 1278118, 'author_profile': 'https://Stackoverflow.com/users/1278118', 'pm_score': 2, 'selected': False, 'text': '<p>If you don\'t want to install SMO objects you can use gplex tool (see <a href="https://stackoverflow.com/a/9892349/1278118">this answer</a>)</p>\n'}, {'answer_id': 10734775, 'author': 'grv', 'author_id': 1414633, 'author_profile': 'https://Stackoverflow.com/users/1414633', 'pm_score': -1, 'selected': False, 'text': '<p>Too difficult :)</p>\n\n<p>Create  array of strings str[] replacing GO with ",@" :</p>\n\n<pre><code>            string[] str ={\n                @"\nUSE master;\n",@"\n\n\nCREATE DATABASE " +con_str_initdir+ @";\n",@"\n-- Verify the database files and sizes\n--SELECT name, size, size*1.0/128 AS [Size in MBs] \n--SELECT name \n--FROM sys.master_files\n--WHERE name = N\'" + con_str_initdir + @"\';\n--GO\n\nUSE " + con_str_initdir + @";\n",@"\n\nSET ANSI_NULLS ON\n",@"\nSET QUOTED_IDENTIFIER ON\n",@"\n\nIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N\'[dbo].[Customers]\') AND type in (N\'U\'))\nBEGIN\nCREATE TABLE [dbo].[Customers](\n    [CustomerID] [int] IDENTITY(1,1) NOT NULL,\n    [CustomerName] [nvarchar](50) NULL,\n CONSTRAINT [PK_Customers] PRIMARY KEY CLUSTERED \n(\n    [CustomerID] ASC\n)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]\n) ON [PRIMARY]\nEND\n",@"\n\n\n\nSET ANSI_NULLS ON\n",@"\nSET QUOTED_IDENTIFIER ON\n",@"\nIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N\'[dbo].[GOODS]\') AND type in (N\'U\'))\nBEGIN\nCREATE TABLE [dbo].[GOODS](\n    [GoodsID] [int] IDENTITY(1,1) NOT NULL,\n    [GoodsName] [nvarchar](50) NOT NULL,\n    [GoodsPrice] [float] NOT NULL,\n CONSTRAINT [PK_GOODS] PRIMARY KEY CLUSTERED \n(\n    [GoodsID] ASC\n)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]\n) ON [PRIMARY]\nEND\n",@"\nSET ANSI_NULLS ON\n",@"\nSET QUOTED_IDENTIFIER ON\n",@"\nIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N\'[dbo].[Orders]\') AND type in (N\'U\'))\nBEGIN\nCREATE TABLE [dbo].[Orders](\n    [OrderID] [int] IDENTITY(1,1) NOT NULL,\n    [CustomerID] [int] NOT NULL,\n    [Date] [smalldatetime] NOT NULL,\n CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED \n(\n    [OrderID] ASC\n)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]\n) ON [PRIMARY]\nEND\n",@"\nSET ANSI_NULLS ON\n",@"\nSET QUOTED_IDENTIFIER ON\n",@"\nIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N\'[dbo].[OrderDetails]\') AND type in (N\'U\'))\nBEGIN\nCREATE TABLE [dbo].[OrderDetails](\n    [OrderID] [int] NOT NULL,\n    [GoodsID] [int] NOT NULL,\n    [Qty] [int] NOT NULL,\n    [Price] [float] NOT NULL,\n CONSTRAINT [PK_OrderDetails] PRIMARY KEY CLUSTERED \n(\n    [OrderID] ASC,\n    [GoodsID] ASC\n)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]\n) ON [PRIMARY]\nEND\n",@"\n\nSET ANSI_NULLS ON\n",@"\nSET QUOTED_IDENTIFIER ON\n",@"\nIF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N\'[dbo].[InsertCustomers]\') AND type in (N\'P\', N\'PC\'))\nBEGIN\nEXEC dbo.sp_executesql @statement = N\'-- =============================================\n-- Author:      &lt;Author,,Name&gt;\n-- Create date: &lt;Create Date,,&gt;\n-- Description: &lt;Description,,&gt;\n-- =============================================\ncreate PROCEDURE [dbo].[InsertCustomers]\n @CustomerName nvarchar(50),\n @Identity int OUT\nAS\nINSERT INTO Customers (CustomerName) VALUES(@CustomerName)\nSET @Identity = SCOPE_IDENTITY()\n\n\' \nEND\n",@"\nIF 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]\'))\nALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Customers] FOREIGN KEY([CustomerID])\nREFERENCES [dbo].[Customers] ([CustomerID])\nON UPDATE CASCADE\n",@"\nALTER TABLE [dbo].[Orders] CHECK CONSTRAINT [FK_Orders_Customers]\n",@"\nIF 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]\'))\nALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetails_GOODS] FOREIGN KEY([GoodsID])\nREFERENCES [dbo].[GOODS] ([GoodsID])\nON UPDATE CASCADE\n",@"\nALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_GOODS]\n",@"\nIF 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]\'))\nALTER TABLE [dbo].[OrderDetails]  WITH CHECK ADD  CONSTRAINT [FK_OrderDetails_Orders] FOREIGN KEY([OrderID])\nREFERENCES [dbo].[Orders] ([OrderID])\nON UPDATE CASCADE\nON DELETE CASCADE\n",@"\nALTER TABLE [dbo].[OrderDetails] CHECK CONSTRAINT [FK_OrderDetails_Orders]\n\n\n                "};\n\n\n            for(int i =0; i&lt;str.Length;i++)     \n            {\n                myCommand.CommandText=str[i];\n                try\n                {\n                myCommand.ExecuteNonQuery();\n                }\n                catch (SystemException ee)\n                {\n                    MessageBox.Show("Error   "+ee.ToString());\n                }\n\n            }\n</code></pre>\n\n<p>That\'s all, enjoy.</p>\n'}, {'answer_id': 13570258, 'author': 'Andy Dove', 'author_id': 1854197, 'author_profile': 'https://Stackoverflow.com/users/1854197', 'pm_score': 2, 'selected': False, 'text': '<p>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 :)</p>\n\n<p>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!</p>\n'}, {'answer_id': 23898655, 'author': 'Ryan Penfold', 'author_id': 1111806, 'author_profile': 'https://Stackoverflow.com/users/1111806', 'pm_score': 2, 'selected': False, 'text': '<p>Based on Blorgbeard\'s solution.</p>\n\n<pre><code>foreach (var sqlBatch in commandText.Split(new[] { "GO" }, StringSplitOptions.RemoveEmptyEntries))\n{\n   sqlCommand.CommandText = sqlBatch;\n   sqlCommand.ExecuteNonQuery();\n}\n</code></pre>\n'}, {'answer_id': 24179458, 'author': 'jbrunodomingues', 'author_id': 3644735, 'author_profile': 'https://Stackoverflow.com/users/3644735', 'pm_score': 0, 'selected': False, 'text': '<p>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.</p>\n\n<pre><code>String pattern = "\\\\bGO\\\\b|\\\\bgo\\\\b";\n\nString[] splitedSql = sql.split(pattern);\nfor (String chunk : splitedSql) {\n  getJdbcTemplate().update(chunk);\n}\n</code></pre>\n\n<p>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.</p>\n'}, {'answer_id': 25601409, 'author': 'Bigjim', 'author_id': 1773646, 'author_profile': 'https://Stackoverflow.com/users/1773646', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n\n<p>It is:</p>\n\n<ul>\n<li>Comment proof (example --GO or /* GO */)</li>\n<li>Only works on a new line, just as in SSMS (example /* test /* GO works and select 1 as go not</li>\n<li><p>String proof (example print \'no go \')</p>\n\n<pre><code>private List&lt;string&gt; SplitScriptGo(string script)\n{\n    var result = new List&lt;string&gt;();\n    int pos1 = 0;\n    int pos2 = 0;\n    bool whiteSpace = true;\n    bool emptyLine = true;\n    bool inStr = false;\n    bool inComment1 = false;\n    bool inComment2 = false;\n\n    while (true)\n    {\n        while (pos2 &lt; script.Length &amp;&amp; Char.IsWhiteSpace(script[pos2]))\n        {\n            if (script[pos2] == \'\\r\' || script[pos2] == \'\\n\')\n            {\n                emptyLine = true;\n                inComment1 = false;\n            }\n\n            pos2++;\n        }\n\n        if (pos2 == script.Length)\n            break;\n\n        bool min2 = (pos2 + 1) &lt; script.Length;\n        bool min3 = (pos2 + 2) &lt; script.Length;\n\n        if (!inStr &amp;&amp; !inComment2 &amp;&amp; min2 &amp;&amp; script.Substring(pos2, 2) == "--")\n            inComment1 = true;\n\n        if (!inStr &amp;&amp; !inComment1 &amp;&amp; min2 &amp;&amp; script.Substring(pos2, 2) == "/*")\n            inComment2 = true;\n\n        if (!inComment1 &amp;&amp; !inComment2 &amp;&amp; script[pos2] == \'\\\'\')\n            inStr = !inStr;\n\n        if (!inStr &amp;&amp; !inComment1 &amp;&amp; !inComment2 &amp;&amp; emptyLine\n            &amp;&amp; (min2 &amp;&amp; script.Substring(pos2, 2).ToLower() == "go")\n            &amp;&amp; (!min3 || char.IsWhiteSpace(script[pos2 + 2]) || script.Substring(pos2 + 2, 2) == "--" || script.Substring(pos2 + 2, 2) == "/*"))\n        {\n            if (!whiteSpace)\n                result.Add(script.Substring(pos1, pos2 - pos1));\n\n            whiteSpace = true;\n            emptyLine = false;\n            pos2 += 2;\n            pos1 = pos2;\n        }\n        else\n        {\n            pos2++;\n            whiteSpace = false;\n\n            if (!inComment2)\n                emptyLine = false;\n        }\n\n        if (!inStr &amp;&amp; inComment2 &amp;&amp; pos2 &gt; 1 &amp;&amp; script.Substring(pos2 - 2, 2) == "*/")\n            inComment2 = false;\n    }\n\n    if (!whiteSpace)\n        result.Add(script.Substring(pos1));\n\n    return result;\n}\n</code></pre></li>\n</ul>\n'}, {'answer_id': 25992625, 'author': 'Stefan Steiger', 'author_id': 155077, 'author_profile': 'https://Stackoverflow.com/users/155077', 'pm_score': 2, 'selected': False, 'text': '<p>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.</p>\n\n<p><a href="https://github.com/ststeiger/ScriptSplitter" rel="nofollow noreferrer">Here\'s</a> the implementation in C# &amp; VB.NET </p>\n\n<p>Usage:</p>\n\n<pre><code>    string strSQL = @"\nSELECT * FROM INFORMATION_SCHEMA.columns\nGO\nSELECT * FROM INFORMATION_SCHEMA.views\n";\n\n    foreach(string Script in new Subtext.Scripting.ScriptSplitter(strSQL ))\n    {\n        Console.WriteLine(Script);\n    }\n</code></pre>\n\n<p>If you have problems with multiline c-style comments, remove the comments with regex:</p>\n\n<pre><code>static string RemoveCstyleComments(string strInput)\n{\n    string strPattern = @"/[*][\\w\\d\\s]+[*]/";\n    //strPattern = @"/\\*.*?\\*/"; // Doesn\'t work\n    //strPattern = "/\\\\*.*?\\\\*/"; // Doesn\'t work\n    //strPattern = @"/\\*([^*]|[\\r\\n]|(\\*+([^*/]|[\\r\\n])))*\\*+/ "; // Doesn\'t work\n    //strPattern = @"/\\*([^*]|[\\r\\n]|(\\*+([^*/]|[\\r\\n])))*\\*+/ "; // Doesn\'t work\n\n    // http://stackoverflow.com/questions/462843/improving-fixing-a-regex-for-c-style-block-comments\n    strPattern = @"/\\*(?&gt;(?:(?&gt;[^*]+)|\\*(?!/))*)\\*/";  // Works !\n\n    string strOutput = System.Text.RegularExpressions.Regex.Replace(strInput, strPattern, string.Empty, System.Text.RegularExpressions.RegexOptions.Multiline);\n    Console.WriteLine(strOutput);\n    return strOutput;\n} // End Function RemoveCstyleComments\n</code></pre>\n\n<p>Removing single-line comments is here:</p>\n\n<pre><code>https://stackoverflow.com/questions/9842991/regex-to-remove-single-line-sql-comments\n</code></pre>\n'}, {'answer_id': 28557032, 'author': 'Sriwantha Attanayake', 'author_id': 215336, 'author_profile': 'https://Stackoverflow.com/users/215336', 'pm_score': 1, 'selected': False, 'text': '<p>use the following method to split the string and execute batch by batch</p>\n\n<pre><code>using System;\nusing System.IO;\nusing System.Text.RegularExpressions;\nnamespace RegExTrial\n{\n    class Program\n    {\n        static void Main(string[] args)\n        {\n            string sql = String.Empty;\n            string path=@"D:\\temp\\sample.sql";\n            using (StreamReader reader = new StreamReader(path)) {\n                sql = reader.ReadToEnd();\n            }            \n            //Select any GO (ignore case) that starts with at least \n            //one white space such as tab, space,new line, verticle tab etc\n            string pattern="[\\\\s](?i)GO(?-i)";\n\n            Regex matcher = new Regex(pattern, RegexOptions.Compiled);\n            int start = 0;\n            int end = 0;\n            Match batch=matcher.Match(sql);\n            while (batch.Success) {\n                end = batch.Index;\n                string batchQuery = sql.Substring(start, end - start).Trim();\n                //execute the batch\n                ExecuteBatch(batchQuery);\n                start = end + batch.Length;\n                batch = matcher.Match(sql,start);\n            }\n\n        }\n\n        private static void ExecuteBatch(string command)\n        { \n            //execute your query here\n        }\n\n    }\n}\n</code></pre>\n'}, {'answer_id': 31785091, 'author': 'Morvael', 'author_id': 1286358, 'author_profile': 'https://Stackoverflow.com/users/1286358', 'pm_score': 0, 'selected': False, 'text': '<p>I hit this same issue and eventually just solved it by a simple string replace, replacing the word GO with a semi-colon (;)</p>\n\n<p>All seems to be working fine while executing scripts with in-line comments, block comments, and GO commands</p>\n\n<pre><code>public static bool ExecuteExternalScript(string filePath)\n{\n    using (StreamReader file = new StreamReader(filePath))\n    using (SqlConnection conn = new SqlConnection(dbConnStr))\n    {\n        StringBuilder sql = new StringBuilder();\n\n        string line;\n        while ((line = file.ReadLine()) != null)\n        {\n            // replace GO with semi-colon\n            if (line == "GO")\n                sql.Append(";");\n            // remove inline comments\n            else if (line.IndexOf("--") &gt; -1)\n                sql.AppendFormat(" {0} ", line.Split(new string[] { "--" }, StringSplitOptions.None)[0]);\n            // just the line as it is\n            else\n                sql.AppendFormat(" {0} ", line);\n        }\n        conn.Open();\n\n        SqlCommand cmd = new SqlCommand(sql.ToString(), conn);\n        cmd.ExecuteNonQuery();\n    }\n\n    return true;\n}\n</code></pre>\n'}, {'answer_id': 47066366, 'author': 'Yargo', 'author_id': 3748460, 'author_profile': 'https://Stackoverflow.com/users/3748460', 'pm_score': 1, 'selected': False, 'text': '<p>To avoid third parties, regexes, memory overheads and fast work with large scripts I created my own stream-based parser. It</p>\n\n<ul>\n<li>checks syntax before</li>\n<li><p>can recognize comments with -- or /**/</p>\n\n<pre><code>-- some commented text\n /*\ndrop table Users;\nGO\n   */\n</code></pre></li>\n<li><p>can recognize string literals with \' or "</p>\n\n<pre><code>set @s =\n    \'create table foo(...);\n    GO\n    create index ...\';\n</code></pre></li>\n<li>preserves LF and CR formatting</li>\n<li>preserves comments block in object bodies (stored procedures, views etc.)</li>\n<li><p>and other constructions such as</p>\n\n<pre><code>      gO -- commented text\n</code></pre></li>\n</ul>\n\n<h2>How to use</h2>\n\n<pre><code>    try\n    {\n        using (SqlConnection connection = new SqlConnection("Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=DATABASE-NAME;Data Source=SERVER-NAME"))\n        {\n            connection.Open();\n\n            int rowsAffected = SqlStatementReader.ExecuteSqlFile(\n                "C:\\\\target-sql-script.sql",\n                connection,\n                // Don\'t forget to use the correct file encoding!!!\n                Encoding.Default,\n                // Indefinitely (sec)\n                0\n            );\n        }\n    }\n    // implement your handlers\n    catch (SqlStatementReader.SqlBadSyntaxException) { }\n    catch (SqlException) { }\n    catch (Exception) { }\n</code></pre>\n\n<h2>Stream-based SQL script reader</h2>\n\n<pre><code>class SqlStatementReader\n{\n    public class SqlBadSyntaxException : Exception\n    {\n        public SqlBadSyntaxException(string description) : base(description) { }\n        public SqlBadSyntaxException(string description, int line) : base(OnBase(description, line, null)) { }\n        public SqlBadSyntaxException(string description, int line, string filePath) : base(OnBase(description, line, filePath)) { }\n        private static string OnBase(string description, int line, string filePath)\n        {\n            if (filePath == null)\n                return string.Format("Line: {0}. {1}", line, description);\n            else\n                return string.Format("File: {0}\\r\\nLine: {1}. {2}", filePath, line, description);\n        }\n    }\n\n    enum SqlScriptChunkTypes\n    {\n        InstructionOrUnquotedIdentifier = 0,\n        BracketIdentifier = 1,\n        QuotIdentifierOrLiteral = 2,\n        DblQuotIdentifierOrLiteral = 3,\n        CommentLine = 4,\n        CommentMultiline = 5,\n    }\n\n    StreamReader _sr = null;\n    string _filePath = null;\n    int _lineStart = 1;\n    int _lineEnd = 1;\n    bool _isNextChar = false;\n    char _nextChar = \'\\0\';\n\n    public SqlStatementReader(StreamReader sr)\n    {\n        if (sr == null)\n            throw new ArgumentNullException("StreamReader can\'t be null.");\n\n        if (sr.BaseStream is FileStream)\n            _filePath = ((FileStream)sr.BaseStream).Name;\n\n        _sr = sr;\n    }\n\n    public SqlStatementReader(StreamReader sr, string filePath)\n    {\n        if (sr == null)\n            throw new ArgumentNullException("StreamReader can\'t be null.");\n\n        _sr = sr;\n        _filePath = filePath;\n    }\n\n    public int LineStart { get { return _lineStart; } }\n    public int LineEnd { get { return _lineEnd == 1 ? _lineEnd : _lineEnd - 1; } }\n\n    public void LightSyntaxCheck()\n    {\n        while (ReadStatementInternal(true) != null) ;\n    }\n\n    public string ReadStatement()\n    {\n        for (string s = ReadStatementInternal(false); s != null; s = ReadStatementInternal(false))\n        {\n            // skip empty\n            for (int i = 0; i &lt; s.Length; i++)\n            {\n                switch (s[i])\n                {\n                    case \' \': continue;\n                    case \'\\t\': continue;\n                    case \'\\r\': continue;\n                    case \'\\n\': continue;\n                    default:\n                        return s;\n                }\n            }\n        }\n        return null;\n    }\n\n    string ReadStatementInternal(bool syntaxCheck)\n    {\n        if (_isNextChar == false &amp;&amp; _sr.EndOfStream)\n            return null;\n\n        StringBuilder allLines = new StringBuilder();\n        StringBuilder line = new StringBuilder();\n        SqlScriptChunkTypes nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;\n        SqlScriptChunkTypes currentChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;\n        char ch = \'\\0\';\n        int lineCounter = 0;\n        int nextLine = 0;\n        int currentLine = 0;\n        bool nextCharHandled = false;\n        bool foundGO;\n        int go = 1;\n\n        while (ReadChar(out ch))\n        {\n            if (nextCharHandled == false)\n            {\n                currentChunk = nextChunk;\n                currentLine = nextLine;\n\n                switch (currentChunk)\n                {\n                    case SqlScriptChunkTypes.InstructionOrUnquotedIdentifier:\n\n                        if (ch == \'[\')\n                        {\n                            currentChunk = nextChunk = SqlScriptChunkTypes.BracketIdentifier;\n                            currentLine = nextLine = lineCounter;\n                        }\n                        else if (ch == \'"\')\n                        {\n                            currentChunk = nextChunk = SqlScriptChunkTypes.DblQuotIdentifierOrLiteral;\n                            currentLine = nextLine = lineCounter;\n                        }\n                        else if (ch == \'\\\'\')\n                        {\n                            currentChunk = nextChunk = SqlScriptChunkTypes.QuotIdentifierOrLiteral;\n                            currentLine = nextLine = lineCounter;\n                        }\n                        else if (ch == \'-\' &amp;&amp; (_isNextChar &amp;&amp; _nextChar == \'-\'))\n                        {\n                            nextCharHandled = true;\n                            currentChunk = nextChunk = SqlScriptChunkTypes.CommentLine;\n                            currentLine = nextLine = lineCounter;\n                        }\n                        else if (ch == \'/\' &amp;&amp; (_isNextChar &amp;&amp; _nextChar == \'*\'))\n                        {\n                            nextCharHandled = true;\n                            currentChunk = nextChunk = SqlScriptChunkTypes.CommentMultiline;\n                            currentLine = nextLine = lineCounter;\n                        }\n                        else if (ch == \']\')\n                        {\n                            throw new SqlBadSyntaxException("Incorrect syntax near \']\'.", _lineEnd + lineCounter, _filePath);\n                        }\n                        else if (ch == \'*\' &amp;&amp; (_isNextChar &amp;&amp; _nextChar == \'/\'))\n                        {\n                            throw new SqlBadSyntaxException("Incorrect syntax near \'*\'.", _lineEnd + lineCounter, _filePath);\n                        }\n                        break;\n\n                    case SqlScriptChunkTypes.CommentLine:\n\n                        if (ch == \'\\r\' &amp;&amp; (_isNextChar &amp;&amp; _nextChar == \'\\n\'))\n                        {\n                            nextCharHandled = true;\n                            currentChunk = nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;\n                            currentLine = nextLine = lineCounter;\n                        }\n                        else if (ch == \'\\n\' || ch == \'\\r\')\n                        {\n                            currentChunk = nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;\n                            currentLine = nextLine = lineCounter;\n                        }\n                        break;\n\n                    case SqlScriptChunkTypes.CommentMultiline:\n\n                        if (ch == \'*\' &amp;&amp; (_isNextChar &amp;&amp; _nextChar == \'/\'))\n                        {\n                            nextCharHandled = true;\n                            nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;\n                            nextLine = lineCounter;\n                        }\n                        else if (ch == \'/\' &amp;&amp; (_isNextChar &amp;&amp; _nextChar == \'*\'))\n                        {\n                            throw new SqlBadSyntaxException("Missing end comment mark \'*/\'.", _lineEnd + currentLine, _filePath);\n                        }\n                        break;\n\n                    case SqlScriptChunkTypes.BracketIdentifier:\n\n                        if (ch == \']\')\n                        {\n                            nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;\n                            nextLine = lineCounter;\n                        }\n                        break;\n\n                    case SqlScriptChunkTypes.DblQuotIdentifierOrLiteral:\n\n                        if (ch == \'"\')\n                        {\n                            if (_isNextChar &amp;&amp; _nextChar == \'"\')\n                            {\n                                nextCharHandled = true;\n                            }\n                            else\n                            {\n                                nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;\n                                nextLine = lineCounter;\n                            }\n                        }\n                        break;\n\n                    case SqlScriptChunkTypes.QuotIdentifierOrLiteral:\n\n                        if (ch == \'\\\'\')\n                        {\n                            if (_isNextChar &amp;&amp; _nextChar == \'\\\'\')\n                            {\n                                nextCharHandled = true;\n                            }\n                            else\n                            {\n                                nextChunk = SqlScriptChunkTypes.InstructionOrUnquotedIdentifier;\n                                nextLine = lineCounter;\n                            }\n                        }\n                        break;\n                }\n            }\n            else\n                nextCharHandled = false;\n\n            foundGO = false;\n            if (currentChunk == SqlScriptChunkTypes.InstructionOrUnquotedIdentifier || go &gt;= 5 || (go == 4 &amp;&amp; currentChunk == SqlScriptChunkTypes.CommentLine))\n            {\n                // 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\n                switch (go)\n                {\n                    case 0:\n                        if (ch == \'\\r\' || ch == \'\\n\')\n                            go = 1;\n                        break;\n                    case 1:\n                        if (ch == \' \' || ch == \'\\t\')\n                            go = 2;\n                        else if (ch == \'G\' || ch == \'g\')\n                            go = 3;\n                        else if (ch != \'\\n\' &amp;&amp; ch != \'\\r\')\n                            go = 0;\n                        break;\n                    case 2:\n                        if (ch == \'G\' || ch == \'g\')\n                            go = 3;\n                        else if (ch == \'\\n\' || ch == \'\\r\')\n                            go = 1;\n                        else if (ch != \' \' &amp;&amp; ch != \'\\t\')\n                            go = 0;\n                        break;\n                    case 3:\n                        if (ch == \'O\' || ch == \'o\')\n                            go = 4;\n                        else if (ch == \'\\n\' || ch == \'\\r\')\n                            go = 1;\n                        else\n                            go = 0;\n                        break;\n                    case 4:\n                        if (ch == \'\\r\' &amp;&amp; (_isNextChar &amp;&amp; _nextChar == \'\\n\'))\n                            go = 5;\n                        else if (ch == \'\\n\' || ch == \'\\r\')\n                            foundGO = true;\n                        else if (ch == \' \' || ch == \'\\t\')\n                            go = 5;\n                        else if (ch == \'-\' &amp;&amp; (_isNextChar &amp;&amp; _nextChar == \'-\'))\n                            go = 6;\n                        else\n                            go = 0;\n                        break;\n                    case 5:\n                        if (ch == \'\\r\' &amp;&amp; (_isNextChar &amp;&amp; _nextChar == \'\\n\'))\n                            go = 5;\n                        else if (ch == \'\\n\' || ch == \'\\r\')\n                            foundGO = true;\n                        else if (ch == \'-\' &amp;&amp; (_isNextChar &amp;&amp; _nextChar == \'-\'))\n                            go = 6;\n                        else if (ch != \' \' &amp;&amp; ch != \'\\t\')\n                            throw new SqlBadSyntaxException("Incorrect syntax was encountered while parsing go.", _lineEnd + lineCounter, _filePath);\n                        break;\n                    case 6:\n                        if (ch == \'\\r\' &amp;&amp; (_isNextChar &amp;&amp; _nextChar == \'\\n\'))\n                            go = 6;\n                        else if (ch == \'\\n\' || ch == \'\\r\')\n                            foundGO = true;\n                        break;\n                    default:\n                        go = 0;\n                        break;\n                }\n            }\n            else\n                go = 0;\n\n            if (foundGO)\n            {\n                if (ch == \'\\r\' || ch == \'\\n\')\n                {\n                    ++lineCounter;\n                }\n                // clear GO\n                string s = line.Append(ch).ToString();\n                for (int i = 0; i &lt; s.Length; i++)\n                {\n                    switch (s[i])\n                    {\n                        case \' \': continue;\n                        case \'\\t\': continue;\n                        case \'\\r\': continue;\n                        case \'\\n\': continue;\n                        default:\n                            _lineStart = _lineEnd;\n                            _lineEnd += lineCounter;\n                            return allLines.Append(s.Substring(0, i)).ToString();\n                    }\n                }\n                return string.Empty;\n            }\n\n            // accumulate by string\n            if (ch == \'\\r\' &amp;&amp; (_isNextChar == false || _nextChar != \'\\n\'))\n            {\n                ++lineCounter;\n                if (syntaxCheck == false)\n                    allLines.Append(line.Append(\'\\r\').ToString());\n                line.Clear();\n            }\n            else if (ch == \'\\n\')\n            {\n                ++lineCounter;\n                if (syntaxCheck == false)\n                    allLines.Append(line.Append(\'\\n\').ToString());\n                line.Clear();\n            }\n            else\n            {\n                if (syntaxCheck == false)\n                    line.Append(ch);\n            }\n        }\n\n        // this is the end of the stream, return it without GO, if GO exists\n        switch (currentChunk)\n        {\n            case SqlScriptChunkTypes.InstructionOrUnquotedIdentifier:\n            case SqlScriptChunkTypes.CommentLine:\n                break;\n            case SqlScriptChunkTypes.CommentMultiline:\n                if (nextChunk != SqlScriptChunkTypes.InstructionOrUnquotedIdentifier)\n                    throw new SqlBadSyntaxException("Missing end comment mark \'*/\'.", _lineEnd + currentLine, _filePath);\n                break;\n            case SqlScriptChunkTypes.BracketIdentifier:\n                if (nextChunk != SqlScriptChunkTypes.InstructionOrUnquotedIdentifier)\n                    throw new SqlBadSyntaxException("Unclosed quotation mark [.", _lineEnd + currentLine, _filePath);\n                break;\n            case SqlScriptChunkTypes.DblQuotIdentifierOrLiteral:\n                if (nextChunk != SqlScriptChunkTypes.InstructionOrUnquotedIdentifier)\n                    throw new SqlBadSyntaxException("Unclosed quotation mark \\".", _lineEnd + currentLine, _filePath);\n                break;\n            case SqlScriptChunkTypes.QuotIdentifierOrLiteral:\n                if (nextChunk != SqlScriptChunkTypes.InstructionOrUnquotedIdentifier)\n                    throw new SqlBadSyntaxException("Unclosed quotation mark \'.", _lineEnd + currentLine, _filePath);\n                break;\n        }\n\n        if (go &gt;= 4)\n        {\n            string s = line.ToString();\n            for (int i = 0; i &lt; s.Length; i++)\n            {\n                switch (s[i])\n                {\n                    case \' \': continue;\n                    case \'\\t\': continue;\n                    case \'\\r\': continue;\n                    case \'\\n\': continue;\n                    default:\n                        _lineStart = _lineEnd;\n                        _lineEnd += lineCounter + 1;\n                        return allLines.Append(s.Substring(0, i)).ToString();\n                }\n            }\n        }\n\n        _lineStart = _lineEnd;\n        _lineEnd += lineCounter + 1;\n        return allLines.Append(line.ToString()).ToString();\n    }\n\n    bool ReadChar(out char ch)\n    {\n        if (_isNextChar)\n        {\n            ch = _nextChar;\n            if (_sr.EndOfStream)\n                _isNextChar = false;\n            else\n                _nextChar = Convert.ToChar(_sr.Read());\n            return true;\n        }\n        else if (_sr.EndOfStream == false)\n        {\n            ch = Convert.ToChar(_sr.Read());\n            if (_sr.EndOfStream == false)\n            {\n                _isNextChar = true;\n                _nextChar = Convert.ToChar(_sr.Read());\n            }\n            return true;\n        }\n        else\n        {\n            ch = \'\\0\';\n            return false;\n        }\n    }\n\n    public static int ExecuteSqlFile(string filePath, SqlConnection connection, Encoding fileEncoding, int commandTimeout)\n    {\n        int rowsAffected = 0;\n        using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read))\n        {\n            // Simple syntax check (you can comment out these two lines below)\n            new SqlStatementReader(new StreamReader(fs, fileEncoding)).LightSyntaxCheck();\n            fs.Seek(0L, SeekOrigin.Begin);\n\n            // Read statements without GO\n            SqlStatementReader rd = new SqlStatementReader(new StreamReader(fs, fileEncoding));\n            string stmt;\n            while ((stmt = rd.ReadStatement()) != null)\n            {\n                using (SqlCommand cmd = connection.CreateCommand())\n                {\n                    cmd.CommandText = stmt;\n                    cmd.CommandTimeout = commandTimeout;\n                    int i = cmd.ExecuteNonQuery();\n                    if (i &gt; 0)\n                        rowsAffected += i;\n                }\n            }\n        }\n        return rowsAffected;\n    }\n}\n</code></pre>\n'}, {'answer_id': 52443620, 'author': 'Filip Cordas', 'author_id': 6330636, 'author_profile': 'https://Stackoverflow.com/users/6330636', 'pm_score': 3, 'selected': False, 'text': '<p>I look at this a few times at the end decided with <a href="https://github.com/aspnet/EntityFramework6/blob/6.1.3/src/EntityFramework.SqlServer/SqlServerMigrationSqlGenerator.cs#L1494" rel="noreferrer">EF implementation</a>\nA bit modified for <code>SqlConnection</code></p>\n\n<pre><code>public static void ExecuteSqlScript(this SqlConnection sqlConnection, string sqlBatch)\n        {\n            // Handle backslash utility statement (see http://technet.microsoft.com/en-us/library/dd207007.aspx)\n            sqlBatch = Regex.Replace(sqlBatch, @"\\\\(\\r\\n|\\r|\\n)", string.Empty);\n\n            // Handle batch splitting utility statement (see http://technet.microsoft.com/en-us/library/ms188037.aspx)\n            var batches = Regex.Split(\n                sqlBatch,\n                string.Format(CultureInfo.InvariantCulture, @"^\\s*({0}[ \\t]+[0-9]+|{0})(?:\\s+|$)", BatchTerminator),\n                RegexOptions.IgnoreCase | RegexOptions.Multiline);\n\n            for (int i = 0; i &lt; batches.Length; ++i)\n            {\n                // Skip batches that merely contain the batch terminator\n                if (batches[i].StartsWith(BatchTerminator, StringComparison.OrdinalIgnoreCase) ||\n                    (i == batches.Length - 1 &amp;&amp; string.IsNullOrWhiteSpace(batches[i])))\n                {\n                    continue;\n                }\n\n                // Include batch terminator if the next element is a batch terminator\n                if (batches.Length &gt; i + 1 &amp;&amp;\n                    batches[i + 1].StartsWith(BatchTerminator, StringComparison.OrdinalIgnoreCase))\n                {\n                    int repeatCount = 1;\n\n                    // Handle count parameter on the batch splitting utility statement\n                    if (!string.Equals(batches[i + 1], BatchTerminator, StringComparison.OrdinalIgnoreCase))\n                    {\n                        repeatCount = int.Parse(Regex.Match(batches[i + 1], @"([0-9]+)").Value, CultureInfo.InvariantCulture);\n                    }\n\n                    for (int j = 0; j &lt; repeatCount; ++j)\n                    {\n                       var command = sqlConnection.CreateCommand();\n                       command.CommandText = batches[i];\n                       command.ExecuteNonQuery();\n                    }\n                }\n                else\n                {\n                    var command = sqlConnection.CreateCommand();\n                    command.CommandText = batches[i];\n                    command.ExecuteNonQuery();\n                }\n            }\n        }\n</code></pre>\n'}, {'answer_id': 55621284, 'author': 'Sprot', 'author_id': 2960937, 'author_profile': 'https://Stackoverflow.com/users/2960937', 'pm_score': -1, 'selected': False, 'text': '<p>For anyone still having the problem. You could use official Microsoft SMO </p>\n\n<p><a href="https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/overview-smo?view=sql-server-2017" rel="nofollow noreferrer">https://learn.microsoft.com/en-us/sql/relational-databases/server-management-objects-smo/overview-smo?view=sql-server-2017</a></p>\n\n<pre><code>using (var connection = new SqlConnection(connectionString))\n{\n  var server = new Server(new ServerConnection(connection));\n  server.ConnectionContext.ExecuteNonQuery(sql);\n}\n</code></pre>\n'}, {'answer_id': 66628734, 'author': 'Ahmed Suror', 'author_id': 1655837, 'author_profile': 'https://Stackoverflow.com/users/1655837', 'pm_score': 0, 'selected': False, 'text': "<p>You can just use <code>;</code> at the end of each statement as it worked for me.\nReally don't know if there are any drawbacks to it.</p>\n"}, {'answer_id': 72480033, 'author': 'adamency', 'author_id': 16974218, 'author_profile': 'https://Stackoverflow.com/users/16974218', 'pm_score': 0, 'selected': False, 'text': "<p>Here is the most elegant solution I could find.</p>\n<p>Let's say your SQL script containing <code>GO</code> statements is in a file <code>script.sql</code>.</p>\n<p>You can do:</p>\n<pre><code>$query = ((Get-Content -Raw &quot;script.sql&quot;) -replace '([\\s\\n]*)GO([\\s\\n]+)','$1$2')\n</code></pre>\n<p>This will remove all GO statements without removing other occurences of the string <code>go</code> anywhere else (according to my tests).</p>\n<p>You can then do:</p>\n<pre><code>$SqlConnection = New-Object System.Data.SqlClient.SqlConnection                \n$SqlCmd = New-Object System.Data.SqlClient.SqlCommand\n\n$SqlConnection.ConnectionString = &lt;your_connection_string&gt;\n\n$SqlConnection.Open()\n   \n$SqlCmd.Connection = $SqlConnection \n$SqlCmd.CommandText = $query\n$SqlCmd.ExecuteNonQuery()\n</code></pre>\n<p>which should normally work without issues.</p>\n"}]
['https://Stackoverflow.com/questions/40814', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/369/']