public void LoadUI(InstallSetup setup) { chkIgnoreWarnings.Checked = setup.AcceptVersionWarningsChangedScripts && setup.AcceptVersionWarningsNewScripts; chkSkipNormalize.Checked = setup.SkipNormalize; chkUseTransaction.Checked = setup.UseTransaction; chkUseHash.Checked = setup.UseHash; }
/// <summary> /// Performs an install of a database /// </summary> public void Install(InstallSetup setup) { //The connection string must reference an existing database if (!DatabaseServer.TestConnectionString(setup.ConnectionString)) { throw new Exception("The connection string does not reference a valid database."); } try { UpgradeInstaller.UpgradeDatabase(setup); } catch (InvalidSQLException ex) { var sb = new StringBuilder(); sb.AppendLine(); sb.AppendLine("BEGIN ERROR SQL"); sb.AppendLine(ex.SQL); sb.AppendLine("END ERROR SQL"); sb.AppendLine(); Log.Verbose(sb.ToString()); UpgradeInstaller.LogError(ex, sb.ToString()); throw; } catch (Exception ex) { throw; } }
private bool CreateDatabase() { var error = false; var connectString = SqlServers.BuildConnectionString(optCreationIntegratedSecurity.Checked, string.Empty, cboCreationServerName.Text, txtCreationUserName.Text, txtCreationPassword.Text); if (SqlServers.TestConnectionString(connectString) && SqlServers.HasCreatePermissions(connectString)) { try { var setup = new InstallSetup() { MasterConnectionString = connectString, NewDatabaseName = txtCreationDatabaseName.Text, }; SqlServers.CreateDatabase(setup); } catch (Exception ex) { error = true; System.Diagnostics.Debug.WriteLine(ex.ToString()); MessageBox.Show("Could not create database." + Environment.NewLine + ex.Message); } } else { error = true; MessageBox.Show("The account does not have permissions to create a database on this server.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } return(error); }
public void SaveUI(InstallSetup setup) { setup.AcceptVersionWarningsChangedScripts = chkIgnoreWarnings.Checked; setup.AcceptVersionWarningsNewScripts = chkIgnoreWarnings.Checked; setup.SkipNormalize = chkSkipNormalize.Checked; setup.UseTransaction = chkUseTransaction.Checked; setup.UseHash = chkUseHash.Checked; }
/// <summary> /// Performs an install of a database /// </summary> public void Install(InstallSetup setup) { if (setup.InstallStatus == InstallStatusConstants.Create) { //Conection cannot reference an existing database if (SqlServers.TestConnectionString(setup.ConnectionString)) { throw new Exception("The connection string references an existing database."); } //The new database name must be specified if (string.IsNullOrEmpty(setup.NewDatabaseName)) { throw new Exception("A new database name was not specified."); } //The connection string and the new database name must be the same var builder = new System.Data.SqlClient.SqlConnectionStringBuilder(setup.ConnectionString); if (builder.InitialCatalog.ToLower() != setup.NewDatabaseName.ToLower()) { throw new Exception("A new database name does not match the specified connection string."); } SqlServers.CreateDatabase(setup); } else if (setup.InstallStatus == InstallStatusConstants.Upgrade) { //The connection string must reference an existing database if (!SqlServers.TestConnectionString(setup.ConnectionString)) { throw new Exception("The connection string does not reference a valid database."); } } try { UpgradeInstaller.UpgradeDatabase(setup); } catch (InvalidSQLException ex) { var sb = new StringBuilder(); sb.AppendLine(); sb.AppendLine("BEGIN ERROR SQL"); sb.AppendLine(ex.SQL); sb.AppendLine("END ERROR SQL"); sb.AppendLine(); Console.WriteLine(sb.ToString()); UpgradeInstaller.LogError(ex, sb.ToString()); throw; } catch (Exception ex) { throw; } }
/// <summary> /// Returns the upgrade script for the specified database /// </summary> public string GetScript(InstallSetup setup) { if (string.IsNullOrEmpty(setup.ConnectionString) && setup.Version == null) { throw new Exception("The connection string must be set."); } if (setup.SkipSections == null) { setup.SkipSections = new List <string>(); } return(UpgradeInstaller.GetScript(setup)); }
private bool IdentifyDatabaseConnectionString(InstallSetup setup) { var F = new IdentifyDatabaseForm(setup); if (F.ShowDialog() == DialogResult.OK) { this.Action = F.Action; this.Settings = F.Settings; return(true); } return(false); }
public static List <string> GetEmbeddedScripts(string resourceFileName, InstallSetup setup) { var retval = new List <string>(); var tempFolder = string.Empty; var scripts = ReadSQLFileSectionsFromResource(resourceFileName, setup); foreach (var sql in scripts) { retval.Add(sql); } return(retval); }
public IdentifyDatabaseForm(InstallSetup setup) : this() { this.Settings = new InstallSettings(); _setup = setup; this.InstallSettingsUI1.LoadUI(setup); cmdCancel.Click += new EventHandler(cmdCancel_Click); cmdOK.Click += new EventHandler(cmdOK_Click); cboConnectionDatabaseName.DropDown += cboConnectionDatabaseName_DropDown; cboConnectionServerName.DropDown += ServerName_DropDown; cboCreationServerName.DropDown += ServerName_DropDown; buttonCreationRefresh.Click += buttonCreationRefresh_Click; optConnectionUserPassword.CheckedChanged += optConnectionUserPassword_CheckedChanged; radioButtonCreationUserPassword.CheckedChanged += radioButtonCreationUserPassword_CheckedChanged; optCreationIntegratedSecurity.CheckedChanged += optCreationIntegratedSecurity_CheckedChanged; cmdViewHistory.Click += cmdViewHistory_Click; buttonConnectionTestConnection.Click += buttonConnectionTestConnection_Click; optConnectionIntegratedSecurity.CheckedChanged += optConnectionIntegratedSecurity_CheckedChanged; buttonConnectionRefresh.Click += buttonConnectionRefresh_Click; //Turn off features for Azure //if (SqlServers.DatabaseVersion != SqlServers.SQLServerTypeConstants.SQLAzure) // tabControlChooseDatabase.TabPages.Remove(tabPageAzureCopy); this.Settings.Load(); if (this.Settings.IsLoaded) { chkSaveSettings.Checked = true; //Tab Upgrade cboConnectionServerName.Text = this.Settings.PrimaryServer; optConnectionIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity; optConnectionUserPassword.Checked = !optConnectionIntegratedSecurity.Checked; txtConnectionUserName.Text = this.Settings.PrimaryUserName; txtConnectionPassword.Text = this.Settings.PrimaryPassword; cboConnectionDatabaseName.Text = this.Settings.PrimaryDatabase; //Tab Create cboCreationServerName.Text = this.Settings.PrimaryServer; txtCreationDatabaseName.Text = this.Settings.PrimaryDatabase; optCreationIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity; radioButtonCreationUserPassword.Checked = !optCreationIntegratedSecurity.Checked; txtCreationUserName.Text = this.Settings.PrimaryUserName; txtCreationPassword.Text = this.Settings.PrimaryPassword; //Tab Azure Copy azureCopyControl1.LoadSettings(this.Settings); } this.UpdateLogin(); }
public IdentifyDatabaseForm(InstallSetup setup) : this() { this.Settings = new InstallSettings(); _setup = setup; this.InstallSettingsUI1.LoadUI(setup); cmdCancel.Click += new EventHandler(cmdCancel_Click); cmdOK.Click += new EventHandler(cmdOK_Click); cboConnectionDatabaseName.DropDown += cboConnectionDatabaseName_DropDown; cboConnectionServerName.DropDown += ServerName_DropDown; cboCreationServerName.DropDown += ServerName_DropDown; buttonCreationRefresh.Click += buttonCreationRefresh_Click; optConnectionUserPassword.CheckedChanged += optConnectionUserPassword_CheckedChanged; radioButtonCreationUserPassword.CheckedChanged += radioButtonCreationUserPassword_CheckedChanged; optCreationIntegratedSecurity.CheckedChanged += optCreationIntegratedSecurity_CheckedChanged; cmdViewHistory.Click += cmdViewHistory_Click; buttonConnectionTestConnection.Click += buttonConnectionTestConnection_Click; optConnectionIntegratedSecurity.CheckedChanged += optConnectionIntegratedSecurity_CheckedChanged; buttonConnectionRefresh.Click += buttonConnectionRefresh_Click; //Turn off features for Azure //if (SqlServers.DatabaseVersion != SqlServers.SQLServerTypeConstants.SQLAzure) // tabControlChooseDatabase.TabPages.Remove(tabPageAzureCopy); this.Settings.Load(); if (this.Settings.IsLoaded) { chkSaveSettings.Checked = true; //Tab Upgrade cboConnectionServerName.Text = this.Settings.PrimaryServer; optConnectionIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity; optConnectionUserPassword.Checked = !optConnectionIntegratedSecurity.Checked; txtConnectionUserName.Text = this.Settings.PrimaryUserName; txtConnectionPassword.Text = this.Settings.PrimaryPassword; cboConnectionDatabaseName.Text = this.Settings.PrimaryDatabase; //Tab Create cboCreationServerName.Text = this.Settings.PrimaryServer; txtCreationDatabaseName.Text = this.Settings.PrimaryDatabase; optCreationIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity; radioButtonCreationUserPassword.Checked = !optCreationIntegratedSecurity.Checked; txtCreationUserName.Text = this.Settings.PrimaryUserName; txtCreationPassword.Text = this.Settings.PrimaryPassword; txtDiskPath.Text = this.Settings.DiskPath; //Tab Azure Copy azureCopyControl1.LoadSettings(this.Settings); } this.UpdateLogin(); }
public IdentifyDatabaseForm(InstallSetup setup) : this() { this.Settings = new InstallSettings(); _setup = setup; this.InstallSettingsUI1.LoadUI(setup); cmdCancel.Click += new EventHandler(cmdCancel_Click); cmdOK.Click += new EventHandler(cmdOK_Click); cboConnectionDatabaseName.DropDown += cboConnectionDatabaseName_DropDown; cboConnectionServerName.DropDown += ServerName_DropDown; cboCreationServerName.DropDown += ServerName_DropDown; buttonCreationRefresh.Click += buttonCreationRefresh_Click; optConnectionUserPassword.CheckedChanged += optConnectionUserPassword_CheckedChanged; radioButtonCreationUserPassword.CheckedChanged += radioButtonCreationUserPassword_CheckedChanged; optCreationIntegratedSecurity.CheckedChanged += optCreationIntegratedSecurity_CheckedChanged; cmdViewHistory.Click += cmdViewHistory_Click; buttonConnectionTestConnection.Click += buttonConnectionTestConnection_Click; optConnectionIntegratedSecurity.CheckedChanged += optConnectionIntegratedSecurity_CheckedChanged; buttonConnectionRefresh.Click += buttonConnectionRefresh_Click; this.Settings.Load(); if (this.Settings.IsLoaded) { chkSaveSettings.Checked = true; //Tab Upgrade cboConnectionServerName.Text = this.Settings.PrimaryServer; optConnectionIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity; optConnectionUserPassword.Checked = !optConnectionIntegratedSecurity.Checked; txtConnectionUserName.Text = this.Settings.PrimaryUserName; txtConnectionPassword.Text = this.Settings.PrimarySecurityPhrase; cboConnectionDatabaseName.Text = this.Settings.PrimaryDatabase; //Tab Create cboCreationServerName.Text = this.Settings.PrimaryServer; txtCreationDatabaseName.Text = this.Settings.PrimaryDatabase; optCreationIntegratedSecurity.Checked = this.Settings.PrimaryUseIntegratedSecurity; radioButtonCreationUserPassword.Checked = !optCreationIntegratedSecurity.Checked; txtCreationUserName.Text = this.Settings.PrimaryUserName; txtCreationPassword.Text = this.Settings.PrimarySecurityPhrase; txtDiskPath.Text = this.Settings.DiskPath; } this.UpdateLogin(); }
/// <summary /> private void UIInstall(InstallSetup setup) { if (IdentifyDatabaseConnectionString(setup)) { setup.ConnectionString = this.Settings.GetPrimaryConnectionString(); setup.InstallStatus = InstallStatusConstants.Upgrade; if (this.Action == ActionTypeConstants.Create) { setup.InstallStatus = InstallStatusConstants.Create; UpgradeInstaller.UpgradeDatabase(setup); } else if (this.Action == ActionTypeConstants.Upgrade) { UpgradeInstaller.UpgradeDatabase(setup); } } }
internal static void CreateDatabase(InstallSetup setup) { try { using (var conn = new NpgsqlConnection(setup.MasterConnectionString)) { conn.Open(); var cmdCreateDb = new NpgsqlCommand(); var fileInfo = string.Empty; if (!string.IsNullOrEmpty(setup.DiskPath)) { fileInfo = " WITH LOCATION '" + Path.Combine(setup.DiskPath, setup.NewDatabaseName) + "')"; } cmdCreateDb.CommandText = $"CREATE DATABASE \"{setup.NewDatabaseName}\"" + fileInfo; cmdCreateDb.CommandType = System.Data.CommandType.Text; cmdCreateDb.Connection = conn; DatabaseServer.ExecuteCommand(cmdCreateDb); } using (var conn = new NpgsqlConnection(setup.ConnectionString)) { conn.Open(); //Add UUID generator var command = new NpgsqlCommand(); command.CommandText = "CREATE EXTENSION \"uuid-ossp\";"; command.CommandType = System.Data.CommandType.Text; command.Connection = conn; DatabaseServer.ExecuteCommand(command); } } catch { throw; } finally { System.Threading.Thread.Sleep(1000); } }
public void Run(InstallSettings settings) { //STEPS TO COPY DATABASE TO AZURE //1. Verify that target is a blank database //2. Execute only tables schemas with no defaults, relations, etc //3. Copy data with BCP one table at a time //4. Run full installer on the target database //1. Verify that target is a blank database if (!this.TargetIsBlank(settings)) { throw new Exception("The target database must be empty!"); } //2. Execute only tables schemas and PK with no defaults, relations, etc Assembly assem = Assembly.GetExecutingAssembly(); string[] resourceNames = assem.GetManifestResourceNames(); var resourceName = resourceNames.FirstOrDefault(x => x.EndsWith(".Create_Scripts.Generated.CreateSchema.sql")); if (string.IsNullOrEmpty(resourceName)) { throw new Exception("Could not find the 'CreateSchema.sql' resource!"); } var scripts = SqlServers.ReadSQLFileSectionsFromResource(resourceName, new InstallSetup()); SqlConnection connection = null; try { connection = new SqlConnection(settings.GetCloudConnectionString()); connection.Open(); ////Create version table //var sb = new StringBuilder(); //sb.AppendLine("if not exists(select * from sysobjects where name = '__nhydrateschema' and xtype = 'U')"); //sb.AppendLine("BEGIN"); //sb.AppendLine("CREATE TABLE [__nhydrateschema] ("); //sb.AppendLine("[dbVersion] [varchar] (50) NOT NULL,"); //sb.AppendLine("[LastUpdate] [datetime] NOT NULL,"); //sb.AppendLine("[ModelKey] [uniqueidentifier] NOT NULL,"); //sb.AppendLine("[History] [text] NOT NULL"); //sb.AppendLine(")"); //sb.AppendLine("--PRIMARY KEY FOR TABLE"); //sb.AppendLine("if not exists(select * from sysobjects where name = '__pk__nhydrateschema' and xtype = 'PK')"); //sb.AppendLine("ALTER TABLE [__nhydrateschema] WITH NOCHECK ADD CONSTRAINT [__pk__nhydrateschema] PRIMARY KEY CLUSTERED ([ModelKey])"); //sb.AppendLine("END"); //var command2 = new SqlCommand(sb.ToString(), connection); //command2.ExecuteNonQuery(); foreach (string sql in scripts) { if ( sql.Contains("--CREATE TABLE") || sql.Contains("--CREATE AUDIT TABLE") || sql.StartsWith("--APPEND AUDIT") || sql.StartsWith("--PRIMARY KEY FOR TABLE")) { var command = new SqlCommand(sql, connection); command.ExecuteNonQuery(); } } } catch (Exception ex) { throw; } finally { if (connection != null) { connection.Close(); } } //3. Copy data with BCP one table at a time this.CopyData(settings); //4. Run full installer on the target database var setup = new InstallSetup() { ConnectionString = settings.GetCloudConnectionString(), InstallStatus = InstallStatusConstants.Upgrade, }; UpgradeInstaller.UpgradeDatabase(setup); }
/// <summary> /// Performs an install of a database /// </summary> public void Install(InstallSetup setup) { if (setup.InstallStatus == InstallStatusConstants.Create) { //Conection cannot reference an existing database if (SqlServers.TestConnectionString(setup.ConnectionString)) throw new Exception("The connection string references an existing database."); //The new database name must be specified if (string.IsNullOrEmpty(setup.NewDatabaseName)) throw new Exception("A new database name was not specified."); //The connection string and the new database name must be the same var builder = new System.Data.SqlClient.SqlConnectionStringBuilder(setup.ConnectionString); if (builder.InitialCatalog.ToLower() != setup.NewDatabaseName.ToLower()) throw new Exception("A new database name does not match the specified connection string."); SqlServers.CreateDatabase(setup); } else if (setup.InstallStatus == InstallStatusConstants.Upgrade) { //The connection string must reference an existing database if (!SqlServers.TestConnectionString(setup.ConnectionString)) throw new Exception("The connection string does not reference a valid database."); } try { UpgradeInstaller.UpgradeDatabase(setup); } catch (InvalidSQLException ex) { var sb = new StringBuilder(); sb.AppendLine(); sb.AppendLine("BEGIN ERROR SQL"); sb.AppendLine(ex.SQL); sb.AppendLine("END ERROR SQL"); sb.AppendLine(); Console.WriteLine(sb.ToString()); UpgradeInstaller.LogError(ex, sb.ToString()); throw; } catch (Exception ex) { throw; } }
/// <summary> /// Performs an install of a database /// </summary> public override void Install(System.Collections.IDictionary stateSaver) { base.Install(stateSaver); var commandParams = GetCommandLineParameters(); var paramUICount = 0; var setup = new InstallSetup(); if (commandParams.Count > 0) { if (commandParams.ContainsKey(PARAMKEYS_SHOWSQL)) { if (commandParams[PARAMKEYS_SHOWSQL].ToLower() == "true" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == "1" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == string.Empty) { setup.ShowSql = true; } else if (commandParams[PARAMKEYS_SHOWSQL].ToLower() == "false" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == "0") { setup.ShowSql = false; } else { throw new Exception("The /" + PARAMKEYS_SHOWSQL + " parameter must be set to 'true or false'."); } paramUICount++; } if (commandParams.Any(x => PARAMKEYS_TRAN.Contains(x.Key))) { setup.UseTransaction = GetSetting(commandParams, PARAMKEYS_TRAN, true); paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_SKIPNORMALIZE)) { setup.SkipNormalize = true; paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_HASH)) { if (commandParams[PARAMKEYS_HASH].ToLower() == "true" || commandParams[PARAMKEYS_HASH].ToLower() == "1" || commandParams[PARAMKEYS_HASH].ToLower() == string.Empty) { setup.UseHash = true; } else if (commandParams[PARAMKEYS_HASH].ToLower() == "false" || commandParams[PARAMKEYS_HASH].ToLower() == "0") { setup.UseHash = false; } else { throw new Exception("The /" + PARAMKEYS_HASH + " parameter must be set to 'true or false'."); } paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_CHECKONLY)) { setup.CheckOnly = true; paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_QUIET)) { setup.SuppressUI = true; paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_VERSIONWARN)) { if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "all") { setup.AcceptVersionWarningsChangedScripts = true; setup.AcceptVersionWarningsNewScripts = true; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "none") { setup.AcceptVersionWarningsChangedScripts = false; setup.AcceptVersionWarningsNewScripts = false; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "new") { setup.AcceptVersionWarningsNewScripts = true; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "changed") { setup.AcceptVersionWarningsChangedScripts = true; } else { throw new Exception("The /" + PARAMKEYS_VERSIONWARN + " parameter must be set to 'all, none, new, or changed'."); } paramUICount++; } if (GetSetting(commandParams, PARAMKEYS_HELP, false)) { ShowHelp(); return; } //Try to drop database if (commandParams.Any(x => PARAMKEYS_DROP.Contains(x.Key))) { var masterConnectionString = GetSetting(commandParams, PARAMKEYS_MASTERDB, string.Empty); var dbname = commandParams.Where(x => PARAMKEYS_NEWNAME.Contains(x.Key)).Select(x => x.Value).FirstOrDefault(); if (commandParams.Count == 3 && !string.IsNullOrEmpty(masterConnectionString)) { if (!DropDatabase(dbname, masterConnectionString)) { throw new Exception("The database '" + dbname + "' could not dropped."); } Console.WriteLine("Database successfully dropped."); return; } throw new Exception("Invalid drop database configuration."); } setup.ConnectionString = GetSetting(commandParams, PARAMKEYS_APPDB, string.Empty); setup.MasterConnectionString = GetSetting(commandParams, PARAMKEYS_MASTERDB, string.Empty); if (GetSetting(commandParams, PARAMKEYS_UPGRADE, setup.IsUpgrade)) { setup.InstallStatus = InstallStatusConstants.Upgrade; } if (commandParams.Any(x => PARAMKEYS_CREATE.Contains(x.Key))) { setup.InstallStatus = InstallStatusConstants.Create; } if (commandParams.Any(x => PARAMKEYS_UPGRADE.Contains(x.Key)) && commandParams.Any(x => PARAMKEYS_CREATE.Contains(x.Key))) { throw new Exception("You cannot specify both the create and update action."); } if (commandParams.Count(x => PARAMKEYS_NEWNAME.Contains(x.Key)) > 1) { throw new Exception("The new database name was specified more than once."); } if (commandParams.Count(x => PARAMKEYS_MASTERDB.Contains(x.Key)) > 1) { throw new Exception("The master database connection string was specified more than once."); } if (commandParams.Count(x => PARAMKEYS_APPDB.Contains(x.Key)) > 1) { throw new Exception("The connection string was specified more than once."); } //Determine if calling as a script generator if (commandParams.ContainsKey(PARAMKEYS_SCRIPT)) { var scriptAction = commandParams[PARAMKEYS_SCRIPT].ToLower(); switch (scriptAction) { case "versioned": case "unversioned": case "create": break; default: throw new Exception("The script action must be 'create', 'versioned', or 'unversioned'."); } if (!commandParams.ContainsKey(PARAMKEYS_SCRIPTFILE)) { throw new Exception("The '" + PARAMKEYS_SCRIPTFILE + "' parameter must be set for script generation."); } var dumpFile = commandParams[PARAMKEYS_SCRIPTFILE]; if (!IsValidFileName(dumpFile)) { throw new Exception("The '" + PARAMKEYS_SCRIPTFILE + "' parameter is not valid."); } var fileCreate = true; if (commandParams.ContainsKey(PARAMKEYS_SCRIPTFILEACTION) && (commandParams[PARAMKEYS_SCRIPTFILEACTION] + string.Empty) == "append") { fileCreate = false; } if (File.Exists(dumpFile) && fileCreate) { File.Delete(dumpFile); System.Threading.Thread.Sleep(500); } switch (scriptAction) { case "versioned": if (commandParams.ContainsKey(PARAMKEYS_DBVERSION)) { if (!GeneratedVersion.IsValid(commandParams[PARAMKEYS_DBVERSION])) { throw new Exception("The '" + PARAMKEYS_DBVERSION + "' parameter is not valid."); } setup.Version = new GeneratedVersion(commandParams[PARAMKEYS_DBVERSION]); } else { if (string.IsNullOrEmpty(setup.ConnectionString)) { throw new Exception("Generation of versioned scripts requires a '" + PARAMKEYS_DBVERSION + "' parameter or valid connection string."); } else { var s = new nHydrateSetting(); s.Load(setup.ConnectionString); setup.Version = new GeneratedVersion(s.dbVersion); } } Console.WriteLine("Generate Script Started"); setup.InstallStatus = InstallStatusConstants.Upgrade; File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup)); Console.WriteLine("Generated Create Script"); break; case "unversioned": Console.WriteLine("Generate Script Started"); setup.InstallStatus = InstallStatusConstants.Upgrade; setup.Version = UpgradeInstaller._def_Version; File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup)); Console.WriteLine("Generated Create Script"); break; case "create": Console.WriteLine("Generate Script Started"); setup.InstallStatus = InstallStatusConstants.Create; setup.Version = new GeneratedVersion(-1, -1, -1, -1, -1); File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup)); Console.WriteLine("Generated Create Script"); break; } return; } //If we processed all parameters and they were UI then we need to show UI if ((paramUICount < commandParams.Count) || setup.SuppressUI) { setup.NewDatabaseName = commandParams.Where(x => PARAMKEYS_NEWNAME.Contains(x.Key)).Select(x => x.Value).FirstOrDefault(); Install(setup); return; } } UIInstall(setup); }
internal static void ExecuteSQL(SqlConnection connection, SqlTransaction transaction, string sql, InstallSetup setup, List<KeyValuePair<Guid, string>> failedScripts, List<Guid> successOrderScripts) { if (sql.StartsWith("--##METHODCALL")) { CallMethod(sql, connection, transaction, setup); return; } var originalSQL = sql.Trim(); sql = originalSQL; if (string.IsNullOrEmpty(sql)) return; #region Get Script Key var isBody = false; var lines = sql.Split(new char[] { '\n' }, StringSplitOptions.None).ToList(); var key = Guid.NewGuid(); var l = lines.FirstOrDefault(x => x.StartsWith("--MODELID: ")); if (l != null) { lines.Remove(l); l = l.Replace("--MODELID:", string.Empty).Trim(); sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run //if (!Guid.TryParse(l, out key)) key = Guid.NewGuid(); } else { l = lines.FirstOrDefault(x => x.StartsWith("--MODELID,BODY: ")); if (l != null) { lines.Remove(l); l = l.Replace("--MODELID,BODY:", string.Empty).Trim(); sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run if (!Guid.TryParse(l, out key)) key = Guid.NewGuid(); else isBody = true; } } #endregion if (string.IsNullOrEmpty(sql)) return; #region Try to remove objects before creation var dropObjectName = string.Empty; var dropSQL = GetSQLDropScript(sql); //Add a bit of convenience for dropping DB objects before creation if (!string.IsNullOrEmpty(dropSQL)) { try { if (!setup.CheckOnly) { var dropCommand = new System.Data.SqlClient.SqlCommand(dropSQL, connection); dropCommand.Transaction = transaction; dropCommand.CommandTimeout = 300; SqlServers.ExecuteCommand(dropCommand); } } catch (Exception ex) { //Ignore. The scripts should not need this. It has been added for convenience } } #endregion var command = new System.Data.SqlClient.SqlCommand(sql, connection); command.Transaction = transaction; command.CommandTimeout = Math.Max(300, connection.ConnectionTimeout); try { if (!setup.CheckOnly) { if (setup.ShowSql && !string.IsNullOrEmpty(sql)) { var debugText = "[" + DateTime.Now.ToString() + "]\r\n"; const int MAX_SQL = 500; var sqlLength = Math.Min(sql.Length, MAX_SQL); debugText += sql.Substring(0, sqlLength); if (sqlLength == MAX_SQL) debugText += "..."; debugText += "\r\n\r\n"; Console.WriteLine(debugText); } _timer.Restart(); SqlServers.ExecuteCommand(command); _timer.Stop(); //System.Diagnostics.Debug.WriteLine("Elapsed: " + _timer.ElapsedMilliseconds + " / " + sql.Split('\n').First()); //Alert user of what is running if (successOrderScripts != null && isBody) successOrderScripts.Add(key); } } catch (System.Data.SqlClient.SqlException sqlexp) { if ((sqlexp.Number == 1779) && sql.StartsWith("--PRIMARY KEY FOR TABLE")) { //Ignore this error return; } else if ((sqlexp.Number == 1781) && sql.StartsWith("--DEFAULTS FOR TABLE")) { //Ignore this error return; } else if (failedScripts != null) { //Ignore this error, we will re-process it failedScripts.Add(new KeyValuePair<Guid, string>(key, originalSQL)); return; } else { if (setup.SuppressUI) throw new InvalidSQLException(sqlexp.Message, sqlexp) { SQL = sql, FileName = setup.DebugScriptName }; else if (!SkipScriptPrompt(new InvalidSQLException(sqlexp.Message, sqlexp) { SQL = sql, FileName = setup.DebugScriptName })) throw new HandledSQLException(sqlexp.Message, sqlexp); } } catch (Exception ex) { throw; } }
/// <summary> /// Returns the upgrade script for the specified database /// </summary> public string GetScript(InstallSetup setup) { if (string.IsNullOrEmpty(setup.ConnectionString) && setup.Version == null) throw new Exception("The connection string must be set."); if (setup.SkipSections == null) setup.SkipSections = new List<string>(); return UpgradeInstaller.GetScript(setup); }
public static void RunEmbeddedFile(NpgsqlConnection connection, NpgsqlTransaction transaction, string resourceFileName, List <KeyValuePair <Guid, string> > failedScripts, nHydrateDbObjectList _databaseItems, InstallSetup setup) { RunEmbeddedFile(connection, transaction, resourceFileName, failedScripts, null, _databaseItems, setup); }
internal static void ExecuteSQL(SqlConnection connection, SqlTransaction transaction, string sql, InstallSetup setup, List<KeyValuePair<Guid, string>> failedScripts) { ExecuteSQL(connection, transaction, sql, setup, failedScripts, null); }
public static void RunEmbeddedFile(SqlConnection connection, SqlTransaction transaction, string resourceFileName, List<KeyValuePair<Guid, string>> failedScripts, List<nHydrateDbObject> _databaseItems, InstallSetup setup) { RunEmbeddedFile(connection, transaction, resourceFileName, failedScripts, null, _databaseItems, setup); }
public static void RunEmbeddedFile(SqlConnection connection, SqlTransaction transaction, string resourceFileName, List<KeyValuePair<Guid, string>> failedScripts, List<Guid> successOrderScripts, List<nHydrateDbObject> _databaseItems, InstallSetup setup) { var tempFolder = string.Empty; var scripts = ReadSQLFileSectionsFromResource(resourceFileName, setup); System.Diagnostics.Debug.WriteLine(System.DateTime.Now.ToString("HH:mm:ss.ff") + " - Run embedded file: " + resourceFileName); #region Load script hashes var runScript = !setup.UseHash; var current = _databaseItems.FirstOrDefault(x => x.name.ToLower() == resourceFileName.ToLower()); var hashValue = string.Join("\r\nGO\r\n", ReadSQLFileSectionsFromResource(resourceFileName, setup)).CalculateMD5Hash(); if (current != null) { //if (current.Hash != hashValue) { runScript = true; current.ModifiedDate = DateTime.Now; current.Hash = hashValue; current.Status = "applied"; } } else { runScript = true; current = new nHydrateDbObject() { name = resourceFileName, Hash = hashValue, ModelKey = new Guid(UpgradeInstaller.MODELKEY), type = "FILE", Status = "applied", Changed = true, }; _databaseItems.Add(current); } #endregion if (runScript && !setup.CheckOnly) { foreach (var sql in scripts) { ExecuteSQL(connection, transaction, sql, setup, failedScripts, successOrderScripts); } } }
/// <summary /> private void UIInstall(InstallSetup setup) { if (IdentifyDatabaseConnectionString(setup)) { setup.ConnectionString = this.Settings.GetPrimaryConnectionString(); setup.InstallStatus = InstallStatusConstants.Upgrade; if (this.Action == ActionTypeConstants.Create) { setup.InstallStatus = InstallStatusConstants.Create; UpgradeInstaller.UpgradeDatabase(setup); } else if (this.Action == ActionTypeConstants.Upgrade) { UpgradeInstaller.UpgradeDatabase(setup); } else if (this.Action == ActionTypeConstants.AzureCopy) { UpgradeInstaller.AzureCopyDatabase(this.Settings); } } }
private bool IdentifyDatabaseConnectionString(InstallSetup setup) { var F = new IdentifyDatabaseForm(setup); if (F.ShowDialog() == DialogResult.OK) { this.Action = F.Action; this.Settings = F.Settings; return true; } return false; }
public static string[] ReadSQLFileSectionsFromResource(string resourceFileName, InstallSetup setup) { if (string.IsNullOrEmpty(resourceFileName)) { return new string[] { } } ; var skipSectionsLowered = new List <string>(); var skipSections = setup.SkipSections.ToList(); if (skipSections != null) { skipSections.Where(x => x != null).ToList().ForEach(x => skipSectionsLowered.Add(x.ToLower())); } #region Load Full Script var fullScript = string.Empty; if (resourceFileName.ToLower().EndsWith(".pgsql")) { var asm = Assembly.GetExecutingAssembly(); var manifestStream = asm.GetManifestResourceStream(resourceFileName); try { using (var sr = new System.IO.StreamReader(manifestStream)) { fullScript = sr.ReadToEnd(); } } catch { } finally { manifestStream.Close(); } } else if (resourceFileName.ToLower().EndsWith(".zip")) { var tempPath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString()); Directory.CreateDirectory(tempPath); var zipPath = Path.Combine(tempPath, Guid.NewGuid().ToString()); var asm = Assembly.GetExecutingAssembly(); var manifestStream = asm.GetManifestResourceStream(resourceFileName); try { using (var fileStream = File.Create(zipPath)) { manifestStream.CopyTo(fileStream); } } catch { } finally { manifestStream.Close(); } using (var archive = System.IO.Compression.ZipFile.Open(zipPath, System.IO.Compression.ZipArchiveMode.Update)) { archive.ExtractToDirectory(tempPath); } System.Threading.Thread.Sleep(250); File.Delete(zipPath); var files = Directory.GetFiles(tempPath, "*.*").OrderBy(x => x).ToList(); files.ForEach(x => fullScript += (File.ReadAllText(x) + "\r\n--GO\r\n")); System.Threading.Thread.Sleep(250); files.ForEach(x => File.Delete(x)); System.Threading.Thread.Sleep(250); Directory.Delete(tempPath); } else { return(new string[] { }); } #endregion var retval = new ArrayList(); var sb = new StringBuilder(); var allLines = fullScript.Replace("\r\n", "\n").Replace("\r", "\n").Split('\n'); var skippingQueue = new List <string>(); foreach (var lineText in allLines) { //If we are currently NOT skipping script then process the SQL //Check to determine if this is a skip section start if (lineText.ToUpper().StartsWith("--##SECTION BEGIN [")) { var sectionName = GetSkipSectionName(lineText).ToLower(); if (skipSectionsLowered.Contains(sectionName)) { //We are now skipping script skippingQueue.Add(sectionName); } } else if (lineText.ToUpper().StartsWith("--##SECTION END [")) { if (skippingQueue.Count > 0) { var sectionName = GetSkipSectionName(lineText).ToLower(); if (skippingQueue.Last().ToLower() == sectionName) { //We are now skipping script skippingQueue.RemoveAt(0); } } else { //Do Nothing } } else if (skippingQueue.Count == 0) { if (lineText.ToUpper().Trim() == "--GO") { var s = sb.ToString(); s = s.Trim(); retval.Add(s); sb = new StringBuilder(); } else if (lineText.ToUpper().StartsWith("--##METHODCALL")) { retval.Add(lineText); sb = new StringBuilder(); } else { var s = lineText; if (s.EndsWith("\r")) { s = lineText.Substring(0, lineText.Length - 1); } sb.AppendLine(s); } } } //Last string if (!string.IsNullOrEmpty(sb.ToString())) { retval.Add(sb.ToString()); } return((string[])retval.ToArray(typeof(string))); }
internal static void ExecuteSQL(NpgsqlConnection connection, NpgsqlTransaction transaction, string sql, InstallSetup setup, List <KeyValuePair <Guid, string> > failedScripts, List <Guid> successOrderScripts) { if (sql.StartsWith("--##METHODCALL")) { CallMethod(sql, connection, transaction, setup); return; } //Test for empty statements var originalSQL = sql.Trim(); sql = originalSQL; if (string.IsNullOrEmpty(sql)) { return; } //Test for noop statements (all comments/empty strings) var lines = sql.BreakLines().TrimAll(); lines.RemoveAll(x => x.StartsWith("--")); lines.RemoveAll(x => x == ""); if ([email protected]()) { return; } lines = sql.BreakLines().TrimAll(); //Reset #region Get Script Key var isBody = false; var key = Guid.NewGuid(); var l = lines.FirstOrDefault(x => x.StartsWith("--MODELID: ")); if (l != null) { lines.Remove(l); l = l.Replace("--MODELID:", string.Empty).Trim(); sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run //if (!Guid.TryParse(l, out key)) key = Guid.NewGuid(); } else { l = lines.FirstOrDefault(x => x.StartsWith("--MODELID,BODY: ")); if (l != null) { lines.Remove(l); l = l.Replace("--MODELID,BODY:", string.Empty).Trim(); sql = string.Join("\n", lines.ToArray()); //Remove the model key from the SQL before run if (!Guid.TryParse(l, out key)) { key = Guid.NewGuid(); } else { isBody = true; } } } #endregion if (string.IsNullOrEmpty(sql)) { return; } #region Try to remove objects before creation var dropObjectName = string.Empty; var dropSQL = GetSQLDropScript(sql); //Add a bit of convenience for dropping DB objects before creation if (!string.IsNullOrEmpty(dropSQL)) { try { if (!setup.CheckOnly) { var dropCommand = new NpgsqlCommand(dropSQL, connection); dropCommand.Transaction = transaction; dropCommand.CommandTimeout = 0; DatabaseServer.ExecuteCommand(dropCommand); } } catch (Exception ex) { //Ignore. The scripts should not need this. It has been added for convenience } } #endregion var command = new NpgsqlCommand(sql, connection); command.Transaction = transaction; command.CommandTimeout = 0; try { if (!setup.CheckOnly) { var debugText = "[" + DateTime.Now.ToString() + "]\r\n"; const int MAX_SQL = 500; var sqlLength = Math.Min(sql.Length, MAX_SQL); debugText += sql.Substring(0, sqlLength); if (sqlLength == MAX_SQL) { debugText += "..."; } debugText += "\r\n\r\n"; Log.Verbose(debugText); _timer.Restart(); DatabaseServer.ExecuteCommand(command); _timer.Stop(); Log.Debug <long, string>("Time:{Elapsed:000} Sql:{sql}", _timer.ElapsedMilliseconds, sql); if (successOrderScripts != null && isBody) { successOrderScripts.Add(key); } } } catch (NpgsqlException sqlexp) { if (failedScripts != null) { //Ignore this error, we will re-process it failedScripts.Add(new KeyValuePair <Guid, string>(key, originalSQL)); return; } else { throw new InvalidSQLException(sqlexp.Message, sqlexp) { SQL = sql, FileName = setup.DebugScriptName }; } } catch (Exception ex) { throw; } finally { if (command != null) { command.Dispose(); } } }
internal static void ExecuteSQL(NpgsqlConnection connection, NpgsqlTransaction transaction, string sql, InstallSetup setup) { ExecuteSQL(connection, transaction, sql, setup, null); }
public void Run(InstallSettings settings) { //STEPS TO COPY DATABASE TO AZURE //1. Verify that target is a blank database //2. Execute only tables schemas with no defaults, relations, etc //3. Copy data with BCP one table at a time //4. Run full installer on the target database //1. Verify that target is a blank database if (!this.TargetIsBlank(settings)) throw new Exception("The target database must be empty!"); //2. Execute only tables schemas and PK with no defaults, relations, etc Assembly assem = Assembly.GetExecutingAssembly(); string[] resourceNames = assem.GetManifestResourceNames(); var resourceName = resourceNames.FirstOrDefault(x => x.EndsWith(".Create_Scripts.Generated.CreateSchema.sql")); if (string.IsNullOrEmpty(resourceName)) throw new Exception("Could not find the 'CreateSchema.sql' resource!"); var scripts = SqlServers.ReadSQLFileSectionsFromResource(resourceName, new InstallSetup()); SqlConnection connection = null; try { connection = new SqlConnection(settings.GetCloudConnectionString()); connection.Open(); ////Create version table //var sb = new StringBuilder(); //sb.AppendLine("if not exists(select * from sysobjects where name = '__nhydrateschema' and xtype = 'U')"); //sb.AppendLine("BEGIN"); //sb.AppendLine("CREATE TABLE [__nhydrateschema] ("); //sb.AppendLine("[dbVersion] [varchar] (50) NOT NULL,"); //sb.AppendLine("[LastUpdate] [datetime] NOT NULL,"); //sb.AppendLine("[ModelKey] [uniqueidentifier] NOT NULL,"); //sb.AppendLine("[History] [text] NOT NULL"); //sb.AppendLine(")"); //sb.AppendLine("--PRIMARY KEY FOR TABLE"); //sb.AppendLine("if not exists(select * from sysobjects where name = '__pk__nhydrateschema' and xtype = 'PK')"); //sb.AppendLine("ALTER TABLE [__nhydrateschema] WITH NOCHECK ADD CONSTRAINT [__pk__nhydrateschema] PRIMARY KEY CLUSTERED ([ModelKey])"); //sb.AppendLine("END"); //var command2 = new SqlCommand(sb.ToString(), connection); //command2.ExecuteNonQuery(); foreach (string sql in scripts) { if ( sql.Contains("--CREATE TABLE") || sql.Contains("--CREATE AUDIT TABLE") || sql.StartsWith("--APPEND AUDIT") || sql.StartsWith("--PRIMARY KEY FOR TABLE")) { var command = new SqlCommand(sql, connection); command.ExecuteNonQuery(); } } } catch (Exception ex) { throw; } finally { if (connection != null) connection.Close(); } //3. Copy data with BCP one table at a time this.CopyData(settings); //4. Run full installer on the target database var setup = new InstallSetup() { ConnectionString = settings.GetCloudConnectionString(), InstallStatus = InstallStatusConstants.Upgrade, }; UpgradeInstaller.UpgradeDatabase(setup); }
internal static void ExecuteSQL(SqlConnection connection, SqlTransaction transaction, string sql, InstallSetup setup) { ExecuteSQL(connection, transaction, sql, setup, null); }
public static void RunEmbeddedFile(NpgsqlConnection connection, NpgsqlTransaction transaction, string resourceFileName, nHydrateDbObjectList _databaseItems, InstallSetup setup) { RunEmbeddedFile(connection, transaction, resourceFileName, null, _databaseItems, setup); }
/// <summary> /// Performs an install of a database /// </summary> public override void Install(System.Collections.IDictionary stateSaver) { base.Install(stateSaver); var commandParams = GetCommandLineParameters(); var paramUICount = 0; var setup = new InstallSetup(); if (commandParams.Count > 0) { if (commandParams.ContainsKey(PARAMKEYS_SHOWSQL)) { if (commandParams[PARAMKEYS_SHOWSQL].ToLower() == "true" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == "1" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == string.Empty) setup.ShowSql = true; else if (commandParams[PARAMKEYS_SHOWSQL].ToLower() == "false" || commandParams[PARAMKEYS_SHOWSQL].ToLower() == "0") setup.ShowSql = false; else throw new Exception("The /" + PARAMKEYS_SHOWSQL + " parameter must be set to 'true or false'."); paramUICount++; } if (commandParams.Any(x => PARAMKEYS_TRAN.Contains(x.Key))) { setup.UseTransaction = GetSetting(commandParams, PARAMKEYS_TRAN, true); paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_SKIPNORMALIZE)) { setup.SkipNormalize = true; paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_HASH)) { if (commandParams[PARAMKEYS_HASH].ToLower() == "true" || commandParams[PARAMKEYS_HASH].ToLower() == "1" || commandParams[PARAMKEYS_HASH].ToLower() == string.Empty) setup.UseHash = true; else if (commandParams[PARAMKEYS_HASH].ToLower() == "false" || commandParams[PARAMKEYS_HASH].ToLower() == "0") setup.UseHash = false; else throw new Exception("The /" + PARAMKEYS_HASH + " parameter must be set to 'true or false'."); paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_CHECKONLY)) { setup.CheckOnly = true; paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_QUIET)) { setup.SuppressUI = true; paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_VERSIONWARN)) { if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "all") { setup.AcceptVersionWarningsChangedScripts = true; setup.AcceptVersionWarningsNewScripts = true; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "none") { setup.AcceptVersionWarningsChangedScripts = false; setup.AcceptVersionWarningsNewScripts = false; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "new") { setup.AcceptVersionWarningsNewScripts = true; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "changed") { setup.AcceptVersionWarningsChangedScripts = true; } else { throw new Exception("The /" + PARAMKEYS_VERSIONWARN + " parameter must be set to 'all, none, new, or changed'."); } paramUICount++; } if (GetSetting(commandParams, PARAMKEYS_HELP, false)) { ShowHelp(); return; } //Try to drop database if (commandParams.Any(x => PARAMKEYS_DROP.Contains(x.Key))) { var masterConnectionString = GetSetting(commandParams, PARAMKEYS_MASTERDB, string.Empty); var dbname = commandParams.Where(x => PARAMKEYS_NEWNAME.Contains(x.Key)).Select(x => x.Value).FirstOrDefault(); if (commandParams.Count == 3 && !string.IsNullOrEmpty(masterConnectionString)) { if (!DropDatabase(dbname, masterConnectionString)) throw new Exception("The database '" + dbname + "' could not dropped."); Console.WriteLine("Database successfully dropped."); return; } throw new Exception("Invalid drop database configuration."); } setup.ConnectionString = GetSetting(commandParams, PARAMKEYS_APPDB, string.Empty); setup.MasterConnectionString = GetSetting(commandParams, PARAMKEYS_MASTERDB, string.Empty); if (GetSetting(commandParams, PARAMKEYS_UPGRADE, setup.IsUpgrade)) setup.InstallStatus = InstallStatusConstants.Upgrade; if (commandParams.Any(x => PARAMKEYS_CREATE.Contains(x.Key))) setup.InstallStatus = InstallStatusConstants.Create; if (commandParams.Any(x => PARAMKEYS_UPGRADE.Contains(x.Key)) && commandParams.Any(x => PARAMKEYS_CREATE.Contains(x.Key))) throw new Exception("You cannot specify both the create and update action."); if (commandParams.Count(x => PARAMKEYS_NEWNAME.Contains(x.Key)) > 1) throw new Exception("The new database name was specified more than once."); if (commandParams.Count(x => PARAMKEYS_MASTERDB.Contains(x.Key)) > 1) throw new Exception("The master database connection string was specified more than once."); if (commandParams.Count(x => PARAMKEYS_APPDB.Contains(x.Key)) > 1) throw new Exception("The connection string was specified more than once."); //Determine if calling as a script generator if (commandParams.ContainsKey(PARAMKEYS_SCRIPT)) { var scriptAction = commandParams[PARAMKEYS_SCRIPT].ToLower(); switch (scriptAction) { case "versioned": case "unversioned": case "create": break; default: throw new Exception("The script action must be 'create', 'versioned', or 'unversioned'."); } if (!commandParams.ContainsKey(PARAMKEYS_SCRIPTFILE)) throw new Exception("The '" + PARAMKEYS_SCRIPTFILE + "' parameter must be set for script generation."); var dumpFile = commandParams[PARAMKEYS_SCRIPTFILE]; if (!IsValidFileName(dumpFile)) throw new Exception("The '" + PARAMKEYS_SCRIPTFILE + "' parameter is not valid."); var fileCreate = true; if (commandParams.ContainsKey(PARAMKEYS_SCRIPTFILEACTION) && (commandParams[PARAMKEYS_SCRIPTFILEACTION] + string.Empty) == "append") fileCreate = false; if (File.Exists(dumpFile) && fileCreate) { File.Delete(dumpFile); System.Threading.Thread.Sleep(500); } switch (scriptAction) { case "versioned": if (commandParams.ContainsKey(PARAMKEYS_DBVERSION)) { if (!GeneratedVersion.IsValid(commandParams[PARAMKEYS_DBVERSION])) throw new Exception("The '" + PARAMKEYS_DBVERSION + "' parameter is not valid."); setup.Version = new GeneratedVersion(commandParams[PARAMKEYS_DBVERSION]); } else { if (string.IsNullOrEmpty(setup.ConnectionString)) throw new Exception("Generation of versioned scripts requires a '" + PARAMKEYS_DBVERSION + "' parameter or valid connection string."); else { var s = new nHydrateSetting(); s.Load(setup.ConnectionString); setup.Version = new GeneratedVersion(s.dbVersion); } } Console.WriteLine("Generate Script Started"); setup.InstallStatus = InstallStatusConstants.Upgrade; File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup)); Console.WriteLine("Generated Create Script"); break; case "unversioned": Console.WriteLine("Generate Script Started"); setup.InstallStatus = InstallStatusConstants.Upgrade; setup.Version = UpgradeInstaller._def_Version; File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup)); Console.WriteLine("Generated Create Script"); break; case "create": Console.WriteLine("Generate Script Started"); setup.InstallStatus = InstallStatusConstants.Create; setup.Version = new GeneratedVersion(-1, -1, -1, -1, -1); File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup)); Console.WriteLine("Generated Create Script"); break; } return; } //If we processed all parameters and they were UI then we need to show UI if ((paramUICount < commandParams.Count) || setup.SuppressUI) { setup.NewDatabaseName = commandParams.Where(x => PARAMKEYS_NEWNAME.Contains(x.Key)).Select(x => x.Value).FirstOrDefault(); Install(setup); return; } } UIInstall(setup); }
public static void RunEmbeddedFile(NpgsqlConnection connection, NpgsqlTransaction transaction, string resourceFileName, List <KeyValuePair <Guid, string> > failedScripts, List <Guid> successOrderScripts, nHydrateDbObjectList _databaseItems, InstallSetup setup) { var timer = Stopwatch.StartNew(); var tempFolder = string.Empty; var scripts = ReadSQLFileSectionsFromResource(resourceFileName, setup); Log.Verbose(TheDate + " Start File=" + Extensions.StripResourceAssem(resourceFileName)); #region Load script hashes var runScript = !setup.UseHash; var current = _databaseItems.FirstOrDefault(x => x.name.ToLower() == resourceFileName.ToLower()); var hashValue = string.Join("\r\n--GO\r\n", ReadSQLFileSectionsFromResource(resourceFileName, setup)).CalculateMD5Hash(); if (current != null) { //if (current.Hash != hashValue) { runScript = true; current.ModifiedDate = DateTime.Now; current.Hash = hashValue; current.Status = "applied"; } } else { runScript = true; current = new nHydrateDbObject() { name = resourceFileName, Hash = hashValue, ModelKey = new Guid(UpgradeInstaller.MODELKEY), type = "FILE", Status = "applied", Changed = true, }; _databaseItems.Add(current); } #endregion if (runScript && !setup.CheckOnly) { foreach (var sql in scripts) { ExecuteSQL(connection, transaction, sql, setup, failedScripts, successOrderScripts); } } timer.Start(); Log.Verbose(TheDate + " End File=" + Extensions.StripResourceAssem(resourceFileName) + ", Elapsed=" + timer.FormattedTime()); }
private static void CallMethod(string text, SqlConnection connection, SqlTransaction transaction, InstallSetup setup) { var cleaned = string.Empty; try { cleaned = text .Replace("--##METHODCALL", string.Empty) .Replace("[", string.Empty) .Replace("]", string.Empty) .Trim(); var arr = cleaned.Split('.'); var methodName = arr.Last(); var typeName = string.Join(".", arr.Take(arr.Length - 1)); Type type = Type.GetType(typeName); var methodType = type.GetMethod(methodName); if (methodType == null) throw new Exception("Method: '" + methodName + "' not implemented"); if (methodType.GetParameters().Count() == 2) { methodType.Invoke(null, new object[] { connection, transaction }); } else if (methodType.GetParameters().Count() == 3) { methodType.Invoke(null, new object[] { connection, transaction, setup.ConnectionString }); } else { throw new Exception("Method: '" + methodName + "' does not have valid parameters."); } } catch (Exception ex) { throw new Exception("The external method call '" + cleaned + "' failed.", ex); } }
internal static void ExecuteSQL(NpgsqlConnection connection, NpgsqlTransaction transaction, string sql, InstallSetup setup, List <KeyValuePair <Guid, string> > failedScripts) { ExecuteSQL(connection, transaction, sql, setup, failedScripts, null); }
public static string[] ReadSQLFileSectionsFromResource(string resourceFileName, InstallSetup setup) { if (string.IsNullOrEmpty(resourceFileName)) return new string[] { }; var skipSectionsLowered = new List<string>(); var skipSections = setup.SkipSections.ToList(); if (skipSections != null) skipSections.Where(x => x != null).ToList().ForEach(x => skipSectionsLowered.Add(x.ToLower())); #region Load Full Script var fullScript = string.Empty; if (resourceFileName.ToLower().EndsWith(".sql")) { var asm = Assembly.GetExecutingAssembly(); var manifestStream = asm.GetManifestResourceStream(resourceFileName); try { using (var sr = new System.IO.StreamReader(manifestStream)) { fullScript = sr.ReadToEnd(); } } catch { } finally { manifestStream.Close(); } } else if (resourceFileName.ToLower().EndsWith(".zip")) { var tempPath = Path.Combine(Path.GetTempPath(), Guid.NewGuid().ToString()); Directory.CreateDirectory(tempPath); var zipPath = Path.Combine(tempPath, Guid.NewGuid().ToString()); var asm = Assembly.GetExecutingAssembly(); var manifestStream = asm.GetManifestResourceStream(resourceFileName); try { using (var fileStream = File.Create(zipPath)) { manifestStream.CopyTo(fileStream); } } catch { } finally { manifestStream.Close(); } using (var archive = System.IO.Compression.ZipFile.Open(zipPath, System.IO.Compression.ZipArchiveMode.Update)) { archive.ExtractToDirectory(tempPath); } System.Threading.Thread.Sleep(250); File.Delete(zipPath); var files = Directory.GetFiles(tempPath, "*.*").OrderBy(x => x).ToList(); files.ForEach(x => fullScript += (File.ReadAllText(x) + "\r\nGO\r\n")); System.Threading.Thread.Sleep(250); files.ForEach(x => File.Delete(x)); System.Threading.Thread.Sleep(250); Directory.Delete(tempPath); } else { return new string[] { }; } #endregion var retval = new ArrayList(); var sb = new StringBuilder(); var allLines = fullScript.Replace("\r\n", "\n").Replace("\r", "\n").Split('\n'); var skippingQueue = new List<string>(); foreach (var lineText in allLines) { //If we are currently NOT skipping script then process the SQL //Check to determine if this is a skip section start if (lineText.ToUpper().StartsWith("--##SECTION BEGIN [")) { var sectionName = GetSkipSectionName(lineText).ToLower(); if (skipSectionsLowered.Contains(sectionName)) { //We are now skipping script skippingQueue.Add(sectionName); } } else if (lineText.ToUpper().StartsWith("--##SECTION END [")) { if (skippingQueue.Count > 0) { var sectionName = GetSkipSectionName(lineText).ToLower(); if (skippingQueue.Last().ToLower() == sectionName) { //We are now skipping script skippingQueue.RemoveAt(0); } } else { //Do Nothing } } else if (skippingQueue.Count == 0) { if (lineText.ToUpper().Trim() == "GO") { var s = sb.ToString(); s = s.Trim(); retval.Add(s); sb = new StringBuilder(); } else if (lineText.ToUpper().StartsWith("--##METHODCALL")) { retval.Add(lineText); sb = new StringBuilder(); } else { var s = lineText; if (s.EndsWith("\r")) s = lineText.Substring(0, lineText.Length - 1); sb.AppendLine(s); } } } //Last string if (!string.IsNullOrEmpty(sb.ToString())) retval.Add(sb.ToString()); return (string[])retval.ToArray(typeof(string)); }
private static void CallMethod(string text, NpgsqlConnection connection, NpgsqlTransaction transaction, InstallSetup setup) { var timer = Stopwatch.StartNew(); var cleaned = string.Empty; try { cleaned = text .Replace("--##METHODCALL", string.Empty) .Replace("[", string.Empty) .Replace("]", string.Empty) .Trim(); var arr = cleaned.Split('.'); var methodName = arr.Last(); var typeName = string.Join(".", arr.Take(arr.Length - 1)); Type type = Type.GetType(typeName); var methodType = type.GetMethod(methodName); if (methodType == null) { throw new Exception("Method: '" + methodName + "' not implemented"); } Log.Verbose(TheDate + " Start CallMethod=" + methodName); if (methodType.GetParameters().Count() == 2) { methodType.Invoke(null, new object[] { connection, transaction }); } else if (methodType.GetParameters().Count() == 3) { methodType.Invoke(null, new object[] { connection, transaction, setup.ConnectionString }); } else { throw new Exception("Method: '" + methodName + "' does not have valid parameters."); } timer.Stop(); Log.Verbose(TheDate + " End CallMethod=" + methodName + ", Elapsed=" + timer.FormattedTime()); } catch (Exception ex) { throw new Exception("The external method call '" + cleaned + "' failed.", ex); } }
internal static void CreateDatabase(InstallSetup setup) { var conn = new System.Data.SqlClient.SqlConnection(); try { conn.ConnectionString = setup.MasterConnectionString; conn.Open(); var cmdCreateDb = new SqlCommand(); var collate = string.Empty; if (!string.IsNullOrEmpty(collate)) collate = " COLLATE " + collate; cmdCreateDb.CommandText = "CREATE DATABASE [" + setup.NewDatabaseName + "]" + collate; cmdCreateDb.CommandType = System.Data.CommandType.Text; cmdCreateDb.Connection = conn; SqlServers.ExecuteCommand(cmdCreateDb); var sb = new StringBuilder(); sb.AppendLine("declare @databasename nvarchar(500)"); sb.AppendLine("set @databasename = (select [name] from master.sys.master_files where database_id = (select top 1 [dbid] from master.sys.sysdatabases where name = '" + setup.NewDatabaseName + "' and [type] = 0))"); sb.AppendLine("exec('ALTER DATABASE [" + setup.NewDatabaseName + "] MODIFY FILE (NAME = [' + @databasename + '], MAXSIZE = UNLIMITED, FILEGROWTH = 10MB)')"); sb.AppendLine("set @databasename = (select [name] from master.sys.master_files where database_id = (select top 1 [dbid] from master.sys.sysdatabases where name = '" + setup.NewDatabaseName + "' and [type] = 1))"); sb.AppendLine("exec('ALTER DATABASE [" + setup.NewDatabaseName + "] MODIFY FILE (NAME = [' + @databasename + '], MAXSIZE = UNLIMITED, FILEGROWTH = 10MB)')"); cmdCreateDb.CommandText = sb.ToString(); SqlServers.ExecuteCommand(cmdCreateDb); } catch { throw; } finally { if (conn != null) conn.Close(); System.Threading.Thread.Sleep(1000); } }
public static List<string> GetEmbeddedScripts(string resourceFileName, InstallSetup setup) { var retval = new List<string>(); var tempFolder = string.Empty; var scripts = ReadSQLFileSectionsFromResource(resourceFileName, setup); foreach (var sql in scripts) { retval.Add(sql); } return retval; }
public static void RunEmbeddedFile(SqlConnection connection, SqlTransaction transaction, string resourceFileName, List<nHydrateDbObject> _databaseItems, InstallSetup setup) { RunEmbeddedFile(connection, transaction, resourceFileName, null, _databaseItems, setup); }
/// <summary> /// Performs an install of a database /// </summary> public override void Install(System.Collections.IDictionary stateSaver) { //base.Install(stateSaver); var commandParams = stateSaver as Dictionary <string, string>; foreach (var action in GetDatabaseActions() .Select(x => Activator.CreateInstance(x) as IDatabaseAction) .OrderBy(x => x.SortOrder) .ToList()) { action.Execute(commandParams); } var paramUICount = 0; var setup = new InstallSetup(); if (commandParams.Count > 0) { if (commandParams.Any(x => PARAMKEYS_TRAN.Contains(x.Key))) { setup.UseTransaction = GetSetting(commandParams, PARAMKEYS_TRAN, true); paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_SKIPNORMALIZE)) { setup.SkipNormalize = true; paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_HASH)) { if (commandParams[PARAMKEYS_HASH].ToLower() == "true" || commandParams[PARAMKEYS_HASH].ToLower() == "1" || commandParams[PARAMKEYS_HASH].ToLower() == string.Empty) { setup.UseHash = true; } else if (commandParams[PARAMKEYS_HASH].ToLower() == "false" || commandParams[PARAMKEYS_HASH].ToLower() == "0") { setup.UseHash = false; } else { throw new Exception("The /" + PARAMKEYS_HASH + " parameter must be set to 'true or false'."); } paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_CHECKONLY)) { setup.CheckOnly = true; paramUICount++; } if (commandParams.ContainsKey(PARAMKEYS_VERSIONWARN)) { if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "all") { setup.AcceptVersionWarningsChangedScripts = true; setup.AcceptVersionWarningsNewScripts = true; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "none") { setup.AcceptVersionWarningsChangedScripts = false; setup.AcceptVersionWarningsNewScripts = false; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "new") { setup.AcceptVersionWarningsNewScripts = true; } else if (commandParams[PARAMKEYS_VERSIONWARN].ToLower() == "changed") { setup.AcceptVersionWarningsChangedScripts = true; } else { throw new Exception("The /" + PARAMKEYS_VERSIONWARN + " parameter must be set to 'all, none, new, or changed'."); } paramUICount++; } if (GetSetting(commandParams, PARAMKEYS_HELP, false)) { ShowHelp(); return; } setup.ConnectionString = GetSetting(commandParams, PARAMKEYS_APPDB, string.Empty); //Determine if calling as a script generator if (commandParams.ContainsKey(PARAMKEYS_SCRIPT)) { var scriptAction = commandParams[PARAMKEYS_SCRIPT].ToLower(); switch (scriptAction) { case "versioned": break; case "unversioned": break; default: throw new Exception("The script action must be 'versioned' or 'unversioned'."); } if (!commandParams.ContainsKey(PARAMKEYS_SCRIPTFILE)) { throw new Exception("The '" + PARAMKEYS_SCRIPTFILE + "' parameter must be set for script generation."); } var dumpFile = commandParams[PARAMKEYS_SCRIPTFILE]; if (!IsValidFileName(dumpFile)) { throw new Exception("The '" + PARAMKEYS_SCRIPTFILE + "' parameter is not valid."); } var fileCreate = true; if (commandParams.ContainsKey(PARAMKEYS_SCRIPTFILEACTION) && (commandParams[PARAMKEYS_SCRIPTFILEACTION] + string.Empty) == "append") { fileCreate = false; } if (File.Exists(dumpFile) && fileCreate) { File.Delete(dumpFile); System.Threading.Thread.Sleep(500); } switch (scriptAction) { case "versioned": if (commandParams.ContainsKey(PARAMKEYS_DBVERSION)) { if (!GeneratedVersion.IsValid(commandParams[PARAMKEYS_DBVERSION])) { throw new Exception("The '" + PARAMKEYS_DBVERSION + "' parameter is not valid."); } setup.Version = new GeneratedVersion(commandParams[PARAMKEYS_DBVERSION]); } else { if (string.IsNullOrEmpty(setup.ConnectionString)) { throw new Exception("Generation of versioned scripts requires a '" + PARAMKEYS_DBVERSION + "' parameter or valid connection string."); } else { var s = new nHydrateSetting(); s.Load(setup.ConnectionString); setup.Version = new GeneratedVersion(s.dbVersion); } } File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup)); break; case "unversioned": setup.Version = UpgradeInstaller._def_Version; File.AppendAllText(dumpFile, UpgradeInstaller.GetScript(setup)); break; } return; } //If we processed all parameters and they were UI then we need to show UI if (paramUICount < commandParams.Count) { Install(setup); return; } } Log.Information("Invalid configuration"); }
private bool CreateDatabase() { var error = false; var connectString = SqlServers.BuildConnectionString(optCreationIntegratedSecurity.Checked, string.Empty, cboCreationServerName.Text, txtCreationUserName.Text, txtCreationPassword.Text); if (SqlServers.TestConnectionString(connectString) && SqlServers.HasCreatePermissions(connectString)) { try { var setup = new InstallSetup() { MasterConnectionString = connectString, NewDatabaseName = txtCreationDatabaseName.Text, }; SqlServers.CreateDatabase(setup); } catch (Exception ex) { error = true; System.Diagnostics.Debug.WriteLine(ex.ToString()); MessageBox.Show("Could not create database." + Environment.NewLine + ex.Message); } } else { error = true; MessageBox.Show("The account does not have permissions to create a database on this server.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } return error; }