private ServiceResponse UpdateDatabaseSchema(DatabaseInfo info, string databaseUserNamePublic, string scriptResourceName, IEnumerable<string> scriptResourceNames, ScriptLoader scriptLoader) { // Create temp App Domain for loading assemblies AppDomain tempAppDomain = AppDomain.CreateDomain("TempAppDomain"); try { using (var ts = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 5, 0))) { using (var database = new DatabaseManipulator(info)) { smo.Server sqlServer = database.SqlServer; // Create the script version info table if needed var versionInfoDao = new SqlScriptVersionInfoDao(info.ConnectionString); versionInfoDao.CreateScriptVersionTable(info); _logger.Info("Verifying database schema...", "Database Name", info.DatabaseName); // Get script execution history as a dictionary var sqlScriptVersionInfoDict = new Dictionary<string, string>(); foreach (IVersionInfo sqlScriptVersionInfo in versionInfoDao.ReadList()) { sqlScriptVersionInfoDict.Add(sqlScriptVersionInfo.Component, sqlScriptVersionInfo.Version); } // Get all assembly names in the specified directory IEnumerable<string> files = scriptLoader.GetSortedScriptAssemblyList(); foreach (string file in files) { // Delegate assembly loading/processing to temp App Domain tempAppDomain.SetData("FileName", file); tempAppDomain.SetData("ResourceFileName", scriptResourceName); tempAppDomain.SetData("ResourceFileNames", scriptResourceNames); var dbScriptsToExecute = new Dictionary<string, string>(); try { tempAppDomain.DoCallBack(new CrossAppDomainDelegate(ScriptLoader.GetScriptsFromAssembly)); dbScriptsToExecute = (Dictionary<string, string>)tempAppDomain.GetData("DbScriptsToExecute"); } catch (Exception) { _logger.Error("Error loading assembly!", "FileName", file); } // Sort the scripts by key string[] sortedScriptKeys = new string[dbScriptsToExecute.Keys.Count]; dbScriptsToExecute.Keys.CopyTo(sortedScriptKeys, 0); Array.Sort(sortedScriptKeys); foreach (string scriptKey in sortedScriptKeys) { string scriptValue = dbScriptsToExecute[scriptKey]; string[] keyParts = scriptKey.Split("_".ToCharArray()); if (keyParts.GetUpperBound(0) != 1) { throw new InvalidOperationException(string.Format(CultureInfo.InvariantCulture, "Invalid script key format ({0})", scriptKey)); } string component = Path.GetFileNameWithoutExtension(file); string version = keyParts[1]; // If component has no script history, or current script hasn't been executed, execute it if (!sqlScriptVersionInfoDict.ContainsKey(component) || (int.Parse(version, CultureInfo.InvariantCulture) > int.Parse(sqlScriptVersionInfoDict[component], CultureInfo.InvariantCulture))) { ConsoleUtils.WriteLine(string.Format(CultureInfo.InvariantCulture, " Executing\n Component: {0}\n Script: {1}\n", component, scriptKey), ConsoleColor.Green); scriptValue = ApplyScriptTokens(scriptValue, databaseUserNamePublic); sqlServer.Databases[info.DatabaseName].ExecuteNonQuery(scriptValue); // Check if script version information already exists for this component IVersionInfo versionInfo = versionInfoDao.Read(component); AddOrUpdateVersionInfo(component, version, versionInfo, versionInfoDao); } } } ts.Complete(); } } } catch (Exception ex) { _logger.Error("Update of database failed. Please check that you have configured the database server to allow both SQL Server authentication and Windows authentication.", ex, info.GenerateCustomLoggingProperties()); _response.AddError("Update of database ({0}) on server ({1}) failed.", info.DatabaseName, info.ServerName); } finally { // Unload the temp App Domain, which unloads any loaded assemblies AppDomain.Unload(tempAppDomain); } return _response; }
public ServiceResponse UpdateDatabaseSchema(DatabaseInfo info, string databaseUserNamePublic, string component, string scriptPath) { try { using (var ts = new TransactionScope(TransactionScopeOption.Required, new TimeSpan(0, 5, 0))) { using (var database = new DatabaseManipulator(info)) { smo.Server sqlServer = database.SqlServer; var versionInfoDao = new SqlScriptVersionInfoDao(info.ConnectionString); versionInfoDao.CreateScriptVersionTable(info); _logger.Info("Verifying database schema...", "Database Name", info.DatabaseName); int currentVersion = 0; IVersionInfo sqlScriptVersionInfo = versionInfoDao.ReadList() .Where(v => v.Component == component) .OrderBy(v => v.Version) .LastOrDefault(); if (sqlScriptVersionInfo != null) { currentVersion = int.Parse(sqlScriptVersionInfo.Version, CultureInfo.InvariantCulture); } IEnumerable<string> files = Directory.GetFiles(scriptPath, "script_*.sql"); foreach (string file in files) { string scriptName = Path.GetFileNameWithoutExtension(file); string scriptValue = File.ReadAllText(file); string[] keyParts = scriptName.Split("_".ToCharArray()); string versionString = keyParts[1]; int scriptVersion = int.Parse(versionString, CultureInfo.InvariantCulture); if (scriptVersion > currentVersion) { ConsoleUtils.WriteLine(string.Format(CultureInfo.InvariantCulture, " Executing Component: {0} Script: {1}\n", component, scriptName), ConsoleColor.Green); scriptValue = ApplyScriptTokens(scriptValue, databaseUserNamePublic); sqlServer.Databases[info.DatabaseName].ExecuteNonQuery(scriptValue); // Check if script version information already exists for this component IVersionInfo versionInfo = versionInfoDao.Read(component); AddOrUpdateVersionInfo(component, versionString, versionInfo, versionInfoDao); } } ts.Complete(); } } } catch (Exception ex) { _logger.Error("Update of database failed. Please check that you have configured the database server to allow both SQL Server authentication and Windows authentication.", ex, info.GenerateCustomLoggingProperties()); _response.AddError("Update of database ({0}) on server ({1}) failed.", info.DatabaseName, info.ServerName); } return _response; }