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