public static RunScriptResult RunScript(TextWriter output, TextWriter error, SqlConnection connection,
string scriptFilePath, RunScriptOptions options, out int scriptFileRowsAffected)
{
if (string.IsNullOrEmpty(scriptFilePath))
{
throw new ArgumentException("The script file path must be specified.", "scriptFilePath");
}
if (!File.Exists(scriptFilePath))
{
throw new FileNotFoundException("SQL script file '" + scriptFilePath + "' does not exist.", scriptFilePath);
}
using (var reader = new StreamReader(scriptFilePath))
{
return(RunScript(output, error, connection, reader, scriptFilePath, options,
out scriptFileRowsAffected));
}
}
/// <summary>
/// Run the commands in an SQL script file.
/// </summary>
/// <param name="output">TextWriter for informational output.</param>
/// <param name="error">TextWriter for error output.</param>
/// <param name="connection">An open connection to run the scripts against.</param>
/// <param name="script">The script to run.</param>
/// <param name="scriptFilePath">The name and path of the script, used for error reporting purposes and script path substitution.</param>
/// <param name="options">Script execution options.</param>
/// <param name="scriptFileRowsAffected">The total number of rows affected by all commands in the script.</param>
/// <returns>True if execution completed successfully, false if errors occurred.</returns>
public static RunScriptResult RunScript(TextWriter output, TextWriter error, SqlConnection connection,
TextReader script, string scriptFilePath, RunScriptOptions options, out int scriptFileRowsAffected)
{
scriptFileRowsAffected = 0;
string[] commands = ReadDatabaseScript(script);
if (commands.IsNullOrEmpty())
{
output.WriteLine("\tExecuting '" + scriptFilePath + "' - it contains no commands.");
return(RunScriptResult.Skipped);
}
int?scriptRecordId = null;
if (options.IsFlagSet(RunScriptOptions.UseDatabaseScriptRecords))
{
string userMessage;
bool shouldRun = DbScriptRegister.CheckAndAddScriptRecord(connection, scriptFilePath, commands,
out scriptRecordId, out userMessage);
if (userMessage != null)
{
output.WriteLine("\t" + userMessage);
}
if (!shouldRun)
{
return(RunScriptResult.Skipped);
}
}
// Relpace $(LINKME_SCRIPT_PATH) variable with the actual script path.
// This syntax is used for compatibility with SqlCmd utility.
if (!string.IsNullOrEmpty(scriptFilePath))
{
output.WriteLine("\tExecuting '" + scriptFilePath + "'...");
string scriptFolder = Path.GetDirectoryName(Path.GetFullPath(scriptFilePath));
for (int i = 0; i < commands.Length; i++)
{
commands[i] = commands[i].Replace("$(LINKME_SCRIPT_PATH)", scriptFolder);
}
}
else
{
for (int i = 0; i < commands.Length; i++)
{
if (commands[i].IndexOf("$(LINKME_SCRIPT_PATH)") != -1)
{
throw new ArgumentException("scriptFileName must be specified when the script"
+ " contains $(LINKME_SCRIPT_PATH) placeholders.", "scriptFilePath");
}
}
}
bool success = true;
for (int i = 0; i < commands.Length; i++)
{
using (var command = new SqlCommand(commands[i], connection))
{
command.CommandTimeout = 0; // No timeout. Some of our scripts run for a very long time!
int rowsAffected = 0;
try
{
rowsAffected = command.ExecuteNonQuery();
}
catch (SqlException ex)
{
if (scriptFilePath == null)
{
error.WriteLine("{0}SQL command {1} failed. Command text:{0}{2}{0}{0}SQL error message:{0}{3}",
System.Environment.NewLine, i + 1, commands[i], ex.Message);
}
else
{
error.WriteLine("{0}SQL command {1} in file '{4}' failed. Command text:{0}{2}"
+ "{0}{0}SQL error message:{0}{3}",
System.Environment.NewLine, i + 1, commands[i], ex.Message, scriptFilePath);
}
success = false;
if (!options.IsFlagSet(RunScriptOptions.ContinueOnError))
{
return(RunScriptResult.Failed);
}
}
if (rowsAffected > 0)
{
scriptFileRowsAffected += rowsAffected;
}
}
}
output.Write("\tDone.");
if (scriptFileRowsAffected > 0)
{
output.Write(" {0} row{1} affected.", scriptFileRowsAffected,
(scriptFileRowsAffected == 1 ? "" : "s"));
}
output.WriteLine();
if (success && scriptRecordId.HasValue)
{
DbScriptRegister.MarkScriptSuccessful(connection, scriptRecordId.Value);
}
return(success ? RunScriptResult.Succeeded : RunScriptResult.Failed);
}
