private static Version SelectSchemaVersion(SqlTextAdapter adapter) { StringBuilder stringVersion = null; Version version = new Version(0, 0, 0); try { // Select the current schema version from the Extended Property of the database string sqlText = "SELECT value FROM fn_listextendedproperty(N'STF Revision', NULL, NULL, NULL, NULL, NULL, NULL)"; var reader = adapter.ExecuteReader(sqlText); if (reader != null && reader.Read()) { stringVersion = new StringBuilder((string)reader["value"]); Match match = Regex.Match(stringVersion.ToString(), @"([0-9]+)\.([0-9]+)\.*([0-9]*)", RegexOptions.IgnoreCase); stringVersion.Clear(); stringVersion.Append(match.Groups[1].Value); stringVersion.Append("."); stringVersion.Append(match.Groups[2].Length == 0 ? "0" : match.Groups[2].Value); stringVersion.Append("."); stringVersion.Append(match.Groups[3].Length == 0 ? "0" : match.Groups[3].Value); version = new Version(stringVersion.ToString()); } } catch (Exception ex) { SystemTrace.Instance.Error(ex.ToString()); } return(version); }
/// <summary> /// Gets the database schema version. /// </summary> /// <param name="hostname"></param> /// <returns></returns> public static Version GetSchemaVersion(string hostname) { using (SqlTextAdapter adapter = new SqlTextAdapter(hostname, "Master")) { return(SelectSchemaVersion(adapter)); } }
private bool ExecuteSql(string sqlText, SqlTextAdapter adapter, bool useTransaction = true) { if (CancelPending()) { return(false); } string[] sqlCommands = sqlText.Split(_delimiter, StringSplitOptions.RemoveEmptyEntries); int count = 1; int length = sqlCommands.Length; string statement = string.Empty; SendProgressStart(sqlCommands.Length); foreach (string command in sqlCommands) { try { SendProgressUpdate(count++); statement = command.Trim(); if (!string.IsNullOrEmpty(statement)) { if (useTransaction) { adapter.BeginTransaction(); adapter.ExecuteSql(statement); adapter.CommitTransaction(); } else { adapter.ExecuteSql(statement); } } if (CancelPending()) { return(false); } } catch (Exception ex) { if (useTransaction) { adapter.RollbackTransaction(); } SendError(ex); SystemTrace.Instance.Error(statement); System.Diagnostics.Debug.WriteLine(ex.ToString()); System.Diagnostics.Debug.WriteLine(statement); throw; } } Thread.Sleep(1000); SendProgressEnd(); return(true); }
private void RollBackSTBInstall() { using (SqlTextAdapter adapter = new SqlTextAdapter(Environment.MachineName, "Master")) { DropDatabaseInstances(GetSTBDatabaseNames(), adapter); } CleanUpFileShare(); }
/// <summary> /// Runs the specified update script against the database. /// Note: Assumes transactions are being handled outside of this method. /// </summary> /// <param name="adapter">The SQL adapter</param> /// <param name="script">The databse update script</param> /// <returns>True if the script was executed successfully. False otherwise.</returns> private bool RunDatabaseUpdate(string databaseHost, SqlUpdateScript script) { // Execute the SQL update script against the defined database host using (SqlTextAdapter adapter = new SqlTextAdapter(databaseHost, "Master")) { UpdateStatus("Updating {0} to version {1}".FormatWith(script.Database, script.Version)); if (!ExecuteSql(script.SqlText, adapter, true)) { return(false); } } return(true); }
private bool UpdateVersion(string databaseHost, Version version) { // Update the STB/STF version on the defined database host. string updateVersionSql = new EmbeddedResource("HP.SolutionTest.DatabaseCreation").Read("ins_SchemaVersion.sql"); using (var adapter = new SqlTextAdapter(databaseHost, "Master")) { adapter.BeginTransaction(); if (!ExecuteSql(updateVersionSql.FormatWith(version.ToString()), adapter, false)) { adapter.RollbackTransaction(); return(false); } adapter.CommitTransaction(); _ticket.CurrentVersion = version; } return(true); }
/// <summary> /// Drop all database instances that were created during this install. /// </summary> /// <param name="creationSQL">The SQL used to create the databases.</param> /// <param name="adapter">The SQL Text Adapter.</param> private void DropDatabaseInstances(IEnumerable <string> databaseNames, SqlTextAdapter adapter) { try { foreach (string databaseName in databaseNames) { UpdateStatus(string.Format("Killing {0} database session.", databaseName)); //UpdateStatus(statement); adapter.ExecuteNonQuery(Resources.UseMaster); adapter.ExecuteSql(Resources.KillDatabaseProcessSql.FormatWith(databaseName)); UpdateStatus(string.Format("Dropping {0}.", databaseName)); adapter.ExecuteSql(Resources.UseMaster); adapter.ExecuteSql(Resources.CloseConnectionSql.FormatWith(databaseName)); adapter.ExecuteSql(Resources.DropDatabaseSql.FormatWith(databaseName)); } } catch (Exception ex) { UpdateStatus(ex.Message); Thread.Sleep(5000); SendError(ex); } }
private bool CreateDatabases() { SystemTrace.Instance.Debug("Creating databases..."); string creationSQL = string.Empty; //Cache this in case we need to roll back. StringBuilder sqlText = new StringBuilder(); // Create tables, insert data, etc. for each database using (SqlTextAdapter adapter = new SqlTextAdapter(Environment.MachineName, "Master")) { try { UpdateStatus("Creating Solution Test Bench database instances..."); EmbeddedResource stream = new EmbeddedResource("HP.SolutionTest.DatabaseCreation"); creationSQL = stream.Read("create_DatabaseInstances.sql"); sqlText.Append(creationSQL); sqlText.Replace("{DATABASE_PATH}", _ticket.DatabaseFilesPath); if (!ExecuteSql(sqlText.ToString(), adapter, false)) { return(false); } UpdateStatus("Creating Solution Test Bench database users..."); if (!ExecuteSql(stream.Read("create_DatabaseUsers.sql"), adapter, true)) { return(false); } UpdateStatus("Creating Solution Test Bench primary configuration database schema..."); if (!ExecuteSql(stream.Read("create_SchemaEnterpriseTest.sql"), adapter, true)) { return(false); } UpdateStatus("Creating Solution Test Bench asset inventory database schema..."); if (!ExecuteSql(stream.Read("create_SchemaAssetInventory.sql"), adapter, true)) { return(false); } UpdateStatus("Creating Solution Test Bench test document database schema..."); if (!ExecuteSql(stream.Read("create_SchemaTestDocumentLibrary.sql"), adapter, true)) { return(false); } UpdateStatus("Creating Solution Test Bench data log database schema..."); if (!ExecuteSql(stream.Read("create_SchemaScalableTestDatalog.sql"), adapter, true)) { return(false); } UpdateStatus("Populating Solution Test Bench databases with base configuration data..."); sqlText.Clear(); sqlText.Append(stream.Read("ins_BaseConfigurationData.sql")); sqlText.Replace("{FILE_SHARE}", _ticket.FileShareName); sqlText.Replace("{SERVER_ADDRESS}", _ticket.ServerHostname); sqlText.Replace("{ADMIN_USER}", _ticket.AdminUserName); sqlText.Replace("{ADMIN_DOMAIN}", _ticket.AdminDomain); sqlText.Replace("{ORGANIZATION}", _ticket.OrganizationName); sqlText.Replace("{ADMIN_EMAIL}", _ticket.AdminEmail); sqlText.Replace("{DNS_DOMAIN}", _dnsDomainName); sqlText.Replace("{AD_DOMAIN}", Environment.UserDomainName); if (!ExecuteSql(sqlText.ToString(), adapter, true)) { return(false); } Version version = new Version(stream.Read("SchemaVersion.txt")); SystemTrace.Instance.Debug($"Schema version: {version}"); UpdateStatus("Updating Solution Test Bench database to initial version"); sqlText.Clear(); sqlText.Append(stream.Read("ins_SchemaVersion.sql")); sqlText = sqlText.Replace("{0}", version.ToString()); if (!ExecuteSql(sqlText.ToString(), adapter, true)) { return(false); } // Update the ticket to the current version _ticket.CurrentVersion = version; } catch (Exception ex) { SendError(ex); DropDatabaseInstances(GetSTBDatabaseNames(ref creationSQL), adapter); return(false); } } return(true); }