public static string ShowAddForeignKeyDialog(string text, string caption, string currentTable, sqlConnection _cnn) { TableLayoutPanel layout = new TableLayoutPanel(); ComboBox tables = 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); tables.Items.AddRange(_cnn.getTableNames().ToArray()); tables.TextChanged += (object sender, EventArgs args) => { columns.Items.Clear(); columns.Items.AddRange(_cnn.getColumnNames(tables.Text).ToArray()); }; layout.Controls.Add(new Label() { Text = "Add reference to: ", TextAlign = ContentAlignment.MiddleCenter, Width = 150 }); layout.Controls.Add(tables); layout.Controls.Add(new Label() { Text = "by field: ", TextAlign = ContentAlignment.MiddleCenter, Width = 150 }); layout.Controls.Add(columns); 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) => { _cnn.sqlExecute("alter table \"" + currentTable + "\" add \"" + columns.Text + "\" INT ;"); result = string.Format(" add constraint FK_{0}_{2} foreign key ({0}) references {1}({0});", columns.Text, tables.Text, currentTable); prompt.Close(); }; prompt.Controls.Add(confirmation); prompt.AcceptButton = confirmation; return(prompt.ShowDialog() == DialogResult.OK ? result : ""); }
void refresh(int tabIndex) { this.SuspendLayout(); tables.Controls.Clear(); foreach (string tableName in _connection.getTableNames()) { TabPage tempTabPage = new TabPage(); DataGridView tempDataGridView = new DataGridView(); tempTabPage.Text = tableName; tempTabPage.Name = tableName; tempDataGridView.Dock = DockStyle.Fill; tempDataGridView.DataSource = _connection.getTable(tableName).Tables[0]; tempDataGridView.ReadOnly = true; tempTabPage.Controls.Add(tempDataGridView); tables.Controls.Add(tempTabPage); } try { if (!(tabIndex < 0)) { tables.SelectTab(tabIndex); } } catch { tables.SelectTab(0); } if (tables.Controls.Count == 0) { table.Enabled = false; renameTable.Enabled = false; removeTable.Enabled = false; records.Enabled = false; executeQuery.Enabled = false; joinTables.Enabled = false; } else { table.Enabled = true; renameTable.Enabled = true; removeTable.Enabled = true; records.Enabled = true; executeQuery.Enabled = true; joinTables.Enabled = true; } this.ResumeLayout(); }
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 : ""); }