/// <summary>
/// Checks if a specified script version has been processed.
/// </summary>
/// <param name="version"></param>
/// <returns></returns>
private bool IsScriptProcessed(SchemaVersion version)
{
bool output;
using (SqlConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();
var script =
string.Format(
"SELECT COUNT(*) FROM [{0}] WHERE Major=@Major AND Minor=@Minor AND Revision=@Revision AND Build=@Build",
TableName);
using (SqlCommand cmd = new SqlCommand(script, conn)) {
cmd.Parameters.AddWithValue("@Major", version.Major);
cmd.Parameters.AddWithValue("@Minor", version.Minor);
cmd.Parameters.AddWithValue("@Revision", version.Revision);
cmd.Parameters.AddWithValue("@Build", version.Build);
var count = Convert.ToInt32(cmd.ExecuteScalar());
output = count > 0;
}
conn.Close();
}
return(output);
}
/// <summary>
/// Executes the specified script on the connected database
/// </summary>
/// <param name="script"></param>
/// <param name="version"></param>
/// <param name="description"></param>
/// <returns>True if succeeded, False is errors occured</returns>
public void ExecuteScript(string script, SchemaVersion version, string description)
{
using (SqlConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();
var expectedVersion = GetSchemaVersion();
// Don't perform version check if schema version is not initialized (ie. database is new)
if (expectedVersion.IsInitialized)
{
expectedVersion.Build++;
if (version != expectedVersion)
{
if (IsScriptProcessed(version))
{
throw new DuplicateScriptException(version);
}
else
{
throw new InvalidSchemaVersionException(version, expectedVersion);
}
}
}
using (var trans = new System.Transactions.TransactionScope()) {
using (SqlCommand cmd = new SqlCommand(script, conn))
{
if (Timeout.HasValue)
{
cmd.CommandTimeout = Timeout.Value;
}
var x = cmd.ExecuteNonQuery();
script = string.Format(
"INSERT INTO [{0}] (Major, Minor, Revision, Build, Description, DateApplied) VALUES (@Major, @Minor, @Revision, @Build, @Description, GETDATE())",
TableName
);
cmd.CommandText = script;
cmd.Parameters.AddWithValue("@Major", version.Major);
cmd.Parameters.AddWithValue("@Minor", version.Minor);
cmd.Parameters.AddWithValue("@Revision", version.Revision);
cmd.Parameters.AddWithValue("@Build", version.Build);
cmd.Parameters.AddWithValue("@Description", description);
var y = cmd.ExecuteNonQuery();
}
trans.Complete();
}
}
}
/// <summary>
/// Gets the current schema version of the connected database
/// </summary>
public SchemaVersion GetSchemaVersion()
{
SchemaVersion version = new SchemaVersion();
using (SqlConnection conn = new SqlConnection(ConnectionString)) {
conn.Open();
using (SqlCommand cmd = new SqlCommand(string.Format("SELECT TOP 1 Major, Minor, Revision, Build FROM [{0}] ORDER BY Major DESC, Minor DESC, Revision DESC, Build DESC", this.TableName), conn)) {
var reader = cmd.ExecuteReader();
if (reader != null)
{
while (reader.Read())
{
version = new SchemaVersion(reader.GetInt32(0), reader.GetInt32(1), reader.GetInt32(2), reader.GetInt32(3));
}
}
}
conn.Close();
}
return(version);
}
public InvalidSchemaVersionException(SchemaVersion expected, SchemaVersion actual)
{
_expectedVersion = expected;
_actualVersion = actual;
}
private static ExitCode ExecuteScripts(Arguments settings)
{
if (!Directory.Exists(settings.ScriptsFolderPath))
{
throw new DirectoryNotFoundException(settings.ScriptsFolderPath);
}
var scriptFilePaths = Directory.GetFiles(settings.ScriptsFolderPath, "*.sql");
Array.Sort(scriptFilePaths);
using (var ds = new DataSource(settings))
{
if (!scriptFilePaths.Any())
{
Console.WriteLine("No scripts found in {0}", settings.ScriptsFolderPath);
}
// Execute change scripts
foreach (string scriptFilePath in scriptFilePaths)
{
SchemaVersion version;
var scriptFileName = Path.GetFileNameWithoutExtension(scriptFilePath);
// Only process scripts where the name starts with a valid SchemaVersion
if (scriptFileName == null || !SchemaVersion.TryParse(scriptFileName.Substring(0, 13), out version))
{
continue;
}
Console.WriteLine("Processing {0}...", Path.GetFileName(scriptFilePath));
try
{
var script = File.ReadAllText(scriptFilePath);
ds.ExecuteScript(script, version, scriptFileName.Substring(13).Trim(" -_.".ToCharArray()));
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine(" Done");
Console.ForegroundColor = ConsoleColor.Gray;
}
catch (InvalidSchemaVersionException ex)
{
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine(" " + ex.Message);
Console.ForegroundColor = ConsoleColor.Gray;
}
catch (DuplicateScriptException ex)
{
Console.ForegroundColor = ConsoleColor.DarkGreen;
Console.WriteLine(" " + ex.Message);
Console.ForegroundColor = ConsoleColor.Gray;
}
catch (Exception ex)
{
Console.ForegroundColor = ConsoleColor.Red;
Console.WriteLine(" Error while processing {0}", Path.GetFileName(scriptFilePath));
Console.WriteLine(" " + ex.Message);
Console.WriteLine(" Not processing further scripts!");
Console.ForegroundColor = ConsoleColor.Gray;
// Don't process further scripts
return(ExitCode.Failed);
}
}
}
return(ExitCode.Success);
}
public DuplicateScriptException(SchemaVersion version)
{
_version = version;
}