private void cmbDatabases_TextChanged(object sender, EventArgs e) { var settingData = StaticObjects.GetSettings(); if (string.IsNullOrEmpty(settingData)) { MessageBox.Show("Configuration is not complete."); } else { selectedDatabase = cmbDatabases.Text; var settings = JsonConvert.DeserializeObject <SettingClass>(settingData); try { using (SqlConnection con = new SqlConnection()) { string connectionString = string.Format("Server={0},{4};Database={1};User Id={2};Password={3};", settings.SQLServer, "master", settings.SQLUsername, settings.SQLPassword, settings.SQLPort); con.ConnectionString = connectionString; using (var cmd = new SqlCommand()) { cmd.CommandText = string.Format("select QUOTENAME(TABLE_SCHEMA) + '.' + QUOTENAME(TABLE_NAME) as tablename from {0}.INFORMATION_SCHEMA.TABLES order by tablename", cmbDatabases.Text); cmd.Connection = con; DataSet ds = new DataSet(); using (var da = new SqlDataAdapter(cmd)) { da.Fill(ds); chkListObjects.Items.Clear(); foreach (DataRow dr in ds.Tables[0].Rows) { chkListObjects.Items.Add(dr["tablename"].ToString()); } } } } } catch (Exception ex) { StaticObjects.log.Error(ex); MessageBox.Show(ex.Message); } } }
private void SourceMigrationForm_Load(object sender, EventArgs e) { var settingData = StaticObjects.GetSettings(); if (string.IsNullOrEmpty(settingData)) { MessageBox.Show("Configuration is not complete."); } else { var settings = JsonConvert.DeserializeObject <SettingClass>(settingData); try { using (SqlConnection con = new SqlConnection()) { string connectionString = string.Format("Server={0},{4};Database={1};User Id={2};Password={3};", settings.SQLServer, "master", settings.SQLUsername, settings.SQLPassword, settings.SQLPort); con.ConnectionString = connectionString; using (var cmd = new SqlCommand()) { cmd.CommandText = "select name from sys.databases"; cmd.Connection = con; DataSet ds = new DataSet(); using (var da = new SqlDataAdapter(cmd)) { da.Fill(ds); cmbDatabases.Items.Clear(); foreach (DataRow dr in ds.Tables[0].Rows) { cmbDatabases.Items.Add(dr["name"].ToString()); } } } } } catch (Exception ex) { StaticObjects.log.Error(ex); MessageBox.Show(ex.Message); } } }
private void backgroundWorker1_DoWork(object sender, DoWorkEventArgs e) { try { backgroundWorker1.ReportProgress(1); //Step 1 > Setting up task functions var script = File.ReadAllText("Scripts/Script1.1.sql"); var settingData = StaticObjects.GetSettings(); var settings = JsonConvert.DeserializeObject <SettingClass>(settingData); var xml = new StringBuilder(); if (!Directory.Exists(settings.MigrationLocation + "/" + selectedDatabase)) { Directory.CreateDirectory(settings.MigrationLocation + "/" + selectedDatabase); Directory.CreateDirectory(settings.MigrationLocation + "/" + selectedDatabase + "/Data/"); } ExecutePatch(script, settings); backgroundWorker1.ReportProgress(2); script = File.ReadAllText("Scripts/Script1.2.sql"); ExecutePatch(script, settings); backgroundWorker1.ReportProgress(3); script = File.ReadAllText("Scripts/Script1.3.sql"); ExecutePatch(script, settings); backgroundWorker1.ReportProgress(4); script = File.ReadAllText("Scripts/Script1.4.sql"); ExecutePatch(script, settings); backgroundWorker1.ReportProgress(5); script = File.ReadAllText("Scripts/Script1.5.sql"); ExecutePatch(script, settings); backgroundWorker1.ReportProgress(6); script = File.ReadAllText("Scripts/Script1.6.sql"); ExecutePatch(script, settings); backgroundWorker1.ReportProgress(7); script = File.ReadAllText("Scripts/Script1.7.sql"); ExecutePatch(script, settings); backgroundWorker1.ReportProgress(8); script = File.ReadAllText("Scripts/Script1.8.sql"); ExecutePatch(script, settings); bool ischecked = false; for (int i = 0; i <= chkListObjects.Items.Count - 1; i++) { if (chkListObjects.GetItemChecked(i)) { ischecked = true; } } StringBuilder sb = new StringBuilder(); if (ischecked) { sb.Append("insert into include_table_list " + Environment.NewLine); for (int i = 0; i <= chkListObjects.Items.Count - 1; i++) { if (chkListObjects.GetItemChecked(i)) { sb.Append(string.Format("select '{0}' " + Environment.NewLine, chkListObjects.Items[i].ToString().Replace("[", "").Replace("]", "").ToLower())); sb.Append("union "); } } var querylines = sb.ToString().Split(new[] { Environment.NewLine }, StringSplitOptions.None); var querylist = querylines.ToList(); querylist.RemoveAt(querylist.Count - 1); var query = string.Join(Environment.NewLine, querylist.ToArray()); ExecuteDataset(query, settings); } backgroundWorker1.ReportProgress(9); script = File.ReadAllText("Scripts/Script2.1.sql"); script = script.Replace("{DBName}", string.Format("'{0}'", selectedDatabase)); script = script.Replace("{TableNames}", string.Format("{0}", "")); var ds = ExecuteDataset(script, settings); foreach (DataRow dr in ds.Tables[0].Rows) { xml.Append(dr[0]); } File.WriteAllText(settings.MigrationLocation + "/" + selectedDatabase + "/V0001__SchemaPatch.sql", xml.ToString()); backgroundWorker1.ReportProgress(10); script = File.ReadAllText("Scripts/Script2.2.sql"); script = script.Replace("{DBName}", string.Format("'{0}'", selectedDatabase)); script = script.Replace("{TableNames}", string.Format("{0}", "")); var dsxml = ExecuteDataset(script, settings); xml = new StringBuilder(); foreach (DataRow dr in dsxml.Tables[0].Rows) { xml.Append(dr[0]); } File.WriteAllText(settings.MigrationLocation + "/" + selectedDatabase + "/V0002__TablePatch.sql", xml.ToString()); backgroundWorker1.ReportProgress(11); script = File.ReadAllText("Scripts/Script3.sql"); script = script.Replace("{Path}", string.Format("'{0}'", settings.MigrationLocation + "/" + selectedDatabase + "/Data/")); script = script.Replace("{DBName}", string.Format("'{0}'", selectedDatabase)); ExecutePatch(script, settings); backgroundWorker1.ReportProgress(12); script = File.ReadAllText("Scripts/Script4.sql"); script = script.Replace("{Path}", string.Format("'{0}'", settings.MigrationLocation + "/" + selectedDatabase + "/Data/")); script = script.Replace("{DBName}", string.Format("'{0}'", selectedDatabase)); script = script.Replace("{User}", string.Format("{0}", settings.SQLUsername)); script = script.Replace("{Pass}", string.Format("{0}", settings.SQLPassword)); script = script.Replace("{DB}", string.Format("{0}", selectedDatabase)); var dsbcp = ExecuteDataset(script, settings); xml = new StringBuilder(); foreach (DataRow dr in dsbcp.Tables[0].Rows) { xml.Append(dr[0]); xml.Append(Environment.NewLine); } File.WriteAllText(settings.MigrationLocation + "/" + selectedDatabase + "/BCP_Patch.bat", xml.ToString()); backgroundWorker1.ReportProgress(13); script = File.ReadAllText("Scripts/Script5.sql"); script = script.Replace("{Path}", string.Format("'{0}'", settings.MigrationLocation + "/" + selectedDatabase + "/Data/")); script = script.Replace("{TableNames}", string.Format("{0}", "")); var dscopy = ExecuteDataset(script, settings); xml = new StringBuilder(); foreach (DataRow dr in dscopy.Tables[0].Rows) { xml.Append(dr[1]); xml.Append(Environment.NewLine); } File.WriteAllText(settings.MigrationLocation + "/" + selectedDatabase + "/V0003__CopyCommand.sql", xml.ToString()); backgroundWorker1.ReportProgress(14); script = File.ReadAllText("Scripts/Script6.sql"); script = script.Replace("{DBName}", string.Format("'{0}'", selectedDatabase)); script = script.Replace("{TableNames}", string.Format("{0}", "")); var dsconstraints = ExecuteDataset(script, settings); xml = new StringBuilder(); foreach (DataRow dr in dsconstraints.Tables[0].Rows) { xml.Append(dr[0]); xml.Append(Environment.NewLine); } File.WriteAllText(settings.MigrationLocation + "/" + selectedDatabase + "/V0004__Constraints.sql", xml.ToString()); backgroundWorker1.ReportProgress(15); script = File.ReadAllText("Scripts/Script7.sql"); script = script.Replace("{DBName}", string.Format("'{0}'", selectedDatabase)); script = script.Replace("{TableNames}", string.Format("{0}", "")); var dsindex = ExecuteDataset(script, settings); xml = new StringBuilder(); foreach (DataRow dr in dsindex.Tables[0].Rows) { xml.Append(dr[0]); xml.Append(Environment.NewLine); } File.WriteAllText(settings.MigrationLocation + "/" + selectedDatabase + "/V0005__IndexCommand.sql", xml.ToString()); backgroundWorker1.ReportProgress(16); script = File.ReadAllText("Scripts/Script8.sql"); script = script.Replace("{DBName}", string.Format("'{0}'", selectedDatabase)); script = script.Replace("{TableNames}", string.Format("{0}", "")); var dsfkconstraints = ExecuteDataset(script, settings); xml = new StringBuilder(); foreach (DataRow dr in dsfkconstraints.Tables[0].Rows) { xml.Append(dr[0]); xml.Append(Environment.NewLine); } File.WriteAllText(settings.MigrationLocation + "/" + selectedDatabase + "/V0006__ForeignKey.sql", xml.ToString()); backgroundWorker1.ReportProgress(17); } catch (Exception ex) { StaticObjects.log.Error(ex); } }