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!!!");
}
}
