/// <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; }