Use .NET To Run SQL Server Installation Scripts With GO Statements

Here is a brief sample showing how to use the Microsoft.SqlServer.Management.dll to execute complex SQL scripts with GO statements. These are often used during database installs and/or upgrades. Be sure to add a project reference for Microsoft.SqlServer.ConnectionInfo, Microsoft.SqlServer, Microsoft.SqlServer.Management, Microsoft.SqlServer.Smo, and Microsoft.SqlServer.Enum.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.IO;
using System.Reflection;
using System.Data.SqlClient;
using System.Diagnostics;
using Microsoft.SqlServer.Management.Smo;
using Microsoft.SqlServer.Management.Common;

public void ExecSql(string sql, string connectionString,string dataBaseNameToPrepend)
{

   if (String.IsNullOrEmpty(sql)) return;

   sql = sql.Trim();

   //  There is a maximum string length the Smo objects will process.
if (sql.Length > 20000) msg = sql.Substring(0,20000);

if (!String.IsNullOrEmpty(dataBaseNameToPrepend)) sql = "USE ["+ dataBaseNameToPrepend.Trim() + "]\nGO\n" + sql;

using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();

var server = new Server(new ServerConnection(conn));

server.ConnectionContext.ExecuteNonQuery(sql);
server.ConnectionContext.Disconnect();

}

}