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
}
}
posted by Garth at 1:56 PM
|
11 comments
