// Button GoButton)
public TransactionScript(Source sourceSource, Target targetTarget, Configuration config, DoneDelegate doneHandler)
{
this.sourceSource = sourceSource;
this.targetTarget = targetTarget;
this.config = config;
this.doneHandler = doneHandler;
//this.goButton = GoButton;
}
private void configAddButton_Click(object sender, EventArgs e)
{
Source source = (Source)(sourceList.SelectedItem);
if (source == null)
{
MessageBox.Show("Please select a source to which you want to add a configuration.");
}
else
{
bool AlreadyClosed = false;
ConfigurationForm form = new ConfigurationForm();
form.meta = meta;
form.dbDeployForm = this;
if (form.ShowDialog() == DialogResult.OK)
{
Configuration config = new Configuration();
config.title = form.TitleText.Text;
config.transferMaxRows = long.Parse(form.maxRowsText.Text);
//config.targetId = target.id;
deployTypeSet(form, config);
if (form.SchemaSelectRadioInclude.Checked)
config.tableSelectSchema = 1;
else
config.tableSelectSchema = 2;
if (form.DataSelectRadioInclude.Checked)
config.tableSelectData = 1;
else
config.tableSelectData = 2;
if (form.DataTransferCheckStatic.Checked)
config.transferStaticData = "Y";
else
config.transferStaticData = "N";
if (form.DataTransferCheckTypes.Checked)
config.transferTypesData = "Y";
else
config.transferTypesData = "N";
if (form.ddlLoggingRadioNone.Checked)
config.ddlLogging = Configuration.DdlLogging.none;
else if (form.ddlLoggingRadioChanges.Checked)
config.ddlLogging = Configuration.DdlLogging.changes;
else
config.ddlLogging = Configuration.DdlLogging.all;
// Could get this from the DB but iiSDLC says it should ALWAYS be done
if (form.backupPerformCheck.Checked)
config.backupPerform = "Y";
else
config.backupPerform = "N";
config.backupLocation = form.backupLocationText.Text;
if (form.excludeSchemaTempCheck.Checked)
config.tempExcludeSchema = "Y";
else
config.tempExcludeSchema = "N";
if (form.excludeDataTempCheck.Checked)
config.tempExcludeData = "Y";
else
config.tempExcludeData = "N";
if (form.SyncFKCheck.Checked)
config.FKCheckSync = "Y";
else
config.FKCheckSync = "N";
OdbcCommand command = new OdbcCommand();
command.Connection = meta;
command.CommandType = CommandType.Text;
command.CommandText = "insert into meta_db_deploy_configurations( " +
" meta_db_deploy_type, " +
" meta_db_deploy_tbl_sel_schema, " +
" meta_db_deploy_tbl_sel_data, " +
" meta_dbdc_title, " +
" meta_dbdc_transfer_static_data, " +
" meta_dbdc_transfer_types_data, " +
" meta_dbdc_transfer_max_rows, " +
" meta_db_deploy_log_type, " +
" meta_dbdc_temp_exclude_schema, " +
" meta_dbdc_temp_exclude_data, " +
" meta_dbdc_backup_perform, " +
" meta_dbdc_backup_location, " +
" meta_dbdc_exclude_fk " +
") " +
" values (" +
config.type + ", " +
config.tableSelectSchema + ", " +
config.tableSelectData + ", " +
" ?, " +
" '" + config.transferStaticData + "', " +
" '" + config.transferTypesData + "', " +
config.transferMaxRows + ", " +
((long)config.ddlLogging) + ", " +
" '" + config.tempExcludeSchema + "', " +
" '" + config.tempExcludeData + "', " +
" '" + config.backupPerform +"', " +
" ? " + ", " +
" '" + config.FKCheckSync + "'" +
")";
command.Parameters.Add(new OdbcParameter("1", config.title));
command.Parameters.Add(new OdbcParameter("2", config.backupLocation));
try
{
meta.Open();
try
{
if (command.ExecuteNonQuery() == 1)
{
log.log(Logger.LogLevel.info, "Configuration added successfully.");
command.CommandText = "select max(meta_db_deploy_configuration) from meta_db_deploy_configurations";
config.id = (long)(command.ExecuteScalar());
meta.Close();
AlreadyClosed = true;
configurationList.Items.Add(config);
configurationList.SelectedItem = config;
TableSelectUpdate(config, form.SchemaTableList, form.DataTableList, form.FilterTableList);
}
else
{
log.log(Logger.LogLevel.error, "Configuration was not added.");
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to add configuration...");
log.log(Logger.LogLevel.error, ex.Message);
}
finally
{
if (!AlreadyClosed)
{
meta.Close();
}
}
}
catch (Exception ex)
{
log.log(Logger.LogLevel.error, "Exception occurred while trying to add configuration...");
log.log(Logger.LogLevel.error, ex.Message);
}
}
}
}
private void deployTypeSet(ConfigurationForm form, Configuration config)
{
if (form.DeployTypeRadioDropCreate.Checked)
{
config.type = 1;
}
else
{
if (form.DeployTypeRadioAlter.Checked)
{
config.type = 2;
}
else if (form.DeployTypeRadioTransfer.Checked)
{
config.type = 3;
}
else
{
config.type = 4;
}
}
}
private void TablesRead(Configuration config)
{
log.phaseUpdate("Loading schema tables from source database...");
log.statusUpdate("Openning connection...");
meta.Open();
log.statusUpdate("Connection open, reading schema tables...");
OdbcCommand command = new OdbcCommand();
command.Connection = meta;
command.CommandType = CommandType.Text;
command.CommandText = "select * from meta_db_deploy_schema_tables where meta_db_deploy_configuration = " + config.id + ";";
config.schemaTables.Clear();
OdbcDataReader reader = command.ExecuteReader();
while (reader.Read())
{
config.schemaTables.Add((string)reader["meta_dbdst_name"], new TableSelect(reader["meta_dbdst_active"].ToString().ToUpper() == "Y",(string)reader["meta_dbdst_name"]));
}
reader.Close();
log.statusUpdate(" schema tables read, readin data tables...");
command.CommandText = "select * from meta_db_deploy_data_tables where meta_db_deploy_configuration = " + config.id + ";";
config.dataTables.Clear();
reader = command.ExecuteReader();
while (reader.Read())
{
config.dataTables.Add((string)reader["meta_dbddt_name"], new TableSelect(reader["meta_dbddt_active"].ToString().ToUpper() == "Y", (string)reader["meta_dbddt_name"]));
}
reader.Close();
log.statusUpdate(" reader tables read, reading filter tables...");
command.CommandText = "select * from meta_db_deploy_filter_tables where meta_db_deploy_configuration = " + config.id + ";";
config.filterTables.Clear();
reader = command.ExecuteReader();
while (reader.Read())
{
config.filterTables.Add((string)reader["meta_dbdft_name"], new TableFilter(reader["meta_dbdft_active"].ToString().ToUpper() == "Y", (string)reader["meta_dbdft_name"], (string)reader["meta_dbdft_filter_clause"]));
}
reader.Close();
log.statusUpdate(" filter tables read, closing database...");
meta.Close();
log.statusUpdate("Database closed.");
}
private void TableSelectUpdateFromList(Configuration config, CheckedListBox list, string tableName, string columnPrefix)
{
string keyName = tableName.Substring(0, tableName.Length - 1);
OdbcCommand selectCommand = new OdbcCommand();
selectCommand.Connection = meta;
selectCommand.CommandType = CommandType.Text;
OdbcCommand updateCommand = new OdbcCommand();
updateCommand.Connection = meta;
updateCommand.CommandType = CommandType.Text;
updateCommand.CommandText = "update " + tableName +
" set " + columnPrefix + "_active = ?" +
" where " + keyName + " = ? ";
OdbcParameter updateActive = new OdbcParameter("1", OdbcType.Char);
OdbcParameter updateKey = new OdbcParameter("2", OdbcType.BigInt);
updateCommand.Parameters.Add(updateActive);
updateCommand.Parameters.Add(updateKey);
OdbcCommand insertCommand = new OdbcCommand();
insertCommand.Connection = meta;
insertCommand.CommandType = CommandType.Text;
insertCommand.CommandText = "insert into " + tableName +
" (meta_db_deploy_configuration, " + columnPrefix + "_name, " + columnPrefix + "_active)" +
" values(" + config.id + ",?,?)";
OdbcParameter insertName = new OdbcParameter("1", OdbcType.VarChar);
OdbcParameter insertActive = new OdbcParameter("2", OdbcType.Char);
insertCommand.Parameters.Add(insertName);
insertCommand.Parameters.Add(insertActive);
/*
OdbcCommand getIdCommand = new OdbcCommand();
getIdCommand.Connection = meta;
getIdCommand.CommandType = CommandType.Text;
getIdCommand.CommandText = "select max( " + keyName + ") from " + tableName +
" where meta_db_deploy_configuration = " + config.id;
*/
OdbcCommand deleteCommand = new OdbcCommand();
deleteCommand.Connection = meta;
deleteCommand.CommandType = CommandType.Text;
deleteCommand.CommandText = "delete from " + tableName +
" where " + keyName + " = ? ";
OdbcParameter deleteKey = new OdbcParameter("1", OdbcType.BigInt);
deleteCommand.Parameters.Add(deleteKey);
OdbcDataReader reader;
meta.Open();
/*
long id;
*/
char active;
foreach (TableSelect select in list.Items)
{
if (list.CheckedItems.Contains(select))
active = 'Y';
else
active = 'N';
selectCommand.CommandText = "select * from " + tableName +
" where meta_db_deploy_configuration = " + config.id +
" and " + columnPrefix + "_name = '" + select.name + "'" +
";";
reader = selectCommand.ExecuteReader();
if( reader.Read() )
{
// item was found ... update it
updateKey.Value = (long)reader[keyName];
updateActive.Value = active;
//TODO: 0. check return value here (also put in a try block)
updateCommand.ExecuteNonQuery();
}
else
{
// item was not found ... insert it
insertName.Value = select.name;
insertActive.Value = active;
//TODO: 0. check return value here (also put in a try block)
insertCommand.ExecuteNonQuery();
/*
//TODO: 0. put in try block
id = ((long)getIdCommand.ExecuteScalar());
*/
}
reader.Close();
}
selectCommand.CommandText = "select * from " + tableName + " where meta_db_deploy_configuration = " + config.id + ";";
reader = selectCommand.ExecuteReader();
bool found;
string name;
while (reader.Read())
{
name = (string)reader[columnPrefix + "_name"];
found = false;
foreach (TableSelect select in list.Items)
{
if (select.name == name)
{
found = true;
break;
}
}
if (!found)
{
// delete item from database
deleteKey.Value = (long)reader[keyName];
//TODO: 0. check return value here (also put in a try block)
deleteCommand.ExecuteNonQuery();
}
}
reader.Close();
meta.Close();
}
private void TableSelectUpdate(Configuration config, CheckedListBox schemaList, CheckedListBox dataList, CheckedListBox filterList)
{
TableSelectUpdateFromList(config, schemaList, "meta_db_deploy_schema_tables", "meta_dbdst");
TableSelectUpdateFromList(config, dataList, "meta_db_deploy_data_tables", "meta_dbddt");
TableSelectUpdateFilterFromList(config, filterList, "meta_db_deploy_filter_tables", "meta_dbdft");
TablesRead(config);
}
