public static string ShowDropRecordDialog(string text, string caption, string currentTable, sqlConnection _cnn)
{
TableLayoutPanel layout = new TableLayoutPanel();
ComboBox values = new ComboBox();
ComboBox columns = new ComboBox();
string result = "";
layout.Size = new System.Drawing.Size(600, 30);
layout.RowCount = 1;
layout.ColumnCount = 4;
layout.Location = new System.Drawing.Point(0, 30);
columns.Items.AddRange(_cnn.getColumnNames(currentTable).ToArray());
columns.TextChanged += (object sender, EventArgs args) => {
values.Items.Clear();
values.Items.AddRange(_cnn.getRows(currentTable, columns.Text).ToArray());
};
layout.Controls.Add(new Label()
{
Text = "Drop where: ", TextAlign = ContentAlignment.MiddleCenter, Width = 150
});
layout.Controls.Add(columns);
layout.Controls.Add(new Label()
{
Text = " is like: ", TextAlign = ContentAlignment.MiddleCenter, Width = 150
});
layout.Controls.Add(values);
Form prompt = new Form()
{
Width = 600,
Height = 170,
FormBorderStyle = FormBorderStyle.FixedDialog,
Text = caption,
StartPosition = FormStartPosition.CenterScreen
};
prompt.Controls.Add(new Label()
{
Text = text, Width = 600, Location = new System.Drawing.Point(0, 0)
});
prompt.Controls.Add(layout);
Button confirmation = new Button()
{
Text = "Ok", Left = 350, Width = 100, Top = 90, DialogResult = DialogResult.OK
};
confirmation.Click += (sender, e) => {
result = string.Format("DELETE FROM {0} WHERE {1} LIKE '{2}';", currentTable, columns.Text, values.Text);
prompt.Close();
};
prompt.Controls.Add(confirmation);
prompt.AcceptButton = confirmation;
return(prompt.ShowDialog() == DialogResult.OK ? result : "");
}
public static string ShowJoinDialog(string text, string caption, sqlConnection _cnn)
{
List <string> columns = new List <string>();
TableLayoutPanel layout = new TableLayoutPanel();
TableLayoutPanel settingsLayout = new TableLayoutPanel();
ComboBox table1 = new ComboBox();
ComboBox table2 = new ComboBox();
ComboBox join1 = new ComboBox();
ComboBox join2 = new ComboBox();
ComboBox condition = new ComboBox();
ComboBox value = new ComboBox();
CheckedListBox table1Cols = new CheckedListBox();
CheckedListBox table2Cols = new CheckedListBox();
string result = "";
settingsLayout.Size = new Size(520, 100);
settingsLayout.RowCount = 2;
settingsLayout.ColumnCount = 4;
settingsLayout.Location = new Point(0, 230);
layout.Size = new System.Drawing.Size(520, 200);
layout.RowCount = 4;
layout.ColumnCount = 2;
layout.Location = new System.Drawing.Point(130, 30);
table1.Items.AddRange(_cnn.getTableNames().ToArray());
table1.TextChanged += (sender, args) => {
join1.Items.Clear();
table1Cols.Items.Clear();
table1Cols.Items.AddRange(_cnn.getColumnNames(table1.Text).ToArray());
};
table2.Items.AddRange(_cnn.getTableNames().ToArray());
table2.TextChanged += (sender, args) => {
join2.Items.Clear();
table2Cols.Items.Clear();
table2Cols.Items.AddRange(_cnn.getColumnNames(table2.Text).ToArray());
};
table1Cols.MouseLeave += (sender, args) => {
join1.Items.Clear();
condition.Items.Clear();
foreach (string item in table1Cols.CheckedItems)
{
join1.Items.Add(item);
condition.Items.Add(table1.Text.Trim() + "." + item);
}
foreach (string item in table2Cols.CheckedItems)
{
condition.Items.Add(table2.Text.Trim() + "." + item);
}
};
table2Cols.MouseLeave += (sender, args) => {
join2.Items.Clear();
condition.Items.Clear();
foreach (string item in table2Cols.CheckedItems)
{
join2.Items.Add(item);
condition.Items.Add(table2.Text.Trim() + "." + item);
}
foreach (string item in table1Cols.CheckedItems)
{
condition.Items.Add(table1.Text.Trim() + "." + item);
}
};
condition.TextChanged += (sender, args) => {
string fixedColumnName;
string fixedTableName;
try
{
fixedColumnName = condition.Text.Substring(condition.Text.IndexOf('.') + 1, condition.Text.Length - condition.Text.IndexOf('.') - 1);
fixedTableName = condition.Text.Substring(0, condition.Text.IndexOf('.'));
value.Items.Clear();
value.Items.AddRange(_cnn.getRows(fixedTableName, fixedColumnName).ToArray());
}
catch
{
MessageBox.Show("Invalid value in condition column name field.");
}
};
layout.Controls.Add(new Label()
{
Text = "First table:", TextAlign = ContentAlignment.MiddleCenter
});
layout.Controls.Add(new Label()
{
Text = "Second table", TextAlign = ContentAlignment.MiddleCenter
});
layout.Controls.Add(table1);
layout.Controls.Add(table2);
layout.Controls.Add(table1Cols);
layout.Controls.Add(table2Cols);
settingsLayout.Controls.Add(new Label()
{
Text = "On: ", TextAlign = ContentAlignment.MiddleCenter
});
settingsLayout.Controls.Add(join1);
settingsLayout.Controls.Add(new Label()
{
Text = " = ", TextAlign = ContentAlignment.MiddleCenter
});
settingsLayout.Controls.Add(join2);
settingsLayout.Controls.Add(new Label()
{
Text = "Where", TextAlign = ContentAlignment.MiddleCenter
});
settingsLayout.Controls.Add(condition);
settingsLayout.Controls.Add(new Label()
{
Text = " like ", TextAlign = ContentAlignment.MiddleCenter
});
settingsLayout.Controls.Add(value);
Form prompt = new Form()
{
Width = 520,
Height = 450,
FormBorderStyle = FormBorderStyle.FixedDialog,
Text = caption,
StartPosition = FormStartPosition.CenterScreen
};
prompt.Controls.Add(new Label()
{
Text = text, Width = 600, Location = new System.Drawing.Point(0, 0)
});
prompt.Controls.Add(layout);
prompt.Controls.Add(settingsLayout);
Button confirmation = new Button()
{
Text = "Ok", Left = 350, Width = 100, Top = 350, DialogResult = DialogResult.OK
};
confirmation.Click += (sender, e) => {
foreach (string item in condition.Items)
{
result = result == "" ? item.Trim() + " AS '" + item.Trim() + "'" : result + ", " + item.Trim() + " AS '" + item.Trim() + "'";
}
result = "select " + result + string.Format(" from {0} full outer join {1} on {0}.{2}={1}.{3}", table1.Text.Trim(), table2.Text.Trim(), join1.Text.Trim(), join2.Text.Trim());
if (value.Text != "")
{
result = result + " where " + condition.Text.Trim() + " like '" + value.Text.Trim() + "';";
}
else
{
result = result + ";";
}
prompt.Close();
};
prompt.Controls.Add(confirmation);
prompt.AcceptButton = confirmation;
return(prompt.ShowDialog() == DialogResult.OK ? result : "");
}