This is what the code would look like to OriginalGriff's solution. I wrote this a couple months ago to do basically what you are doing. It is designed to handle scripts that have the
GO
statement.
using System;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
namespace SqlCommandExecutor
{
class Program
{
static void Main(string[] args)
{
string sqlConnectionString = @"Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=master;Data Source=localhost";
Console.WriteLine("Loading scripts...");
StreamReader createDatabaseScriptStreamReader = File.OpenText("CreateDatabase.sql");
StreamReader loadDataScriptStreamReader = File.OpenText("LoadDatabase.sql");
using (SqlConnection sqlConnection = new SqlConnection(sqlConnectionString))
{
sqlConnection.Open();
ExecuteScript(createDatabaseScriptStreamReader, sqlConnection);
ExecuteScript(loadDataScriptStreamReader, sqlConnection);
sqlConnection.Close();
}
Console.WriteLine("Finished.");
}
private static void ExecuteScript(StreamReader createDatabaseScriptStreamReader, SqlConnection sqlConnection)
{
StringBuilder stringBuilder = new StringBuilder();
while (!createDatabaseScriptStreamReader.EndOfStream)
{
string line = createDatabaseScriptStreamReader.ReadLine();
if (line == "GO")
{
try
{
string command = stringBuilder.ToString();
string message;
if (command.Length > 15)
message = command.Substring(0, 15);
else
message = command;
message = message.Trim();
Console.WriteLine("Executing command \"" + message + "...\"");
SqlCommand sqlCommand = new SqlCommand(command, sqlConnection);
sqlCommand.ExecuteNonQuery();
}
catch (Exception ex)
{
Console.WriteLine();
Console.BackgroundColor = ConsoleColor.Red;
Console.ForegroundColor = ConsoleColor.White;
Console.WriteLine(ex.Message);
Console.ResetColor();
Console.WriteLine();
}
stringBuilder = new StringBuilder();
}
else
{
stringBuilder.AppendLine(line);
}
}
}
}
}