private void comboBox_Database_SelectedIndexChanged(object sender, EventArgs e) { try { SQLCDCApp obj = new SQLCDCApp(); List <string> dblist = new List <string>(); comboBox_Tables.Items.Clear(); dblist.Add(comboBox_Database.Text.ToString()); DataSet dstables = obj.fn_GetTables(dblist, "Destination"); DataTable dt = dstables.Tables[0]; if (dt.Rows.Count == 0) { comboBox_Tables.Items.Insert(0, "Create New"); return; } foreach (DataRow rw in dt.Rows) { comboBox_Tables.Items.Add(rw[1] + "." + rw[2]); //comboBox_Tables.Items.Add(item.schema + "." + item.name); } comboBox_Tables.Items.Insert(0, "Create New"); comboBox_Tables.Text = comboBox_Tables.Items[1].ToString(); } catch (Exception) { throw; } }
public List <CDC> fn_getselectedtables() { List <DataGridViewRow> rows_with_checked_column = new List <DataGridViewRow>(); SQLCDCApp scdc = new SQLCDCApp(); List <CDC> cdclist = new List <CDC>(); foreach (DataGridViewRow row in dataGridView_tables.Rows) { if (Convert.ToBoolean(row.Cells[0].Value) == true) { rows_with_checked_column.Add(row); } } foreach (DataGridViewRow dgvr in rows_with_checked_column) { CDC cdcobj = new CDC(); if (dgvr.Cells[4].Value.ToString() == "True") { cdcobj.Databasename = dgvr.Cells[1].Value.ToString().Trim(); cdcobj.source_schema = dgvr.Cells[2].Value.ToString().Trim(); cdcobj.source_name = dgvr.Cells[3].Value.ToString().Trim(); cdcobj.capture_instance = textBox_captureinstance.Text.Trim(); cdclist.Add(cdcobj); } } return(cdclist); }
private void button_cdctable_Click(object sender, EventArgs e) { SQLCDCApp scdc = new SQLCDCApp(); List <CDC> cdclist = new List <CDC>(); List <DataGridViewRow> rows_with_checked_column = new List <DataGridViewRow>(); foreach (DataGridViewRow row in dataGridView_tables.Rows) { if (Convert.ToBoolean(row.Cells[0].Value) == true) { rows_with_checked_column.Add(row); } } if (rows_with_checked_column.Count == 0) { MessageBox.Show("Please select a table.", "SQLCDCApp Information"); return; } foreach (DataGridViewRow dgvr in rows_with_checked_column) { CDC cdcobj = new CDC(); // if(dgvr.Cells[4].Value.ToString()=="False") // { cdcobj.Databasename = dgvr.Cells[1].Value.ToString().Trim(); cdcobj.source_schema = dgvr.Cells[2].Value.ToString().Trim(); cdcobj.source_name = dgvr.Cells[3].Value.ToString().Trim(); cdcobj.role_name = textBox_rolename.Text.Trim();; cdcobj.capture_instance = textBox_captureinstance.Text.Trim(); cdcobj.index_name = textBox_indexname.Text.Trim(); cdcobj.captured_column_list = textBox_capturedcollist.Text.Trim(); cdcobj.filegroup_name = textBox_filegroupname.Text.Trim(); if (checkBox_netchanges.Checked == true) { cdcobj.supports_net_changes = 1; } if (checkBox_netchanges.Checked == false) { cdcobj.supports_net_changes = 0; } if (checkBox_allowpartitionswitch.Checked == true) { cdcobj.allow_partition_switch = 1; } if (checkBox_allowpartitionswitch.Checked == false) { cdcobj.allow_partition_switch = 0; } cdclist.Add(cdcobj); } MessageBox.Show(scdc.fn_EnableCDCOnTable(cdclist, true).ToString(), "SQLCDCAPP Information"); fn_ListTables(); }
/// <summary> /// Get change data from the table. /// </summary> /// <param name="ChangeDataType"></param> private void fn_GetChangedData(int ChangeDataType) { SQLCDCApp cdcobj = new SQLCDCApp(); int insertcount = 0, deletecount = 0, updatecount = 0; try { DataTable result = new DataTable(); result = cdcobj.fn_GetChangeData(ChangeDataType, Databasename, tablename, comboBox_capturedinstances.Text, dateTimePicker_FromDataTime.Value, dateTimePicker_ToDateTime.Value); srctable = result; if (result.Columns.Count == 1) { groupBox_ExportData.Enabled = false; MessageBox.Show(result.Rows[0][0].ToString(), "SQLCDCApp Info!!!"); return; } groupBox_ExportData.Enabled = true; if (result.Columns.Contains("__$start_lsn") == true) { result.Columns.Remove("__$start_lsn"); } if (result.Columns.Contains("__$seqval") == true) { result.Columns.Remove("__$seqval"); } if (result.Columns.Contains("__$operation") == true) { result.Columns.Remove("__$operation"); } if (result.Columns.Contains("__$update_mask") == true) { result.Columns.Remove("__$update_mask"); } if (result.Columns.Contains("Operation") == true) { MorphBinaryColumns(result); dataGridView_showresults.DataSource = result; textBox_datacount.Text = "Total Records: " + (dataGridView_showresults.Rows.Count - 1).ToString(); insertcount = result.AsEnumerable() .Count(row => row.Field <string>("Operation") == "INSERT"); deletecount = result.AsEnumerable() .Count(row => row.Field <string>("Operation") == "DELETE"); updatecount = result.AsEnumerable() .Count(row => row.Field <string>("Operation") == "UPDATE AFTER" || row.Field <string>("Operation") == "UPDATE BEFORE"); textBox_Insertcount.Text = "Total Inserts : " + insertcount.ToString(); textBox_Deletecount.Text = "Total Deletes : " + deletecount.ToString(); textBox_updatecount.Text = "Total Updates : " + updatecount.ToString(); } } catch (SqlException) { throw; } }
public void fn_ListDatabases() { SQLCDCApp obj = new SQLCDCApp(); List <SQLCDCApp> _listdatabases = new List <SQLCDCApp>(); Settings1.Default.Server = textBox_Server.Text; Settings1.Default.Password = textBox_password.Text; Settings1.Default.User = textBox_User.Text; Settings1.Default.AuthenticationType = comboBox_Authentication.Text; try { if (string.IsNullOrEmpty(textBox_Server.Text)) { MessageBox.Show("Server name can't be blank", "SQLCDCApp Error"); } if (comboBox_Authentication.SelectedIndex == 1) { if (string.IsNullOrEmpty(textBox_User.Text)) { MessageBox.Show("User can't be blank", "SQLCDCApp Error"); } if (string.IsNullOrEmpty(textBox_password.Text)) { MessageBox.Show("Password can't be blank", "SQLCDCApp Error"); } } _listdatabases = obj.fn_GetDatabases(); dataGridView_Databases.DataSource = _listdatabases; if (dataGridView_Databases.ColumnCount < 3) { DataGridViewCheckBoxColumn cb = new DataGridViewCheckBoxColumn(); cb.Frozen = true; cb.Name = "Select"; cb.Width = 40; dataGridView_Databases.Columns.Insert(0, cb); cb.TrueValue = 1; cb.FalseValue = 0; } dataGridView_Databases.Columns[1].ReadOnly = true; dataGridView_Databases.Columns[2].ReadOnly = true; } catch (Exception ex) { throw; } }
/// <summary> /// initial sync /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button_InitialSync_Click(object sender, EventArgs e) { try { SQLCDCApp obj = new SQLCDCApp(); Cursor.Current = Cursors.WaitCursor; srctable.TableName = tablename.ReplaceFirstOccurrance("_", "."); int _recordcount = obj.fn_ExportToDB(srctable, desttable, comboBox_Database.Text.ToString(), "false", Databasename, comboBox_Database.Text); MessageBox.Show("Number of Records Exported:" + _recordcount.ToString()); Cursor.Current = Cursors.Arrow; }catch (Exception ex) { MessageBox.Show(ex.Message.ToString(), "SQLCDCApp Error !!!"); } }
private void fn_ListTables() { List <Tables> tablelist = new List <Tables>(); SQLCDCApp obj = new SQLCDCApp(); List <string> dblist = new List <string>(); List <DataGridViewRow> rows_with_checked_column = new List <DataGridViewRow>(); foreach (DataGridViewRow row in dataGridView_Databases.Rows) { if (Convert.ToBoolean(row.Cells[0].Value) == true) { rows_with_checked_column.Add(row); } } foreach (DataGridViewRow dgvr in rows_with_checked_column) { if (dgvr.Cells[2].Value.ToString() == "True") { dblist.Add(dgvr.Cells[1].Value.ToString()); } } tablelist = obj.fn_GetTables(dblist); dataGridView_tables.DataSource = tablelist; if (dataGridView_tables.ColumnCount < 5) { DataGridViewCheckBoxColumn cb = new DataGridViewCheckBoxColumn(); cb.Frozen = true; cb.Name = "Select"; cb.Width = 40; dataGridView_tables.Columns.Insert(0, cb); cb.TrueValue = 1; cb.FalseValue = 0; } }
private void CDCData_Load(object sender, EventArgs e) { try { SQLCDCApp scdc = new SQLCDCApp(); Databasename = _lst[_lst.Count - 1]; tablename = _lst[_lst.Count - 2]; _lst.RemoveAt(_lst.Count - 1); _lst.RemoveAt(_lst.Count - 1); dateTimePicker_FromDataTime.CustomFormat = "MM/dd/yyyy hh:mm:ss tt"; dateTimePicker_FromDataTime.Format = DateTimePickerFormat.Custom; dateTimePicker_ToDateTime.CustomFormat = "MM/dd/yyyy hh:mm:ss tt"; dateTimePicker_ToDateTime.Format = DateTimePickerFormat.Custom; comboBox_capturedinstances.DataSource = _lst; comboBox_Authentication.Text = comboBox_Authentication.Items[0].ToString(); }catch (Exception ex) { MessageBox.Show(ex.Message, "SQLCDCApp Error!!!"); } }
private void button_Disablecdctable_Click(object sender, EventArgs e) { List <DataGridViewRow> rows_with_checked_column = new List <DataGridViewRow>(); SQLCDCApp scdc = new SQLCDCApp(); List <CDC> cdclist = new List <CDC>(); foreach (DataGridViewRow row in dataGridView_tables.Rows) { if (Convert.ToBoolean(row.Cells[0].Value) == true) { rows_with_checked_column.Add(row); } } if (rows_with_checked_column.Count == 0) { MessageBox.Show("Please select a table.", "SQLCDCApp Information"); return; } foreach (DataGridViewRow dgvr in rows_with_checked_column) { CDC cdcobj = new CDC(); if (dgvr.Cells[4].Value.ToString() == "True") { cdcobj.Databasename = dgvr.Cells[1].Value.ToString().Trim(); cdcobj.source_schema = dgvr.Cells[2].Value.ToString().Trim(); cdcobj.source_name = dgvr.Cells[3].Value.ToString().Trim(); //cdcobj.capture_instance = textBox_captureinstance.Text.Trim(); cdclist.Add(cdcobj); } } MessageBox.Show(scdc.fn_EnableCDCOnTable(cdclist, false).ToString(), "SQLCDCAPP Information"); fn_ListTables(); }
/// <summary> /// Connects to sql server /// returns true when successfull /// </summary> /// <returns></returns> public List <SQLCDCApp> fn_GetDatabases() { List <SQLCDCApp> _listdatabases = new List <SQLCDCApp>(); SqlConnection _Sqlcon = new SqlConnection(); _Sqlcon = fn_ConnecttoSQL(); try { if (_Sqlcon.State.ToString() == "Closed") { _Sqlcon.Open(); } SqlCommand SQLGetDatabases = new SqlCommand("Select name,is_cdc_enabled from sys.databases where database_id>4 and state_desc='Online' order by is_cdc_enabled desc ", _Sqlcon); SqlDataReader Datareader; Datareader = SQLGetDatabases.ExecuteReader(); while (Datareader.Read()) { SQLCDCApp obj = new SQLCDCApp(); obj.DatabaseName = Datareader[0].ToString(); obj.is_cdc_enabled = Datareader[1].ToString(); //obj.state = Datareader[2].ToString(); _listdatabases.Add(obj); } return(_listdatabases); } catch (Exception ex) { throw ex; } finally{ _Sqlcon.Close(); } }
private void button_Tablename_Click(object sender, EventArgs e) { try { if (string.IsNullOrEmpty(textBox_TableName.Text)) { MessageBox.Show("Please enter a tablename", "SQLCDCApp Information!!"); } else { desttable = textBox_TableName.Text; } SQLCDCApp obj = new SQLCDCApp(); obj.fn_copytableschema(srctable, desttable, destdb, srcdb); MessageBox.Show("Table Created!!!", "SQLCDCAPP Information!!!"); this.Close(); } catch (Exception ex) { MessageBox.Show(ex.Message, "SQLCDCApp Error !!!"); } }
private void button2_Click(object sender, EventArgs e) { SQLCDCApp scdc = new SQLCDCApp(); List <DataGridViewRow> rows_with_checked_column = new List <DataGridViewRow>(); foreach (DataGridViewRow row in dataGridView_tables.Rows) { if (Convert.ToBoolean(row.Cells[0].Value) == true) { rows_with_checked_column.Add(row); } } if (rows_with_checked_column.Count == 0) { MessageBox.Show("Please select a table.", "SQLCDCApp Information"); return; } if (rows_with_checked_column.Count > 1) { MessageBox.Show("Please select single table only.", "SQLCDCApp Information"); return; } List <CDC> cdclist = new List <CDC>(); cdclist = fn_getselectedtables(); List <string> lst = new List <string>(); lst = scdc.fn_CaptureInstance(cdclist); Form nf = new CDCData(lst); nf.ShowDialog(); }
private string fn_ConfigureCDC(bool Enable) { // get checked rows List <DataGridViewRow> rows_with_checked_column = new List <DataGridViewRow>(); List <string> lstdbcdc = new List <string>(); SQLCDCApp obj = new SQLCDCApp(); string msgoutput = "", returnmsg = ""; try { foreach (DataGridViewRow row in dataGridView_Databases.Rows) { if (Convert.ToBoolean(row.Cells[0].Value) == true) { rows_with_checked_column.Add(row); } } if (rows_with_checked_column.Count == 0) { returnmsg = "Please select a database."; return(returnmsg); } bool output = false; if (Enable) { foreach (DataGridViewRow dgvr in rows_with_checked_column) { if (dgvr.Cells[2].Value.ToString() == "False") { lstdbcdc.Add(dgvr.Cells[1].Value.ToString()); } } msgoutput = MessageBox.Show("Enable CDC on selected databases?", "SQLCDCAPP Info", MessageBoxButtons.YesNo).ToString(); if (msgoutput == "Yes") { output = obj.fn_ConfigureCDC(lstdbcdc, true); returnmsg = "CDC Enabled on selected Databases!!!"; } } else { foreach (DataGridViewRow dgvr in rows_with_checked_column) { if (dgvr.Cells[2].Value.ToString() == "True") { lstdbcdc.Add(dgvr.Cells[1].Value.ToString()); } } msgoutput = MessageBox.Show("Disable CDC on selected databases?", "SQLCDCAPP Info", MessageBoxButtons.YesNo).ToString(); if (msgoutput == "Yes") { output = obj.fn_ConfigureCDC(lstdbcdc, false); returnmsg = "CDC Disabled on selected Databases!!!"; } } if (output == true) { fn_ListDatabases(); } return(returnmsg); } catch (Exception ex) { throw ex; } }
/// <summary> /// schedule increment data load /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button_scheduleIDL_Click(object sender, EventArgs e) { Cursor.Current = Cursors.WaitCursor; Tables tbl = new Tables(Databasename, tablename.Substring(tablename.IndexOf("_") + 1)); if (!tbl._primary_key_enabled) { MessageBox.Show("Increment load can be done on tables with primary key constraint.", "SQLCDCApp Info!!!"); return; } if (string.IsNullOrEmpty(textBox_Server.Text)) { MessageBox.Show("Destination server can't be empty", "SQLCDCApp Info!!!"); textBox_Server.Focus(); return; } if (string.IsNullOrEmpty(comboBox_Database.Text)) { MessageBox.Show("Destination Database can't be empty", "SQLCDCApp Info!!!"); comboBox_Database.Focus(); return; } if (string.IsNullOrEmpty(comboBox_Tables.Text)) { MessageBox.Show("Destination table can't be empty", "SQLCDCApp Info!!!"); comboBox_Tables.Focus(); return; } try { // make an entry in meta tables // obj.fn_Updatemetatables(Settings1.Default.Server.ToString(), textBox_Server.Text, Databasename, comboBox_Database.Text, tablename, desttable, comboBox_capturedinstances.Text, textBox_User.Text, Crypto.Crypto.Encrypt(textBox_password.Text, true)); string _sqljobname = "SQLCDCApp_IDL_" + Databasename + "_" + tablename + "_" + comboBox_capturedinstances.Text; SQLAgentJob _sajob = new SQLAgentJob(Settings1.Default.Server, Settings1.Default.User, Crypto.Crypto.Encrypt(Settings1.Default.Password, true), Settings1.Default.AuthenticationType); _sajob.SQLJob(_sqljobname); string userid = Settings1.Default.User, password = Crypto.Crypto.Encrypt(Settings1.Default.Password, true); Guid _sqljobid = _sajob.SQLJobID; SQLCDCApp _obj = new SQLCDCApp(); _obj.fn_Updatemetatables(Settings1.Default.Server.ToString(), textBox_Server.Text, Databasename, comboBox_Database.Text, tablename, desttable, comboBox_capturedinstances.Text, textBox_User.Text, Crypto.Crypto.Encrypt(textBox_password.Text, true)); _obj.fn_setsqljobid(_sqljobid, comboBox_capturedinstances.Text, Databasename, tablename, desttable, comboBox_Database.Text, textBox_Server.Text); if (string.IsNullOrEmpty(Settings1.Default.User) || Settings1.Default.AuthenticationType == "Windows") { userid = "$$@#$$"; } string _cmd = Directory.GetCurrentDirectory() + "//" + "SQLCDCIDLoader.exe " + _sqljobid + " " + Settings1.Default.Server + " " + userid + " " + password; _sajob.SQLJobStep(_cmd, _sqljobname); MessageBox.Show(_sqljobname + " created on " + Settings1.Default.Server + ". Schedule accordingly from SQL Server Agent.", "SQLCDCApp"); Cursor.Current = Cursors.Arrow; }catch (Exception ex) { MessageBox.Show(ex.Message, "SQLCDCApp Error!!!"); } // update meta tables with sql job id. }
private void button_Connect_Click(object sender, EventArgs e) { SQLCDCApp obj = new SQLCDCApp(); List <SQLCDCApp> _listdatabases = new List <SQLCDCApp>(); comboBox_Database.Items.Clear(); try { if (string.IsNullOrEmpty(textBox_Server.Text)) { MessageBox.Show("Server name can't be blank", "SQLCDCAPP Error !!!"); return; } if (comboBox_Authentication.SelectedIndex == 1) { if (string.IsNullOrEmpty(textBox_User.Text)) { MessageBox.Show("User can't be blank", "SQLCDCAPP Error !!!"); textBox_User.Focus(); return; } if (string.IsNullOrEmpty(textBox_password.Text)) { MessageBox.Show("Password can't be blank", "SQLCDCAPP Error !!!"); textBox_password.Focus(); return; } } // save dest connection details Settings1.Default.Destserver = textBox_Server.Text; Settings1.Default.Destuser = textBox_User.Text; Settings1.Default.Destpassword = Crypto.Crypto.Encrypt(textBox_password.Text, true); Settings1.Default.Destauthtype = comboBox_Authentication.Text; DataTable dtdblist = obj.fn_GetDatabases("destination"); if (dtdblist.Rows.Count == 0) { MessageBox.Show("No user database is available!!!", "SQLCDCApp Info!!!"); button_InitialSync.Enabled = false; button_scheduleIDL.Enabled = false; return; } button_InitialSync.Enabled = true; button_scheduleIDL.Enabled = true; foreach (DataRow rw in dtdblist.Rows) { comboBox_Database.Items.Add(rw["name"]); } /* foreach(SQLCDCApp item in _listdatabases) * { * comboBox_Database.Items.Add(item.DatabaseName); * } */ comboBox_Database.Text = comboBox_Database.Items[0].ToString(); }catch (Exception ex) { MessageBox.Show(ex.Message, "SQLCDCApp Error!!!"); } }