/// <summary>
/// Load scripts from the file system.
/// </summary>
/// <param name="scriptDirectory">The script directory.</param>
/// <returns></returns>
public static ScriptSet LoadScripts(string scriptDirectory)
{
ScriptSet scripts = new ScriptSet();
LoadScripts(new DirectoryInfo(scriptDirectory), scripts, scriptDirectory);
return(scripts);
}
/// <summary>
/// Append another script to this one
/// </summary>
/// <param name="adding">The script to append</param>
public void Append(ScriptSet adding)
{
foreach (Script script in adding ?? new ScriptSet())
{
Append(script);
}
}
/// <summary>
/// Load scripts from the file system.
/// </summary>
/// <param name="scriptDirectory">The script directory.</param>
/// <param name="scripts">The set of scripts to populate.</param>
/// <param name="baseDirectory">The base script directory.</param>
private static void LoadScripts(DirectoryInfo scriptDirectory, ScriptSet scripts, string baseDirectory)
{
if (scriptDirectory.Name == ".svn")
{
RunOptions.Current.Logger.Log("Ignoring " + scriptDirectory.FullName, OutputLevel.Reads);
return;
}
//load each script in turn
foreach (FileInfo file in scriptDirectory.GetFiles())
{
bool parse = true;
foreach (string pattern in RunOptions.Current.IgnoreFiles)
{
if (System.Text.RegularExpressions.Regex.IsMatch(file.FullName, pattern))
{
parse = false;
break;
}
}
if (parse)
{
LoadScript(file, scripts, file.FullName.Replace(baseDirectory, "").Replace(file.Extension, ""));
}
}
//process subdirs
foreach (DirectoryInfo subDir in scriptDirectory.GetDirectories())
{
LoadScripts(subDir, scripts, baseDirectory);
}
}
/// <summary>
/// Load a single script from the file system.
/// </summary>
/// <param name="scriptFile">The script file.</param>
/// <returns></returns>
public static ScriptSet LoadScript(string scriptFile)
{
ScriptSet scripts = new ScriptSet();
LoadScript(new FileInfo(scriptFile), scripts, null);
return(scripts);
}
/// <summary>
/// Executes the specified script.
/// </summary>
/// <param name="script">The script to execute.</param>
/// <param name="connectionString">The database connection string.</param>
public static void ExecuteScripts(Script script, string connectionString)
{
ScriptSet scripts = new ScriptSet();
scripts.Add(script);
ExecuteScripts(scripts, connectionString);
}
/// <summary>
/// Adds the scripts in the specified set.
/// </summary>
/// <param name="adding">The set of scripts.</param>
public void Add(ScriptSet adding)
{
if (adding == null)
{
return;
}
foreach (Script script in adding)
{
Add(script);
}
}
/// <summary>
/// Writes a set of scripts to the file system.
/// </summary>
/// <param name="scripts">The scripts to write out.</param>
/// <param name="differences">The differences the scripts are based on.</param>
public static void WriteScripts(ScriptSet scripts, DifferenceSet differences)
{
if (!Directory.Exists(RunOptions.Current.ScriptOutputDirectory))
{
Directory.CreateDirectory(RunOptions.Current.ScriptOutputDirectory);
}
StreamWriter writer = null;
if (RunOptions.Current.ScriptFile != null)
{
writer = new StreamWriter(
Path.Combine(RunOptions.Current.ScriptOutputDirectory, RunOptions.Current.ScriptFile), false);
if (differences != null)
{
writer.WriteLine("/*");
writer.WriteLine("Changes:");
differences.Write(writer);
writer.WriteLine("*/");
writer.WriteLine();
}
writer.Write("SET NOCOUNT ON\n\nGO\n\n");
writer.Write("SET QUOTED_IDENTIFIER ON\nGO\n\n");
}
StreamWriter diffWriter = null;
if (RunOptions.Current.DiffFile != null)
{
writer = new StreamWriter(
Path.Combine(RunOptions.Current.ScriptOutputDirectory, RunOptions.Current.DiffFile), false);
}
for (int i = 0; i < scripts.Count; i++)
{
Script script = (Script)scripts[i];
if (writer == null)
{
string extension = ScriptExtensions.ContainsKey(script.Type) ? ScriptExtensions[script.Type] : ".sql";
writer = new StreamWriter(
Path.Combine(RunOptions.Current.ScriptOutputDirectory, script.Name.Unescaped + extension), false);
}
else
{
writer.Write("-- " + i + " - " + script.Name + "\r\nGO\r\n");
}
writer.Write(script.Text);
writer.Flush();
if (RunOptions.Current.ScriptFile == null)
{
writer.Close();
writer = null;
}
else
{
writer.Write("\r\n\r\n");
}
}
if (writer != null)
{
writer.Close();
}
if (diffWriter != null)
{
diffWriter.Close();
}
}
/// <summary>
/// Load a script from the file system.
/// </summary>
/// <param name="file">The file to load.</param>
/// <param name="scripts">The script set to populate.</param>
/// <param name="name">The script name.</param>
public static void LoadScript(FileInfo file, ScriptSet scripts, string name)
{
//what we do depends on what type of file we've got
switch (file.Extension.ToLower())
{
//data
case ".data":
scripts.Add(new Script(file, ScriptType.TableData, name));
break;
//extended table properties
case ".ext":
RunOptions.Current.Logger.Log("Ignoring extended property file " + file.FullName, OutputLevel.Reads);
break;
//foreign key constraints
case ".fky":
scripts.Add(new Script(file, ScriptType.ForeignKey, name));
break;
//generic script?
case ".sql":
switch (Path.GetExtension(Path.GetFileNameWithoutExtension(file.FullName)))
{
case ".data":
scripts.Add(new Script(file, ScriptType.TableData, name));
break;
case ".fkey":
scripts.Add(new Script(file, ScriptType.ForeignKey, name));
break;
case ".function":
scripts.Add(new Script(file, ScriptType.UserDefinedFunction, name));
break;
case ".pkey":
scripts.Add(new Script(file, ScriptType.PrimaryKey, name));
break;
case ".proc":
scripts.Add(new Script(file, ScriptType.StoredProc, name));
break;
case ".table":
scripts.Add(new Script(file, ScriptType.Table, name));
break;
case ".trigger":
scripts.Add(new Script(file, ScriptType.Trigger, name));
break;
case ".view":
scripts.Add(new Script(file, ScriptType.View, name));
break;
default:
scripts.Add(new Script(file, ScriptType.Unknown, name));
break;
}
break;
//known files to ignore
case ".dbp": //database project
case ".log": //log file
case ".scc": //source control file
case ".vspscc": //source control file
RunOptions.Current.Logger.Log("Ignoring non script file " + file.FullName, OutputLevel.Reads);
break;
//primary key constraints
case ".kci":
scripts.Add(new Script(file, ScriptType.PrimaryKey, name));
break;
//stored procs
case ".prc":
scripts.Add(new Script(file, ScriptType.StoredProc, name));
break;
//table definitions
case ".tab": //table definitions
scripts.Add(new Script(file, ScriptType.Table, name));
break;
//triggers
case ".trg":
scripts.Add(new Script(file, ScriptType.Trigger, name));
break;
//user defined functions
case ".udf":
scripts.Add(new Script(file, ScriptType.UserDefinedFunction, name));
break;
//views
case ".viw":
scripts.Add(new Script(file, ScriptType.View, name));
break;
default:
RunOptions.Current.Logger.Log("Ignoring unknown file " + file.FullName, OutputLevel.Reads);
break;
}
}
/// <summary>
/// Executes the specified scripts.
/// </summary>
/// <param name="scripts">The scripts to execute.</param>
/// <param name="connectionString">The database connection string.</param>
public static void ExecuteScripts(ScriptSet scripts, string connectionString)
{
scripts.Sort();
using (SqlConnection connection = new SqlConnection(connectionString))
{
//initialize
connection.Open();
SqlCommand command = new SqlCommand();
command.Connection = connection;
command.CommandTimeout = COMMAND_TIMEOUT;
bool success = true;
ScriptType lastProcessed = ScriptType.DropFulltextIndex;
RunOptions.Current.Logger.Log("Beginning table updates", OutputLevel.Updates);
//process each script
foreach (Script script in scripts)
{
try
{
//Fulltext indexes have to happen outside of transactions
if (lastProcessed < ScriptType.DropTrigger && script.Type >= ScriptType.DropTrigger)
{
CompleteUpdate(success, command, false, true);
}
if (lastProcessed < ScriptType.FulltextIndex && script.Type >= ScriptType.FulltextCatalog)
{
CompleteUpdate(success, command, false, false);
}
//separate table updates and more generic scripts
if (lastProcessed < ScriptType.Trigger && script.Type >= ScriptType.Trigger)
{
CompleteUpdate(success, command, false, true);
RunOptions.Current.Logger.Log("Table updates complete", OutputLevel.Updates);
}
//do the same for data population scripts
if (lastProcessed < ScriptType.Unknown && script.Type >= ScriptType.Unknown)
{
CompleteUpdate(success, command, false, true);
}
RunOptions.Current.Logger.Log("Processing script: " + script.Name + " (" + script.Type + ")", OutputLevel.Updates);
lastProcessed = script.Type;
foreach (string batch in script.Batches)
{
if (batch.Trim() == "")
{
continue;
}
command.CommandText = batch;
command.ExecuteNonQuery();
}
}
catch (Exception ex)
{
success = false;
RunOptions.Current.Logger.Log("Error: " + ex.Message, OutputLevel.Errors);
}
}
//clean up behind the horse
CompleteUpdate(success, command, true, false);
}
}