Handle and Run SQL Scripts With GO Statement in ADO.NET

Ever encountered error when trying to run a SQL script file (.sql) with "GO" statements using ADO.NET SqlCommand.ExecuteNonQuery? The error occurs because "GO" statement is not a valid Transact-SQL statement.

According to MSDN,

GO is not a Transact-SQL statement; it is a command recognized by the sqlcmd and osql utilities and SQL Server Management Studio Code editor.

To run the SQL script file using ADO.NET, there are 3 methods. The first method is to remove all "GO" statement in the file before running them in ADO.NET. Second method is to perform a split (tokenize) the file content using "GO" as the delimiter. The third method is to make use of SQL Server Management Objects (SMO).

The below is a code snippet applying the third method - Using SMO. Do take note that references to Microsoft.SqlServer.Smo.dll and Microsoft.SqlServer.ConnectionInfo.dll are required.

using System;
using System.IO;
using System.Data.SqlClient;
using System.Collections.Generic;

using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

public class RunSQLScriptUsingSMO
{
  public static void Main()
  {
    string sqlFile = @"C:\script.sql";
    string connectionString = "Data Source=COM\InstanceName;Initial Catalog=TEST;Integrated Security=True";
    
    StreamReader sr = new StreamReader(sqlFile);
    string sql = sr.ReadToEnd();
    
    SqlConnection connection = new SqlConnection(connectionString);
    Server server = new Server(new ServerConnection(connection));
    server.ConnectionContext.ExecuteNonQuery(script);
  }
}