private void comboBoxCreationServerName_DropDown(object sender, System.EventArgs e) { if (cboCreationServerName.Items.Count == 0) { cboCreationServerName.DataSource = SqlServers.GetServers(); } }
private bool CreateDatabase() { bool error = false; string 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 static string CreateFileGroup(string connectionString, string path, string groupName) { try { if (!AllowFileAccess(connectionString)) { return(null); } using (var conn = new System.Data.SqlClient.SqlConnection()) { conn.ConnectionString = connectionString; conn.Open(); //Check and create the file group var da = new SqlDataAdapter($"select * from sys.filegroups where name = '{groupName}'", conn); var ds = new DataSet(); da.Fill(ds); if (ds.Tables[0].Rows.Count > 0) { return(null); //already exists } da = new SqlDataAdapter("select * from sys.master_files where database_id = DB_ID() and type = 0", conn); ds = new DataSet(); da.Fill(ds); var fileID = ds.Tables[0].Rows[0]["name"].ToString(); var builder = new SqlConnectionStringBuilder(connectionString); //Create the new file group using (var command = new SqlCommand($"ALTER DATABASE {builder.InitialCatalog} ADD FILEGROUP {groupName}", conn)) { SqlServers.ExecuteCommand(command); } //If no path specified then just add to the data path if (string.IsNullOrEmpty(path)) { path = GetDataFilePath(connectionString); } //Create N "groupName" files in new file group for (var ii = 1; ii <= 4; ii++) { var newfileName = Path.Combine(path, $"{builder.InitialCatalog}_{groupName}{ii}.ndf"); using (var command = new SqlCommand($"ALTER DATABASE {builder.InitialCatalog} ADD FILE (NAME = {groupName}{ii}, FILENAME = '{newfileName}', SIZE = 64MB, FILEGROWTH = 64MB) TO FILEGROUP {groupName}", conn)) { SqlServers.ExecuteCommand(command); } } return(path); } } catch (Exception ex) { throw; } }
private string[] GetDatabaseNames() { var connectString = SqlServers.BuildConnectionString(optConnectionIntegratedSecurity.Checked, cboConnectionDatabaseName.Text, cboConnectionServerName.Text, txtConnectionUserName.Text, txtConnectionPassword.Text); return(SqlServers.GetDatabaseNames(connectString)); }
public HistoryForm(string connectionString) : this() { List <HistoryItem> historyList = SqlServers.GetHistory(connectionString); foreach (HistoryItem item in historyList.OrderByDescending(x => x.PublishDate)) { lstItem.Items.Add(item.PublishDate.ToString("yyyy-MM-dd HH:mm:ss") + " / " + item.Version); } }
/// <summary> /// Performs an install of a database /// </summary> public void Install(InstallSetup setup) { if (setup.InstallStatus == InstallStatusConstants.Create) { //Connection 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 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(); System.Diagnostics.Trace.WriteLine(sb.ToString()); UpgradeInstaller.LogError(ex, sb.ToString()); throw; } catch (Exception ex) { throw; } }
private void cmdViewHistory_Click(object sender, EventArgs e) { string connectionString = SqlServers.BuildConnectionString(optConnectionIntegratedSecurity.Checked, cboConnectionDatabaseName.Text, cboConnectionServerName.Text, txtConnectionUserName.Text, txtConnectionPassword.Text); if (!SqlServers.TestConnectionString(connectionString)) { MessageBox.Show("The information does not describe a valid connection string.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return; } HistoryForm F = new HistoryForm(connectionString); F.Show(); }
private void buttonConnectionTestConnection_Click(object sender, System.EventArgs e) { string connectString = SqlServers.BuildConnectionString(optConnectionIntegratedSecurity.Checked, cboConnectionDatabaseName.Text, cboConnectionServerName.Text, txtConnectionUserName.Text, txtConnectionPassword.Text); bool valid = SqlServers.TestConnectionString(connectString); if (valid) { MessageBox.Show("Connection Succeeded.", "Information", MessageBoxButtons.OK, MessageBoxIcon.Information); } else { MessageBox.Show("The information does not describe a valid connection string.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } }
private void cmdOK_Click(object sender, System.EventArgs e) { this.SaveSettings(); if (tabControlChooseDatabase.SelectedTab == this.tabPageConnection) { var connectString = SqlServers.BuildConnectionString(optConnectionIntegratedSecurity.Checked, cboConnectionDatabaseName.Text, cboConnectionServerName.Text, txtConnectionUserName.Text, txtConnectionPassword.Text); var valid = SqlServers.TestConnectionString(connectString); if (valid) { this.InstallSettingsUI1.SaveUI(_setup); this.Action = ActionTypeConstants.Upgrade; this.DialogResult = DialogResult.OK; this.Close(); } else { MessageBox.Show("The information does not describe a valid connection string."); } } else if (tabControlChooseDatabase.SelectedTab == this.tabPageCreation) { bool error = false; if (_cbCreateDatabase.Checked) { error = CreateDatabase(); } if (!error) { var outputConnectString = SqlServers.BuildConnectionString(optCreationIntegratedSecurity.Checked, txtCreationDatabaseName.Text, cboCreationServerName.Text, txtCreationUserName.Text, txtCreationPassword.Text); if (SqlServers.TestConnectionString(outputConnectString)) { //_connectionString = outputConnectString; //_databaseName = cboCreationServerName.Text + "." + txtCreationDatabaseName.Text; //_createdDb = true; } this.Action = ActionTypeConstants.Create; this.DialogResult = DialogResult.OK; this.Close(); } } else if (tabControlChooseDatabase.SelectedTab == this.tabPageAzureCopy) { this.Action = ActionTypeConstants.AzureCopy; this.DialogResult = DialogResult.OK; this.Close(); } }
public static void SplitDbFiles(string connectionString) { try { if (!AllowFileAccess(connectionString)) { return; } using (var conn = new System.Data.SqlClient.SqlConnection()) { conn.ConnectionString = connectionString; conn.Open(); var da = new SqlDataAdapter("select * from sys.master_files where database_id = DB_ID() and type = 0", conn); var ds = new DataSet(); da.Fill(ds); if (ds.Tables[0].Rows.Count != 1) { return; } var fileID = ds.Tables[0].Rows[0]["name"].ToString(); var fileName = ds.Tables[0].Rows[0]["physical_name"].ToString(); var fi = new FileInfo(fileName); //Create N more data files var builder = new SqlConnectionStringBuilder(connectionString); for (var ii = 2; ii <= 4; ii++) { var newfileName = Path.Combine(fi.DirectoryName, $"{builder.InitialCatalog}_Data{ii}.ndf"); using (var command = new SqlCommand($"ALTER DATABASE {builder.InitialCatalog} ADD FILE (NAME = data{ii}, FILENAME = '{newfileName}', SIZE = 64MB, FILEGROWTH = 64MB)", conn)) { SqlServers.ExecuteCommand(command); } } //Set original file to grow at same rate using (var command = new SqlCommand($"ALTER DATABASE {builder.InitialCatalog} MODIFY FILE (NAME = {fileID}, FILEGROWTH = 64MB)", conn)) { SqlServers.ExecuteCommand(command); } } } catch (Exception ex) { throw; } }
private void buttonCreationRefresh_Click(object sender, System.EventArgs e) { this.Cursor = Cursors.WaitCursor; try { if (cboConnectionServerName.Items.Count == 0) { cboCreationServerName.DataSource = SqlServers.GetServers(); } } catch (Exception ex) { throw; } finally { this.Cursor = Cursors.Default; } }
private void ServerName_DropDown(object sender, System.EventArgs e) { this.Cursor = Cursors.WaitCursor; try { if (cboConnectionServerName.Items.Count == 0) { ((ComboBox)sender).DataSource = SqlServers.GetServers(); } } catch (Exception ex) { throw; } finally { this.Cursor = Cursors.Default; } }
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)) { if (!string.IsNullOrWhiteSpace(txtDiskPath.Text) && !Directory.Exists(txtDiskPath.Text)) { error = true; MessageBox.Show("The specified disk path does not exist.", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); return(error); } try { var setup = new InstallSetup() { MasterConnectionString = connectString, NewDatabaseName = txtCreationDatabaseName.Text, DiskPath = txtDiskPath.Text.Trim(), }; SqlServers.CreateDatabase(setup); } catch (Exception ex) { error = true; System.Diagnostics.Debug.WriteLine(ex.ToString()); MessageBox.Show("Could not create database." + Environment.NewLine + ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error); } } 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); }
/// <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."); } } UpgradeInstaller.UpgradeDatabase(setup); }
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); }
private void buttonCreationRefresh_Click(object sender, System.EventArgs e) { cboCreationServerName.DataSource = SqlServers.GetServers(); }