Execute SQLCMD scripts via a class in C#

Here's a utility class I put together so I could take the output scripts from a Visual Studio 2008 database project and execute them from code without calling SQLCMD.EXE. The obstacle to direct execution of the scripts is they contain many instances of syntax only available through SQLCMD. To my knowledge there's no direct ADO.NET way to use SQLCMD syntax in scripts.

Note: You may even find this useful for any SQL Scripts simply because it supports the GO statement that typical ADO.Net does not.

My class supports several of the most import SQLCMD Syntax bits and gracefully ignores the one's it doesn't support. It's not a perfect replacement for SQLCMD but gets the job done. This class has not been extensively tested.

using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
using System.Net;

namespace MyNameSpace
{

  
    /// <summary>
    /// Supports running a SQLCmd Mode style statement such as the output from a VS 2008 Database Team Edition Database Project
    /// Only a limited subset of the SQLCmd mode syntax is supported. Other gotchas.
    ///
    /// Uses a database transaction; if anything fails inside a On Error Exit group then the entire script is rolled back
    ///
    ///
    /// Supported Commands:
    ///     GO (note GO [N] will only be executed once not N times)
    ///     :setvar
    ///     $(MyVar)
    ///     :on error exit
    ///     :on error resume (only for SQL Errors)
    ///     :on error ignore (only for SQL Errors)
    ///
    /// The Following SQLCMD Commands are recognized but ignored. They will not crash your script if encountered but will be skipped
    ///     :ED
    ///     :Error
    ///     :!!
    ///     :Perftrace
    ///     :Quit
    ///     :Exit
    ///     :Help
    ///     :XML
    ///     :r
    ///     :ServerList
    ///     :Listvar
    ///
    /// The following SQLCMD pre-defined variables are pre-defined by this class just like they are by SQLCMD
    /// The only difference is SQLCMD actually used and/or updated these variable. This class simply has them predefined
    /// with much the same values as SQLCMD did. The class allows you to change ALL variables (unlike SQLCMD) where some are
    /// read only.
    ///     SQLCMDUSER ""
    ///     SQLCMDPASSWORD
    ///     SQLCMDSERVER {Server Name}
    ///     SQLCMDWORKSTATION {Computer Name}
    ///     SQLCMDLOGINTIMEOUT {Connection Timeout}
    ///     SQLCMDDBNAME {Database Name}
    ///     SQLCMDHEADERS "0"
    ///     SQLCMDCOLSEP " "
    ///     SQLCMDCOLWIDTH "0"
    ///     SQLCMDPACKETSIZE "4096"
    ///     SQLCMDERRORLEVEL "0"
    ///     SQLCMDMAXVARTYPEWIDTH "256"
    ///     SQLCMDMAXFIXEDTYPEWIDTH "0"
    ///     SQLCMDEDITOR "edit.com"
    ///     SQLCMDINI ""
    ///
    /// The following pre-defnined variables ARE used by the class and thier values when set are not ignored
    ///     SQLCMDSTATTIMEOUT "0"
    ///   
    /// One Additional Variable is defined so that scripts could potentially detect they are running in this class instead
    /// of SQLCmd.
    ///     SQLCMDREAL "0"
    /// </summary>
    public class ExecuteSQLCmdMode
    {
        private Dictionary<string, string> _variables; // Hidden
        private List<string> _lockedVariables; // Hidden
        private errorMode _errorMode; // Hidden
        private SqlConnection _sqlConn; // Hidden
        private List<string> _ignoredCommands;  // Hidden
        private bool _allowVariableOverwrites;

        /// <summary>
        /// Initializes a new instance of the <see cref="ExecuteSQLCmdMode"/> class.
        /// </summary>
        /// <param name="sqlConn">The SQL conn.</param>
        public ExecuteSQLCmdMode(SqlConnection sqlConn)
        {
            // Check for legal values
            if (sqlConn == null)
                throw new Exception("sqlConn cannot be null");

            // Set connection variable from supplied SQLConnection
            _sqlConn = sqlConn;

            // Load up the script variables
            _variables = new Dictionary<string, string>();
            _variables.Add("SQLCMDUSER", "");
            _variables.Add("SQLCMDPASSWORD", "");
            _variables.Add("SQLCMDSERVER", sqlConn.DataSource);
            _variables.Add("SQLCMDWORKSTATION", sqlConn.WorkstationId);
            _variables.Add("SQLCMDDBNAME", sqlConn.Database);
            _variables.Add("SQLCMDLOGINTIMEOUT", sqlConn.ConnectionTimeout.ToString());
            _variables.Add("SQLCMDSTATTIMEOUT", "0");
            _variables.Add("SQLCMDHEADERS", "0");
            _variables.Add("SQLCMDCOLSEP", "");
            _variables.Add("SQLCMDCOLWIDTH", "0");
            _variables.Add("SQLCMDPACKETSIZE", "4096");
            _variables.Add("SQLCMDERRORLEVEL", "0");
            _variables.Add("SQLCMDMAXVARTYPEWIDTH", "256");
            _variables.Add("SQLCMDMAXFIXEDTYPEWIDTH", "0");
            _variables.Add("SQLCMDEDITOR", "edit.com");
            _variables.Add("SQLCMDINI", "");
            _variables.Add("SQLCMDREAL", "0");

            // Setup pre-locked variables
            _lockedVariables = new List<string>();
            _lockedVariables.Add("SQLCMDREAL");

            // Setup the list of commands to be ignored
            _ignoredCommands = new List<string>();
            _ignoredCommands.Add(":ED");
            _ignoredCommands.Add(":ERROR");
            _ignoredCommands.Add(":!!");
            _ignoredCommands.Add(":PERFTRACE");
            _ignoredCommands.Add(":QUIT");
            _ignoredCommands.Add(":EXIT");
            _ignoredCommands.Add(":HELP");
            _ignoredCommands.Add(":XML");
            _ignoredCommands.Add(":R");
            _ignoredCommands.Add(":SERVERLIST");
            _ignoredCommands.Add(":LISTVAR");

            // Some other misc values
            _errorMode = errorMode.errExit;
            _allowVariableOverwrites = false;

        }

        /// <summary>
        /// Gets or sets a value indicating whether to allow variable overwrites.
        /// If True then even though a variable is specified externally it may
        /// be overwritten by :SetVar in the script. If False then the reverse
        /// variables specified externally superscede :setvar
        /// Default = false
        /// </summary>
        /// <value>true if allow variable overwrites; otherwise, false.</value>
        public bool AllowVariableOverwrites
        {
            get { return _allowVariableOverwrites; }
            set { _allowVariableOverwrites = value; }
        }

        /// <summary>
        /// Sets a variable in advance of script execution.
        /// </summary>
        /// <param name="variableName">Name of the variable.</param>
        /// <param name="variableValue">The variable value.</param>
        public void SetVariable(string variableName, string variableValue)
        {
            variableName = variableName.Trim().ToUpper();
            if (variableName.Length == 0  variableName.Contains(" "))
                throw new Exception("Variable name cannot be blank or contain spaces");

            // See if we already have this variable
            if (_variables.ContainsKey(variableName))
                _variables[variableName] = variableValue;
            else
            {
                _variables.Add(variableName, variableValue);

                if (!_allowVariableOverwrites)
                    _lockedVariables.Add(variableName);

            }

        }

        /// <summary>
        /// Executes the specified SQL script.
        /// </summary>
        /// <param name="scriptToExecute">The SQL script.</param>
        public List<Exception> Execute(string scriptToExecute)
        {
            List<Exception> exceptions = new List<Exception>();
            StringBuilder sqlCmd = new StringBuilder();
            int begPos;
            int endPos;
            string temp;
            string[] SQLScript;
            SqlTransaction trx = null;
          
            _sqlConn.Open();
            trx = _sqlConn.BeginTransaction(System.Data.IsolationLevel.ReadCommitted);

            try
            {
                SQLScript = (scriptToExecute.Replace(Environment.NewLine, "\n") + "\nGO\n").Split('\n');

                // Loop each line in the script
                for (int i = 0; i < SQLScript.GetUpperBound(0); i++)
                {
                    // Prepare a specially modified version of the line for checking for commands
                    string ucaseLine = SQLScript[i].Replace("\t", " ").Trim().ToUpper() + " ";

                    // See if it's one of the commands to be ignored
                    if (!_ignoredCommands.Contains((ucaseLine).Split(' ')[0]))
                    {
                        // See if we have a GO line (everything after GO on the line is ignored)
                        if (ucaseLine.StartsWith("GO "))
                        {
                            try
                            {
                                if (sqlCmd.Length > 0)
                                {
                                    // Attempt the SQL command
                                    SqlCommand sqlComm = new SqlCommand(sqlCmd.ToString(), _sqlConn, trx);
                                    sqlComm.ExecuteNonQuery();
                                }
                            }
                            catch (Exception ex)
                            {
                                if (_errorMode != errorMode.errIgnore)
                                    throw new Exception("Error executing " + sqlCmd.ToString(), ex);
                                else
                                    exceptions.Add(new Exception("Error executing " + sqlCmd.ToString(), ex));
                            }

                            // Reset the SQL Command
                            sqlCmd = new StringBuilder();

                        }

                        // Handle :SetVar MyVar "MyValue"
                        else if (ucaseLine.StartsWith(":SETVAR "))
                        {
                            temp = SQLScript[i].Trim().Substring(8, SQLScript[i].Trim().Length - 8);
                            begPos = temp.IndexOf(" ");

                            string varName = temp.Substring(0, begPos).Trim().ToUpper();
                            string varValue = temp.Substring(begPos + 1, temp.Length - begPos - 1).Trim();
                            if (varValue.StartsWith("\"") && varValue.EndsWith("\""))
                                varValue = varValue.Substring(1, varValue.Length - 2);
                            else
                                throw new Exception(string.Format("Improperly formatted :SetVar on line {0}.", i));

                            if (_variables.ContainsKey(varName))
                            {
                                if (!_lockedVariables.Contains(varName))
                                    _variables[varName] = varValue;
                            }
                            else
                                _variables.Add(varName, varValue);

                        }

                        // Handle :on error
                        else if (ucaseLine.StartsWith(":ON ERROR "))
                        {
                            temp = ucaseLine.Substring(10, ucaseLine.Length - 10).Trim();
                            if (temp == "EXIT")
                                _errorMode = errorMode.errExit;
                            else if (temp == "RESUME"  temp == "IGNORE")
                                _errorMode = errorMode.errIgnore;
                            else
                                throw new Exception(string.Format("Unknown On Error mode '{0}' on line {1}", temp, i));
                        }

                        // Regular SQL Line to have variables replaced on then added to SQLCmd for execution
                        else
                        {
                            temp = SQLScript[i];

                            // Quick check to see if there's any possibility of variables in the line of SQL
                            if (temp.Length > 4 && temp.Contains("$("))
                            {
                                // Loop each variable to check the line for
                                foreach (KeyValuePair<string, string> keyPair in _variables)
                                {
                                    string SearchFor = string.Format("$({0})", keyPair.Key);
                                    begPos = temp.ToUpper().IndexOf(SearchFor);
                                    while (begPos >= 0)
                                    {
                                        // Make the variable substitution
                                        endPos = begPos + SearchFor.Length;
                                        temp = temp.Substring(0, begPos) + keyPair.Value + temp.Substring(endPos, temp.Length - endPos);

                                        // Calculate a new begPos
                                        begPos = temp.ToUpper().IndexOf(string.Format(SearchFor));
                                    }
                                }
                            }

                            sqlCmd.AppendLine(temp);
                        }

                    } // If !IgnoreCommand
                } // for ...

                // All is well so commit the transaction
                trx.Commit();

            } // Try
            catch (Exception ex)
            {
                // Rollback transaction
                if (trx != null)
                    trx.Rollback();

                throw ex;


            }

            return exceptions;

        } // Execute
    } // Class

    /// <summary>
    /// Legal values for the error mode
    /// Error mode controls what happens when a SQL Error occurs
    /// </summary>
    public enum errorMode
    {
        errExit,
        errIgnore
    }
}