private void openDbManager(object sender, EventArgs e)
{
if (databases.SelectedItem == null)
{
return;
}
submit.Text = "Connecting...";
submit.Enabled = false;
this.Refresh();
sqlConnection conection = new sqlConnection(_serverName, databases.SelectedItem.ToString(), _userName, _password);
if (conection.establishConnection())
{
this.Hide();
dbManager manager = new dbManager(conection);
manager.Show();
manager.FormClosing += (object sender, FormClosingEventArgs args) => { this.Show(); };
}
submit.Text = "Connect";
submit.Enabled = true;
}
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 : "");
}
public static string ShowUpdateDialog(string text, string caption, string currentTable, sqlConnection _cnn)
{
TableLayoutPanel layout = new TableLayoutPanel();
ComboBox values = new ComboBox();
ComboBox columns = new ComboBox();
ComboBox valueCondition = new ComboBox();
ComboBox columnCondition = new ComboBox();
string result = "";
layout.Size = new System.Drawing.Size(600, 60);
layout.RowCount = 2;
layout.ColumnCount = 4;
layout.Location = new System.Drawing.Point(0, 30);
columns.Items.AddRange(_cnn.getColumnNames(currentTable).ToArray());
columnCondition.Items.AddRange(_cnn.getColumnNames(currentTable).ToArray());
columns.TextChanged += (object sender, EventArgs args) => {
values.Items.Clear();
values.Items.AddRange(_cnn.getRows(currentTable, columns.Text).ToArray());
};
columnCondition.TextChanged += (object sender, EventArgs args) => {
valueCondition.Items.Clear();
valueCondition.Items.AddRange(_cnn.getRows(currentTable, columnCondition.Text).ToArray());
};
layout.Controls.Add(new Label()
{
Text = "Set: ", TextAlign = ContentAlignment.MiddleCenter, Width = 150
});
layout.Controls.Add(columns);
layout.Controls.Add(new Label()
{
Text = " to: ", TextAlign = ContentAlignment.MiddleCenter, Width = 150
});
layout.Controls.Add(values);
layout.Controls.Add(new Label()
{
Text = "Where: ", TextAlign = ContentAlignment.MiddleCenter, Width = 150
});
layout.Controls.Add(columnCondition);
layout.Controls.Add(new Label()
{
Text = " is like: ", TextAlign = ContentAlignment.MiddleCenter, Width = 150
});
layout.Controls.Add(valueCondition);
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("UPDATE {0} SET {1}='{2}' WHERE {3}='{4}';", currentTable, columns.Text, values.Text, columnCondition.Text, valueCondition.Text);
prompt.Close();
};
prompt.Controls.Add(confirmation);
prompt.AcceptButton = confirmation;
return(prompt.ShowDialog() == DialogResult.OK ? result : "");
}
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 : "");
}
public dbManager(sqlConnection connection)
{
InitializeComponent();
this.Text = connection.serverName + "/" + connection.databaseName;
_connection = connection;
database = new ToolStripMenuItem();
database.Text = "Database";
addTable = new ToolStripMenuItem();
addTable.Text = "Add table";
addTable.Click += (sender, args) => {
string newTableName = DialogPrompt.ShowStringDialog("New table name:", "New table");
if (newTableName.Length == 0)
{
return;
}
_connection.sqlExecute("create table \"" + newTableName + " \"(" + newTableName + "ID INT NOT NULL IDENTITY(1,1), PRIMARY KEY (" + newTableName + "ID));");
refresh(tables.SelectedIndex + 1);
};
removeTable = new ToolStripMenuItem();
removeTable.Click += (object sender, EventArgs args) => {
_connection.sqlExecute("drop table \"" + tables.SelectedTab.Text + "\";");
refresh(-1);
};
removeTable.Text = "Drop current table";
renameTable = new ToolStripMenuItem();
renameTable.Text = "Rename current table";
renameTable.Click += (sender, args) => {
string newTableName = DialogPrompt.ShowStringDialog("New table name:", "Rename table");
if (newTableName.Length == 0)
{
return;
}
_connection.sqlExecute("EXEC sp_rename '" + tables.SelectedTab.Text + "', '" + newTableName + "';");
refresh(tables.SelectedIndex);
};
database.DropDownItems.AddRange(new ToolStripItem[] { addTable, removeTable, renameTable });
table = new ToolStripMenuItem();
table.Text = "Table";
addColumn = new ToolStripMenuItem();
addColumn.Text = "Add column";
addColumn.Click += (sender, args) => {
string query = DialogPrompt.ShowCreateColumnDialog("Specify new column:", "New column");
if (query.Length == 0)
{
return;
}
_connection.sqlExecute("alter table \"" + tables.SelectedTab.Text + "\" add " + query);
refresh(tables.SelectedIndex);
};
removeColumn = new ToolStripMenuItem();
removeColumn.Text = "Drop column";
removeColumn.Click += (sender, args) => { string column = DialogPrompt.ShowRemoveColumnDialog("Drop column", "Drop column", _connection.getColumnNames(tables.SelectedTab.Text));
if (column.Length == 0)
{
return;
}
_connection.sqlExecute("alter table \"" + tables.SelectedTab.Text + "\" drop column " + column);
refresh(tables.SelectedIndex); };
renameColumn = new ToolStripMenuItem();
renameColumn.Text = "Rename column";
renameColumn.Click += (object sender, EventArgs args) => {
string query = DialogPrompt.ShowRenameColumnDialog("Rename column", "Rename column", _connection.getColumnNames(tables.SelectedTab.Text));
if (query.Length == 0)
{
return;
}
_connection.sqlExecute("sp_rename '" + tables.SelectedTab.Text + query);
refresh(tables.SelectedIndex);
};
addForeignKey = new ToolStripMenuItem();
addForeignKey.Text = "Add foreign key";
addForeignKey.Click += (sender, args) => {
string query = DialogPrompt.ShowAddForeignKeyDialog("Add foreign key", "Add foreign key", tables.SelectedTab.Text, _connection);
if (query.Length == 0)
{
return;
}
_connection.sqlExecute("alter table " + tables.SelectedTab.Text + query);
refresh(tables.SelectedIndex);
};
dropKey = new ToolStripMenuItem();
dropKey.Text = "Drop key";
dropKey.Click += (sender, args) => {
string constraint = DialogPrompt.ShowRemoveKeyDialog("Drop key", "Drop key", _connection.getKeys(tables.SelectedTab.Text));
if (constraint.Length == 0)
{
return;
}
_connection.sqlExecute("alter table \"" + tables.SelectedTab.Text + "\" drop constraint " + constraint);
refresh(tables.SelectedIndex);
};
table.DropDownItems.AddRange(new ToolStripItem[] { addColumn, removeColumn, renameColumn, addForeignKey, dropKey });
records = new ToolStripMenuItem();
records.Text = "Records";
insertRecord = new ToolStripMenuItem();
insertRecord.Text = "Insert into";
insertRecord.Click += (sender, args) => {
string query = DialogPrompt.ShowInsertIntoDialog("Insert into " + tables.SelectedTab.Text, "Insert into " + tables.SelectedTab.Text, _connection.getColumnNames(tables.SelectedTab.Text));
if (query == "")
{
return;
}
_connection.sqlExecute("insert into " + tables.SelectedTab.Text + " values (" + query + ");");
refresh(tables.SelectedIndex);
};
dropRecord = new ToolStripMenuItem();
dropRecord.Text = "Drop records";
dropRecord.Click += (sender, args) => {
string query = DialogPrompt.ShowDropRecordDialog("Drop records", "Drop records", tables.SelectedTab.Text, _connection);
if (query == "")
{
return;
}
_connection.sqlExecute(query);
refresh(tables.SelectedIndex);
};
editRecord = new ToolStripMenuItem();
editRecord.Text = "Update record";
editRecord.Click += (sender, args) => {
string query = DialogPrompt.ShowUpdateDialog("Update records", "Update records", tables.SelectedTab.Text, _connection);
if (query == "")
{
return;
}
_connection.sqlExecute(query);
refresh(tables.SelectedIndex);
};
records.DropDownItems.AddRange(new ToolStripItem[] { insertRecord, editRecord, dropRecord });
executeQuery = new ToolStripMenuItem();
executeQuery.Text = "Execute query";
executeQuery.Click += (sender, args) => {
string query = DialogPrompt.ShowStringDialog("Execute query", "Execute query:");
if (query == "")
{
return;
}
_connection.sqlExecute(query);
refresh(0);
};
joinTables = new ToolStripMenuItem();
joinTables.Text = "Join tables";
joinTables.Click += (sender, args) => {
string query = DialogPrompt.ShowJoinDialog("Join tables", "Join tables", _connection);
if (query == "")
{
return;
}
Form viewer = new Form();
DataGridView tempGrid = new DataGridView();
tempGrid.Dock = DockStyle.Fill;
tempGrid.ReadOnly = true;
try
{
tempGrid.DataSource = _connection.getQuery(query).Tables[0];
}
catch
{
MessageBox.Show("Error while loading data.");
}
viewer.Size = new Size(800, 600);
viewer.Controls.Add(tempGrid);
viewer.Show();
};
menu.Items.Add(database);
menu.Items.Add(table);
menu.Items.Add(records);
menu.Items.Add(joinTables);
menu.Items.Add(executeQuery);
tables.Dock = DockStyle.Fill;
this.Controls.Add(tables);
this.Controls.Add(menu);
refresh(-1);
}
