You can use ADO.NET. This way you don't have to depend on sqlcmd.exe.
Here is a very helpful class to handle your scripts (with GO, :setvar, $(MyVar), etc).
http://bitmugger.blogspot.com/2008_04_01_archive.html
I have been using a slightly modified version in production for some months now without problems. Here is my version (can't remember what I changed - maybe added some supported commands).
using System;
using System.Collections.Generic;
using System.Text;
using System.Data.SqlClient;
namespace GR.WIX
{
/// <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.
///
///
///
/// 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 their 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
{
#region Fields
private readonly Dictionary<string, string> variables;
private readonly List<string> lockedVariables;
private ErrorMode errorMode;
private readonly SqlConnection connection;
private readonly List<string> ignoredCommands;
private bool allowVariableOverwrites;
#endregion Fields
#region Properties
/// <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; }
}
#endregion Properties
#region Constructor
/// <summary>
/// Initializes a new instance of the <see cref="ExecuteSqlCmdMode"/> class.
/// </summary>
/// <param name="sqlConnection">The SQL conn.</param>
public ExecuteSqlCmdMode(SqlConnection sqlConnection)
{
// Check for legal values
if (sqlConnection == null)
{
throw new Exception("connection cannot be null");
}
// Set connection variable from supplied SQLConnection.
connection = sqlConnection;
// Load up the script variables.
variables = new Dictionary<string, string>();
variables.Add("SQLCMDUSER", "");
variables.Add("SQLCMDPASSWORD", "");
variables.Add("SQLCMDSERVER", sqlConnection.DataSource);
variables.Add("SQLCMDWORKSTATION", sqlConnection.WorkstationId);
variables.Add("SQLCMDDBNAME", sqlConnection.Database);
variables.Add("SQLCMDLOGINTIMEOUT", sqlConnection.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;
}
#endregion Constructor
/// <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(string.Format("Variable name {0} cannot be blank or contain spaces", variableName));
}
// 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 to execute.</param>
public List<Exception> Execute(string scriptToExecute)
{
var exceptions = new List<Exception>();
var queryBlock = new StringBuilder();
connection.Open();
var scriptLines = (scriptToExecute.Replace(Environment.NewLine, "\n") + "\nGO\n").Split('\n');
// Loop each line in the script
for (var i = 0; i < scriptLines.GetUpperBound(0); i++)
{
// Prepare a specially modified version of the line for checking for commands.
var ucaseLine = scriptLines[i].Replace("\t", " ").Trim().ToUpper() + " ";
// See if it's one of the commands to be ignored.
if (ignoredCommands.Contains(ucaseLine.Split(' ')[0]))
{
// Just ignore this line.
}
else if (ucaseLine.StartsWith("GO "))
{
// We have a GO line (everything after GO on the line is ignored). Execute the block
// we have gathered so far.
ExecuteBlock(queryBlock, exceptions);
// After a GO command, we reset our query.
queryBlock = new StringBuilder();
}
else if (ucaseLine.StartsWith(":SETVAR "))
{
// We have found a SetVar line. Add (or update) the variable and its value to our list.
SetVariableValue(scriptLines[i]);
}
else if (ucaseLine.StartsWith(":ON ERROR "))
{
// Handle :on error.
HandleOnErrorCommand(i, ucaseLine);
}
else if (ucaseLine.StartsWith(":R "))
{
// TODO: Handle this case.
}
else
{
// Regular SQL Line to have variables replaced on then added to SQLCmd for execution.
// Replace variables with its value for the line (if any).
var noVariableVersion = ReplaceVariablesWithValue(scriptLines[i]);
// Add it to the current block of code to execute.
queryBlock.AppendLine(noVariableVersion);
}
}
return exceptions;
}
private string ReplaceVariablesWithValue(string temp)
{
if (temp.Length > 4 && temp.Contains("$("))
{
// Loop each variable to check the line for.
foreach (var keyPair in variables)
{
var searchFor = string.Format("$({0})", keyPair.Key);
var begPos = temp.ToUpper().IndexOf(searchFor);
while (begPos >= 0)
{
// Make the variable substitution
var 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));
}
}
}
return temp;
}
private void ExecuteBlock(StringBuilder sqlCommand, List<Exception> exceptions)
{
try
{
if (sqlCommand.Length > 0)
{
// Attempt the SQL command.
using (var sqlComm = new SqlCommand(sqlCommand.ToString(), connection))
{
sqlComm.CommandTimeout = 120;
sqlComm.ExecuteNonQuery();
}
}
}
catch (Exception ex)
{
if (errorMode != ErrorMode.ErrIgnore)
{
throw new Exception("Error executing " + sqlCommand, ex);
}
exceptions.Add(new Exception("Error executing " + sqlCommand, ex));
}
}
private void HandleOnErrorCommand(int i, string ucaseLine)
{
var 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));
}
}
private void SetVariableValue(string scriptLine)
{
var temp = scriptLine.Trim().Substring(8, scriptLine.Trim().Length - 8);
var begPos = temp.IndexOf(" ");
var varName = temp.Substring(0, begPos).Trim().ToUpper();
var 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 the following line {0}.", scriptLine));
}
if (variables.ContainsKey(varName))
{
if (!lockedVariables.Contains(varName))
{
variables[varName] = varValue;
}
}
else
{
variables.Add(varName, varValue);
}
}
}
/// <summary>
/// Legal values for the error mode
/// Error mode controls what happens when a SQL Error occurs
/// </summary>
public enum ErrorMode
{
ErrExit,
ErrIgnore
}
}
Use it like this:
/// <summary>
/// Executes the SQL script.
/// </summary>
/// <param name="serverName">Name of the server.</param>
/// <param name="scriptPath">The path of the script to execute.</param>
/// <param name="variables">The variables.</param>
/// <returns></returns>
private static void ExecuteSqlScript(string serverName, string scriptPath, Dictionary<string, string> variables)
{
using (var connection = new SqlConnection(string.Format(SqlConnectionFormat, serverName)))
{
var mode = new ExecuteSqlCmdMode(connection);
// Add variables.
foreach (var variable in variables)
{
mode.SetVariable(variable.Key, variable.Value);
}
mode.Execute(FileToString(scriptPath));
}
}
