private void FillListConnections()
{
DataBase db = new DataBase("prime_db");
db.OpenConnection();
MySqlCommand command = new MySqlCommand($"SELECT First_Atr_ID, Second_Atr_ID, Connection_ID FROM `connection`", db.GetConnection());
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
IDataRecord record = (IDataRecord)reader;
ConnectionsListBox.Items.Add($"{Convert.ToInt32(record[2])} {GetAttrName(Convert.ToInt32(record[0]))} ----> {GetAttrName(Convert.ToInt32(record[1]))}");
}
db.CloseConnection();
}
private void FillListTables()
{
comboBoxTables.Items.Clear();
DataBase db = new DataBase("prime_db");
db.OpenConnection();
MySqlCommand command = new MySqlCommand("SELECT Name FROM `table`", db.GetConnection());
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
comboBoxTables.Items.Add(reader.GetValue(0).ToString());
}
db.CloseConnection();
}
private void FillListAttrs()
{
AllAttrsListBox.Items.Clear();
DataBase db = new DataBase("prime_db");
db.OpenConnection();
MySqlCommand command = new MySqlCommand($"SELECT Attribute_Name FROM `attribute` WHERE Table_ID = {GetTableID(comboBoxTables.Text)}", db.GetConnection());
MySqlDataReader reader = command.ExecuteReader();
while (reader.Read())
{
AllAttrsListBox.Items.Add(reader.GetValue(0).ToString());
}
db.CloseConnection();
}
private List <string> GetAllTableNames()
{
List <string> res = new List <string>();
DataBase mData = new DataBase("prime_db");
mData.OpenConnection();
MySqlCommand com = new MySqlCommand("SELECT Name FROM `table`", mData.GetConnection());
MySqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
res.Add(reader.GetValue(0).ToString());
}
mData.CloseConnection();
return(res);
}
private void depTableComboBox_TextChanged(object sender, EventArgs e)
{
depAttrListBox.Items.Clear();
DataBase mData = new DataBase("prime_db");
mData.OpenConnection();
MySqlCommand com = new MySqlCommand($"SELECT Attribute_Name FROM `attribute` WHERE Table_ID = {GetTableID(depTableComboBox.Text)}", mData.GetConnection());
MySqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
depAttrListBox.Items.Add(reader.GetValue(0).ToString());
}
mData.CloseConnection();
CheckAttrsSelection();
}
//подключение к таблице
private DataTable GetTable(DataBase db, string table_name)
{
try
{
db.OpenConnection();
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataTable table = new DataTable();
string query_select_id = $"SELECT * FROM `{table_name}`";
MySqlCommand command = new MySqlCommand(query_select_id, db.GetConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
db.CloseConnection();
return(table);
}
catch
{
MessageBox.Show("Данной таблицы не существует");
return(null);
}
}
private void CreateConnectionButton_Click(object sender, EventArgs e)
{
DataBase mData = new DataBase("prime_db");
DataBase userDB = new DataBase(currentDB);
bool flag = false;
userDB.OpenConnection();
try
{
MySqlCommand com = new MySqlCommand($"ALTER TABLE `{depTableComboBox.Text}` ADD CONSTRAINT {depAttrListBox.SelectedItem}_refto_{refAttrListBox.SelectedItem} FOREIGN KEY ({depAttrListBox.SelectedItem}) REFERENCES `{refTableComboBox.Text}` ({refAttrListBox.SelectedItem}) ON UPDATE CASCADE ON DELETE CASCADE", userDB.GetConnection());
com.ExecuteNonQuery();
}
catch (MySqlException ex)
{
MessageBox.Show("Невозможно установить связь по данным атрибутам");
flag = true;
}
mData.OpenConnection();
if (!flag)
{
DataTable table = GetTable(mData);
int nextID;
if (table.Rows.Count > 0)
{
nextID = UnicID(table);
}
else
{
nextID = 1;
}
MySqlCommand com = new MySqlCommand($"INSERT INTO `connection` (Connection_ID,Connection_Name,First_Atr_ID,Second_Atr_ID,Connection_Description) VALUES ({nextID}, \"{depAttrListBox.SelectedItem}_refto_{refAttrListBox.SelectedItem}\", {GetAttrID(depAttrListBox.SelectedItem.ToString())}, {GetAttrID(refAttrListBox.SelectedItem.ToString())}, \"{richTextBox1.Text}\")", mData.GetConnection());
com.ExecuteNonQuery();
MessageBox.Show("Связь успешно добавлена");
}
userDB.CloseConnection();
mData.CloseConnection();
}
private void Delete(string key_value)
{
GetKeyAttrib();
DataBase db;
MySqlCommand command;
string query;
if (delete_step == 0)
{
ShowOptionalTable(key_value, "delete");
}
else
{
db = new DataBase(currentDB);
query = $"DELETE FROM {get_table.Text} WHERE {attr_name} = {key_value}";
db.OpenConnection();
command = new MySqlCommand(query, db.GetConnection());
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Запись успешно удалена");
if (dataTable.Rows.Count != 0)
{
dataTable.Rows[0].Delete();
}
Optional_table.DataSource = dataTable;
InputKeyShow();
}
else
{
MessageBox.Show("Запись с данными значениями отсутствует");
dataTable.Rows.Clear();
InputKeyShow();
}
dataGridView.DataSource = GetTable(db, get_table.Text);
db.CloseConnection();
}
}
public void GetKeyAttrib()
{
DataBase db;
MySqlCommand command;
MySqlDataReader reader;
string query;
query = $"SELECT attribute.Attribute_Name, attribute.Is_Key FROM `attribute` INNER JOIN `table` ON (`attribute`.Table_ID = `table`.Table_ID) WHERE `table`.Name = \"{get_table.Text}\"";
db = new DataBase("prime_db");
command = new MySqlCommand(query, db.GetConnection());
db.OpenConnection();
reader = command.ExecuteReader();
while (reader.Read())
{
if (reader.GetValue(1).ToString() == "True")
{
attr_name = reader.GetValue(0).ToString();
}
}
db.CloseConnection();
reader.Close();
}
private void FillKeyLists()
{
KeyItemsListbox.Items.Clear();
NonKeyItemsListbox.Items.Clear();
DataBase mData = new DataBase("prime_db");
mData.OpenConnection();
MySqlCommand com = new MySqlCommand($"SELECT Attribute_Name FROM `attribute` WHERE Table_ID = {GetTableID(comboBoxTables.Text)}", mData.GetConnection());
MySqlDataReader reader = com.ExecuteReader();
while (reader.Read())
{
string currAttr = reader.GetValue(0).ToString();
if (GetKeyValue(currAttr) == 1)
{
KeyItemsListbox.Items.Add(currAttr);
}
else
{
NonKeyItemsListbox.Items.Add(currAttr);
}
}
mData.CloseConnection();
}
private void CreateAttrButton_Click(object sender, EventArgs e)
{
DataBase mData = new DataBase("prime_db");
DataBase userDB = new DataBase(currentDB);
int tID = GetTableID(tableName);
int UniqueValue = 0;
int KeyValue = 0;
int CanNullValue = 0;
if (UniqueCheckBox.Checked)
{
UniqueValue = 1;
}
if (CanNullCheckBox.Checked)
{
CanNullValue = 1;
}
string attrName = AttrNametextBox.Text;
string type = TypescomboBox.Text;
string strValue = StartValueTextBox.Text;
string desc = richTextBox1.Text;
#region CreateAttr
bool flag = false;
MySqlCommand altCom;
try
{
userDB.OpenConnection();
string nullDef = "NULL";
if (CanNullValue == 0)
{
nullDef = "Not Null";
}
altCom = new MySqlCommand($"ALTER TABLE `{tableName}` ADD COLUMN {attrName} {type} {nullDef}", userDB.GetConnection());
altCom.ExecuteNonQuery();
if (UniqueValue == 1)
{
altCom = new MySqlCommand($"ALTER TABLE `{tableName}` ADD CONSTRAINT {attrName}_un_const UNIQUE ({attrName})", userDB.GetConnection());
altCom.ExecuteNonQuery();
}
}catch (MySqlException)
{
flag = true;
MessageBox.Show("Не получилось добавить новый атрибут");
}
try
{
altCom = new MySqlCommand($"ALTER TABLE `{tableName}` DROP COLUMN `I_D`", userDB.GetConnection());
altCom.ExecuteNonQuery();
}
catch (MySqlException ex)
{
//Игнорируем, если удаление уже производилось
}
#endregion
if (!flag)
{
#region InsertIntoPrime
mData.OpenConnection();
DataTable table = GetTable(mData);
int nextID;
if (table.Rows.Count > 0)
{
nextID = UnicID(table);
}
else
{
nextID = 1;
}
MySqlCommand commandIns = new MySqlCommand($"INSERT INTO `attribute` (Attribute_ID,Attribute_Name,Type,Start_Value,Is_Unique,Is_Key,Can_Null,Description,Table_ID) VALUES ({nextID},\"{attrName}\",\"{type}\",\"{strValue}\",{UniqueValue},{KeyValue},{CanNullValue},\"{desc}\",{tID})", mData.GetConnection());
commandIns.ExecuteNonQuery();
mData.CloseConnection();
#endregion
if (strValue != "")
{
try
{
userDB.OpenConnection();
switch (type)
{
case ("Int"):
{
MySqlCommand defCom = new MySqlCommand($"ALTER TABLE `{tableName}` ALTER COLUMN `{attrName}` SET DEFAULT {strValue} ", userDB.GetConnection());
defCom.ExecuteNonQuery();
break;
}
default:
{
MySqlCommand defCom = new MySqlCommand($"ALTER TABLE `{tableName}` ALTER COLUMN `{attrName}` SET DEFAULT '{strValue}' ", userDB.GetConnection());
defCom.ExecuteNonQuery();
break;
}
}
}
catch (MySqlException ex)
{
MessageBox.Show($"Значение по умолчанию введено некорректно. Атрибут {attrName} добавлен без значения по умолчанию.");
DBChangeForm tform = new DBChangeForm(currentDB, tableName);
tform.Show();
this.Hide();
return;
}
}
DBChangeForm form = new DBChangeForm(currentDB, tableName);
form.Show();
this.Hide();
MessageBox.Show($"Атрибут {attrName} успешно добавлен!");
}
else
{
DBChangeForm form = new DBChangeForm(currentDB, tableName);
form.Show();
this.Hide();
}
}
private void ShowOptionalTable(string key_value, string func)
{
GetKeyAttrib();
DataBase db;
MySqlCommand command;
MySqlDataReader reader;
string query;
query = $"SELECT * FROM {get_table.Text} WHERE {attr_name} = {key_value}";
db = new DataBase(currentDB);
command = new MySqlCommand(query, db.GetConnection());
db.OpenConnection();
reader = command.ExecuteReader();
try
{
DataRow row;
row = dataTable.NewRow();
if (reader.HasRows != false)
{
while (reader.Read())
{
for (int i = 0; i < dataTable.Columns.Count; i++)
{
row[$"{dataTable.Columns[i].ColumnName}"] = reader.GetValue(i);
}
}
dataTable.Rows.Add(row);
Optional_table.DataSource = dataTable;
reader.Close();
db.CloseConnection();
if (func == "delete")
{
delete_step++;
}
else
{
update_step++;
}
InputKeyHide();
MessageBox.Show("Выбрана следующая запись, для продолжения нажмите Далее");
}
else
{
MessageBox.Show("Запись не найдена");
if (func == "delete")
{
delete_step = 0;
}
else
{
update_step = 0;
}
}
}
catch
{
MessageBox.Show("Ой! Что-то пошло не так!");
}
}
private void Update(string key_value)
{
GetKeyAttrib();
DataBase db;
MySqlCommand command;
string query;
if (update_step == 0)
{
ShowOptionalTable(key_value, "update");
AddDefaultRow();
}
else
{
db = new DataBase(currentDB);
string values = "";
string temp = "";
string temp2;
string[] data = new string[2];
for (int i = 0; i < dataTable.Columns.Count; i++)
{
temp = Optional_table[i, 1].Value.ToString();
if (dataTable.Columns[i].DataType.ToString() == "System.DateTime")
{
temp = $"{temp.Substring(0, 10)}";
data = temp.Split('.');
temp2 = data[2];
data[2] = data[0];
data[0] = temp2;
temp = $"\"{data[0]}.{data[1]}.{data[2]}\"";
}
if (dataTable.Columns[i].DataType.ToString() == "System.String")
{
temp = $"\"{temp}\"";
}
values += $"`{dataTable.Columns[i].ColumnName.Trim('*')}`={temp},";
}
values = values.Trim(',');
query = $"UPDATE `{get_table.Text}` SET {values} WHERE {attr_name} = {key_value}";
db.OpenConnection();
command = new MySqlCommand(query, db.GetConnection());
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Запись успешно обновлена");
dataTable.Rows.Clear();
Optional_table.DataSource = dataTable;
InputKeyShow();
update_step++;
}
else
{
MessageBox.Show("");
dataTable.Rows.Clear();
InputKeyShow();
}
dataGridView.DataSource = GetTable(db, get_table.Text);
db.CloseConnection();
}
}
private void Insert()
{
//char kavichka = '"';
string query = $"SELECT attribute.Attribute_Name, attribute.Is_Key FROM `attribute` INNER JOIN `table` ON (`attribute`.Table_ID = `table`.Table_ID) WHERE `table`.Name = \"{get_table.Text}\"";
DataBase db = new DataBase("prime_db");
MySqlCommand command = new MySqlCommand(query, db.GetConnection());
db.OpenConnection();
MySqlDataReader reader = command.ExecuteReader();
attr_name = "";
while (reader.Read())
{
attr_name += "`" + reader.GetValue(0).ToString() + "`,";
}
attr_name = attr_name.Trim(',');
db.CloseConnection();
db = new DataBase(currentDB);
try
{
db.OpenConnection();
string values = "";
string temp;
string temp2;
string[] data = new string[2];
for (int i = 0; i < Optional_table.Columns.Count; i++)
{
temp = Optional_table.Rows[0].Cells[i].Value.ToString();
if (dataTable.Columns[i].DataType.ToString() == "System.DateTime")
{//values += $"\"{DateTime.Parse(temp).ToString("d")}\",";
temp = $"{temp.Substring(0, 10)}";
data = temp.Split('.');
temp2 = data[2];
data[2] = data[0];
data[0] = temp2;
temp = $"\"{data[0]}.{data[1]}.{data[2]}\"";
}
if (dataTable.Columns[i].DataType.ToString() == "System.String")
{
temp = $"\"{temp}\"";
}
values += $"{temp},";
}
values = values.Trim(',');
query = $"INSERT INTO `{get_table.Text}` ({attr_name}) VALUES ({values});";
command = new MySqlCommand(query, db.GetConnection());
if (command.ExecuteNonQuery() == 1)
{
MessageBox.Show("Запись успешно добавлена");
dataGridView.DataSource = GetTable(db, get_table.Text);
dataTable.Rows.Clear();
AddDefaultRow();
}
else
{
MessageBox.Show("Ой! Что-то пошло не так!");
}
db.CloseConnection();
}
catch (MySqlException)
{
MessageBox.Show("Данное значение ключевого атрибута уже существует в таблице. Введите уникальное значение.");
}
catch (Exception ex)
{
MessageBox.Show("ERROR:" + ex.Message);
}
}
