public static DatabaseVersion GetLastExecutedVersion(Database database) { DatabaseVersion databaseVersion = new DatabaseVersion { ReleaseVersion = DatabaseVersion.NO_EXECUTED_RELEASE_VERSION }; List <string> releaseVersions = new List <string>(); SqlDatabaseManager databaseManager = new SqlDatabaseManager(database, true); IDataReader reader = databaseManager.ExecuteReader("SELECT DISTINCT RELEASE_VERSION FROM " + Constants.CHANGE_LOG_TABLE); while (reader.Read()) { releaseVersions.Add((string)reader["RELEASE_VERSION"]); } reader.Close(); if (releaseVersions.Count == 0) { databaseManager.CloseConnection(); return(databaseVersion); } var latestReleaseVerion = ChangeReader.AllReleaseChanges.Where(x => releaseVersions.Contains(x.Name)).OrderByDescending(y => y.Sequence).First(); databaseVersion.ReleaseVersion = latestReleaseVerion.Name; reader = databaseManager.ExecuteReader(@"SELECT MAX(CHANGE_VERSION) AS CHANGE_VERSION FROM " + Constants.CHANGE_LOG_TABLE + " WHERE RELEASE_VERSION = '" + databaseVersion.ReleaseVersion + "';"); while (reader.Read()) { databaseVersion.ChangeVersion = (int)reader["CHANGE_VERSION"]; } reader.Close(); databaseManager.CloseConnection(); return(databaseVersion); }
public static void ExecuteChanges(DatabaseGroup databaseGroup, string releaseVersion, int changeVersion, bool force) { ReleaseChanges releaseChanges = AsserValidReleaseVersion(releaseVersion); if (ChangeReader.AllReleaseChanges.First(x => x.Name == releaseVersion).Changes.Count(y => y.Version == changeVersion) == 0) { throw new VersioningException("Change version - \"" + changeVersion + "\" does not exist in the change xml file for Release version - \"" + releaseVersion + "\" ."); } foreach (Database database in databaseGroup.Databases) { int lastExecutedCurrentChangeVersion = GetLastExecutedChangeVersion(database, releaseVersion); if (force) { lastExecutedCurrentChangeVersion = changeVersion - 1; } else if (!IsVersionChainMaintained(database, releaseChanges, changeVersion, lastExecutedCurrentChangeVersion)) { continue; } SqlDatabaseManager databaseManager = new SqlDatabaseManager(database, true); SqlTransaction tx = databaseManager.Connection.BeginTransaction(); Change executingChange = null; string executingSql = null; try { List <int> executedVersions = new List <int>(); for (int version = lastExecutedCurrentChangeVersion + 1; version <= changeVersion; version++) { executingChange = releaseChanges.Changes.FirstOrDefault(x => x.Version == version); foreach (var sql in executingChange.ChangeSqls) { executingSql = string.Empty; if (!string.IsNullOrEmpty(sql.Path)) { executingSql = System.IO.File.ReadAllText(System.IO.Path.Combine(Constants.CHANGE_SCRIPT_DIRECTORY, sql.Path)); } else { executingSql = sql.Sql; } if (database.Replacements != null) { foreach (var replacement in database.Replacements) { executingSql = executingSql.Replace(replacement.Text, replacement.ReplacementText); } } databaseManager.ExecuteNonQuery(executingSql, tx); } if (!force) { databaseManager.ExecuteNonQuery(@" INSERT INTO " + Constants.CHANGE_LOG_TABLE + @" (RELEASE_VERSION, CHANGE_VERSION, EXECUTION_TIME, EXECUTOR_NAME, EXECUTOR_IP, DESCRIPTION) VALUES ('" + releaseVersion + "'," + version + " ,CURRENT_TIMESTAMP, SUSER_NAME(), CAST(CONNECTIONPROPERTY('client_net_address') AS VARCHAR(255)), '" + (releaseChanges.Changes.FirstOrDefault(x => x.Version == version).Description ?? "") + "');", tx); } executedVersions.Add(version); } tx.Commit(); Display.DisplayMessage(DisplayType.Success, "Successfulle executed change versions {0} in release {1} in database {2}", string.Join(", ", executedVersions), releaseVersion, database.Name); } catch (Exception ex) { string additionaMessage = string.Empty; string executingSqlText = string.Empty; if (executingChange != null) { additionaMessage += "\nException in Change \"" + executingChange.Version + "\" (" + (executingChange.Description ?? "") + ").\n"; } if (!string.IsNullOrEmpty(executingSql)) { executingSqlText = "\nException while executing SQL:\n" + executingSql; } Display.DisplayMessage(DisplayType.Error, "Exception occured while executing changes in release \"{0}\" in database \"{1}\".{2}\nException {3}{4}", releaseVersion, database.Name, additionaMessage, ex, executingSqlText); tx.Rollback(); } finally { databaseManager.CloseConnection(); } } }