private void RebuildSQL()
{
// update other options based on selection
AutoFixOptions option = (AutoFixOptions)cmbOptions.SelectedItem;
switch (option)
{
case AutoFixOptions.AttemptIDRemote:
case AutoFixOptions.AttemptIDLocal:
if (lstSelected.Items.Count < 1)
{
txtUpdateSQL.Text = "";
txtSelectSQL.Text = "";
}
else
{
txtSelectSQL.Text = "SELECT " + cmbTargetColumn.SelectedItem;
txtUpdateSQL.Text = "UPDATE " + cmbTables.SelectedItem + " SET " + cmbTargetColumn.SelectedItem + " = @PARAM0 WHERE ";
string selectSQL = "SELECT ";
bool first = true;
for (int i = 0; i < lstSelected.Items.Count; i++)
{
if (first)
{
selectSQL += lstSelected.Items[i];
first = false;
}
else
{
selectSQL += ", ";
selectSQL += lstSelected.Items[i];
txtUpdateSQL.Text += " AND ";
}
txtSelectSQL.Text += ", " + lstSelected.Items[i];
int paramCol = i + 1;
txtUpdateSQL.Text += lstSelected.Items[i] + " = @PARAM" + paramCol.ToString();
}
selectSQL += " FROM " + cmbTables.SelectedItem + " WHERE " + lstSelected.Items[0] + " = @PARAM0);";
txtSelectSQL.Text += " FROM " + cmbTables.SelectedItem + " WHERE " + lstSelected.Items[0] + " IN (" + selectSQL;
if (!String.IsNullOrEmpty(txtUpdateSQL.Text))
{
txtUpdateSQL.Text += ";";
}
}
break;
case AutoFixOptions.AppendExtraChar:
txtUpdateSQL.Text = String.Format("UPDATE {0} SET {1} = {1} || '~rf' WHERE {1} = @PARAM0",
cmbTables.SelectedItem, cmbTargetColumn.SelectedItem);
txtSelectSQL.Text = txtUpdateSQL.Text;
break;
}
}
public AutoCorrectRule(string tableName, string keyName,
string targetTable, string targetColumn, string replicateName,
string sqlRuleLocal, string sqlRuleRemote, string dependencies, AutoFixOptions options)
{
TableName = tableName;
KeyName = keyName;
TargetTable = targetTable;
TargetColumn = targetColumn;
ReplicateName = replicateName;
SQLRuleLocal = sqlRuleLocal;
SQLRuleRemote = sqlRuleRemote;
Dependencies = dependencies;
Options = options;
}
private void btnSave_Click(object sender, EventArgs e)
{
if (cmbTables.SelectedIndex == -1)
{
ShowError("Error", "Please select a table.");
cmbTables.DroppedDown = true;
return;
}
if (cmbKeys.SelectedIndex == -1)
{
ShowError("Error", "Please select a key or error message.");
cmbKeys.DroppedDown = true;
return;
}
if (cmbOptions.SelectedIndex == -1)
{
ShowError("Error", "Please select an option.");
cmbOptions.DroppedDown = true;
return;
}
AutoFixOptions option = (AutoFixOptions)cmbOptions.SelectedItem;
if (_rule == null)
{
_rule = new AutoCorrectRule();
_rule.SQLRuleRemote = String.Empty;
_rule.SQLRuleLocal = String.Empty;
_rule.Dependencies = String.Empty;
_rule.ReplicateName = String.Empty;
}
_rule.TableName = (string)cmbTables.SelectedItem;
_rule.KeyName = (string)cmbKeys.SelectedItem;
_rule.TargetTable = _rule.TableName;
_rule.Options = option;
API api = new API();
try
{
if (option == AutoFixOptions.IgnoreRecord)
{
api.AutoCorrectRuleAdd(_connection, _rule);
this.DialogResult = System.Windows.Forms.DialogResult.OK;
return;
}
if (cmbTargetColumn.SelectedIndex == -1)
{
ShowError("Error", "Please select the target column.");
cmbTargetColumn.DroppedDown = true;
return;
}
_rule.TargetColumn = (string)cmbTargetColumn.SelectedItem;
_rule.SQLRuleLocal = txtSelectSQL.Text;
_rule.SQLRuleRemote = txtUpdateSQL.Text;
if (String.IsNullOrEmpty(txtUpdateSQL.Text))
{
ShowError("Error", "Please enter the update SQL for this rule.");
return;
}
if (option == AutoFixOptions.AppendExtraChar)
{
api.AutoCorrectRuleAdd(_connection, _rule);
this.DialogResult = System.Windows.Forms.DialogResult.OK;
return;
}
if (lstSelected.Items.Count == 0)
{
ShowError("Error", "Please select the columns which will be validated and updated.");
return;
}
if (String.IsNullOrEmpty(txtSelectSQL.Text))
{
ShowError("Error", "Please enter the select SQL for this rule.");
return;
}
// fix id requires the columns to be set
_rule.ReplicateName = String.Empty;
foreach (string s in lstSelected.Items)
{
if (!String.IsNullOrEmpty(_rule.ReplicateName))
{
_rule.ReplicateName += ",";
}
_rule.ReplicateName += s;
}
api.AutoCorrectRuleAdd(_connection, _rule);
this.DialogResult = System.Windows.Forms.DialogResult.OK;
return;
}
finally
{
api = null;
}
}
private void cmbOptions_SelectedIndexChanged(object sender, EventArgs e)
{
// update other options based on selection
AutoFixOptions option = (AutoFixOptions)cmbOptions.SelectedItem;
switch (option)
{
case AutoFixOptions.AttemptIDRemote:
case AutoFixOptions.AttemptIDLocal:
lblSelectSQL.Visible = true;
lblUpdateSQL.Visible = true;
txtSelectSQL.Visible = true;
txtUpdateSQL.Visible = true;
lblAvailableColumns.Visible = true;
lblSelectedColumns.Visible = true;
lstAvailable.Visible = true;
lstSelected.Visible = true;
btnAdd.Visible = true;
btnRemove.Visible = true;
lblTargetColumn.Visible = true;
cmbTargetColumn.Visible = true;
this.Height = 500;
break;
case AutoFixOptions.AppendExtraChar:
lblAvailableColumns.Visible = false;
lblSelectedColumns.Visible = false;
lstAvailable.Visible = false;
lstSelected.Visible = false;
btnAdd.Visible = false;
btnRemove.Visible = false;
lblSelectSQL.Visible = false;
lblUpdateSQL.Visible = true;
txtSelectSQL.Visible = false;
txtUpdateSQL.Visible = true;
lblTargetColumn.Visible = true;
cmbTargetColumn.Visible = true;
this.Height = 270;
break;
case AutoFixOptions.IgnoreRecord:
default:
lblAvailableColumns.Visible = false;
lblSelectedColumns.Visible = false;
lstAvailable.Visible = false;
lstSelected.Visible = false;
btnAdd.Visible = false;
btnRemove.Visible = false;
lblSelectSQL.Visible = false;
lblUpdateSQL.Visible = false;
txtSelectSQL.Visible = false;
txtUpdateSQL.Visible = false;
lblTargetColumn.Visible = false;
cmbTargetColumn.Visible = false;
this.Height = 200;
break;
}
}