private void buttonDeleteTable_Click(object sender, EventArgs e)
{
DataBase mData = new DataBase("prime_db");
DataBase userDB = new DataBase(currentDB);
mData.OpenConnection();
bool flag = false;
try
{
userDB.OpenConnection();
MySqlCommand commandCreate = new MySqlCommand($"DROP TABLE IF EXISTS {comboBoxTables.Text}", userDB.GetConnection());
commandCreate.ExecuteNonQuery();
}
catch (MySqlException ex)
{
flag = true;
MessageBox.Show($"Не получилось произвести удаление таблицы {comboBoxTables.Text}. Попробуйте сначала удалить зависимости");
}
if (!flag)
{
MySqlCommand commandIns = new MySqlCommand($"DELETE FROM `table` WHERE Name = \"{comboBoxTables.Text}\"", mData.GetConnection());
commandIns.ExecuteNonQuery();
MessageBox.Show($"Таблица {comboBoxTables.Text} успешно удалена!");
}
DBChangeForm form = new DBChangeForm(currentDB);
form.Show();
this.Hide();
}
private void buttonCreateTable_Click(object sender, EventArgs e)
{
DataBase mData = new DataBase("prime_db");
DataBase userDB = new DataBase(currentDB);
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 `table` (Table_ID,Name,Description) VALUES ({nextID},\"{tableName}\",\"{richTextBox1.Text}\")", mData.GetConnection());
commandIns.ExecuteNonQuery();
userDB.OpenConnection();
MySqlCommand commandCreate = new MySqlCommand($@"
CREATE TABLE {tableName}
(I_D INT NOT NULL)", userDB.GetConnection());
commandCreate.ExecuteNonQuery();
DBChangeForm form = new DBChangeForm(currentDB);
form.Show();
this.Hide();
MessageBox.Show($"Таблица {tableName} успешно добавлена!");
}
private void DeleteAttrButton_Click(object sender, EventArgs e)
{
DataBase mData = new DataBase("prime_db");
DataBase userDB = new DataBase(currentDB);
string deopAttrName = AllAttrsListBox.SelectedItem.ToString();
string sourceName = GetTableName(GetSourceId(deopAttrName));
bool flag = false;
mData.OpenConnection();
userDB.OpenConnection();
try
{
MySqlCommand altCom = new MySqlCommand($"ALTER TABLE `{sourceName}` DROP COLUMN `{deopAttrName}`", userDB.GetConnection());
altCom.ExecuteNonQuery();
}catch (MySqlException ex)
{
flag = true;
MessageBox.Show($"Не удалось произвести удаление атрибута {deopAttrName}");
}
if (!flag)
{
MySqlCommand com = new MySqlCommand($"DELETE FROM `attribute` WHERE Attribute_Name = \"{deopAttrName}\"", mData.GetConnection());
com.ExecuteNonQuery();
MessageBox.Show($"Удаление атрибута {deopAttrName} выполнено!");
DBChangeForm form = new DBChangeForm(currentDB);
form.Show();
this.Hide();
}
}
private void buttonCreate_Click(object sender, EventArgs e)
{
MySqlConnection con = new MySqlConnection("server=localhost;port=3306;username=root;password=root");
con.Open();
MySqlCommand com = new MySqlCommand($"DROP DATABASE IF EXISTS user_db", con);
com.ExecuteNonQuery();
MySqlCommand command = new MySqlCommand($"CREATE DATABASE IF NOT EXISTS user_db", con);
command.ExecuteNonQuery();
currentDB = "user_db";
DataBase mData = new DataBase("prime_db");
mData.OpenConnection();
string[] names = { "table", "connection", "attribute" };
foreach (string t in names)
{
MySqlCommand del = new MySqlCommand($"DELETE FROM `{t}`", mData.GetConnection());
del.ExecuteNonQuery();
}
MainForm form = new MainForm(currentDB);
form.Show();
this.Hide();
MessageBox.Show($"База данных {currentDB} создана успешно!");
}
private void ChangeKeyButton_Click(object sender, EventArgs e)
{
DataBase mData = new DataBase("prime_db");
DataBase userDB = new DataBase(currentDB);
string updKeyValues = "";
mData.OpenConnection();
userDB.OpenConnection();
foreach (string attr in KeyItemsListbox.Items)
{
MySqlCommand updComm = new MySqlCommand($"UPDATE `attribute` SET Is_Key = 1 WHERE Attribute_Name = \"{attr}\"", mData.GetConnection());
updComm.ExecuteNonQuery();
updKeyValues += attr + ", ";
}
foreach (string attr in NonKeyItemsListbox.Items)
{
MySqlCommand updComm = new MySqlCommand($"UPDATE `attribute` SET Is_Key = 0 WHERE Attribute_Name = \"{attr}\"", mData.GetConnection());
updComm.ExecuteNonQuery();
}
try
{
MySqlCommand dropKey = new MySqlCommand($"ALTER TABLE `{comboBoxTables.Text}` DROP PRIMARY KEY", userDB.GetConnection());
dropKey.ExecuteNonQuery();
}
catch (MySqlException ex)
{
//Если ключа нет - игнорируем
}
updKeyValues = updKeyValues.Remove(updKeyValues.Length - 2);
MySqlCommand createKey = new MySqlCommand($"ALTER TABLE `{comboBoxTables.Text}` ADD CONSTRAINT {comboBoxTables.Text}_keyValue PRIMARY KEY ({updKeyValues})", userDB.GetConnection());
createKey.ExecuteNonQuery();
MessageBox.Show($"Атрибуты: {updKeyValues} теперь составляют первичный ключ таблицы {comboBoxTables.Text}!");
}
private int GetTableID(string tName)
{
DataBase mData = new DataBase("prime_db");
mData.OpenConnection();
MySqlCommand com = new MySqlCommand($"SELECT Table_ID FROM `table` WHERE Name = \"{tName}\"", mData.GetConnection());
MySqlDataReader reader = com.ExecuteReader();
reader.Read();
return(Convert.ToInt32(reader.GetValue(0).ToString()));
}
private int GetSourceId(string attrName)
{
DataBase mData = new DataBase("prime_db");
mData.OpenConnection();
MySqlCommand com = new MySqlCommand($"SELECT Table_ID FROM `attribute` WHERE Attribute_Name = \"{attrName}\"", mData.GetConnection());
MySqlDataReader reader = com.ExecuteReader();
reader.Read();
return(Convert.ToInt32(reader.GetValue(0)));
}
private string GetTableName(int id)
{
DataBase mData = new DataBase("prime_db");
mData.OpenConnection();
MySqlCommand com = new MySqlCommand($"SELECT Name FROM `table` WHERE Table_ID = {id}", mData.GetConnection());
MySqlDataReader reader = com.ExecuteReader();
reader.Read();
return(reader.GetValue(0).ToString());
}
private DataTable GetTable(DataBase connection)
{
connection.OpenConnection();
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataTable table = new DataTable();
string query_select_id = $"SELECT * FROM `attribute`";
MySqlCommand command = new MySqlCommand(query_select_id, connection.GetConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
return(table);
}
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 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 button_select_Click(object sender, EventArgs e)
{
List <string> checkedItems = new List <string>();
if (checkedList_atribytes.CheckedItems.Count != 0)
{
for (int x = 0; x < checkedList_atribytes.CheckedItems.Count; x++)
{
checkedItems.Add(checkedList_atribytes.CheckedItems[x].ToString());
}
}
DataBase db = new DataBase(currentDB);
db.OpenConnection();
dataGridView.DataSource = select_some_atr(db, get_table.Text, checkedItems);
}
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 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);
}
//заполнение CheckBox именами атрибутов | обращение к метаданным
private void FillCheckBoxList()
{
dataTable = new DataTable();
checkedList_atribytes.Items.Clear();
DataBase db = new DataBase("prime_db");
db.OpenConnection();
//вытащить из метаданных
string query = $"SELECT attribute.Attribute_Name, attribute.Type, attribute.Is_Key FROM `attribute` INNER JOIN `table` ON (`attribute`.Table_ID = `table`.Table_ID) WHERE `table`.Name = \"{get_table.Text}\"";
MySqlCommand command = new MySqlCommand(query, db.GetConnection());
MySqlDataReader reader = command.ExecuteReader();
DataColumn column;
DataRow row;
row = dataTable.NewRow();
while (reader.Read())
{
column = new DataColumn();
if (reader.GetValue(2).ToString() == "True")
{
column.ColumnName = reader.GetValue(0).ToString() + "*"; //название колонки
}
else
{
column.ColumnName = reader.GetValue(0).ToString(); //название колонки
}
column.DataType = Type.GetType(get_type(reader.GetValue(1).ToString())); //тип колонки
dataTable.Columns.Add(column);
checkedList_atribytes.Items.Add(reader.GetValue(0).ToString());
try { row[column.ColumnName] = DBNull.Value.Equals(column.DataType); }
catch
{
row[column.ColumnName] = Convert.ToDateTime("1999-01-01");
}
}
dataTable.Rows.Add(row);
Optional_table.DataSource = dataTable;
Optional_table.Enabled = false;
}
//таблица с выбранными атрибутами
private DataTable select_some_atr(DataBase connection, string table_name, List <string> checkedItems)
{
connection.OpenConnection();
MySqlDataAdapter adapter = new MySqlDataAdapter();
DataTable table = new DataTable();
string new_string = "";
foreach (string tt in checkedItems)
{
new_string += tt + ", ";
}
new_string = new_string.Trim(',', ' ');
string query_select_id = $"SELECT {new_string} FROM `{table_name}`";
MySqlCommand command = new MySqlCommand(query_select_id, connection.GetConnection());
adapter.SelectCommand = command;
adapter.Fill(table);
return(table);
}
public MainForm()
{
InitializeComponent();
currentDB = "user_db";
try
{
DataBase db = new DataBase(currentDB);
db.OpenConnection();
}
catch (MySqlException ex)
{
currentDB = null;
}
if (currentDB == null)
{
buttonChangeDB.Enabled = false;
buttonEntries.Enabled = false;
buttonQueries.Enabled = false;
}
}
//подключение к таблице
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();
}
//заполнение Box именами таблиц
private void get_table_TextChanged(object sender, EventArgs e)
{
DataBase db = new DataBase(currentDB);
db.OpenConnection();
dataGridView.DataSource = GetTable(db, get_table.Text);
FillCheckBoxList();
button_select.Enabled = false;
button_delete.Enabled = true;
button_insert.Enabled = true;
button_update.Enabled = true;
button_do.Enabled = true;
button_insert.BackColor = Color.FromArgb(227, 227, 227);
button_delete.BackColor = Color.FromArgb(227, 227, 227);
button_update.BackColor = Color.FromArgb(227, 227, 227);
InputKeyHide();
delete_step = 0;
update_step = 0;
dataTable.Rows.Clear();
}
private void DeleteConnectionButton_Click(object sender, EventArgs e)
{
DataBase mData = new DataBase("prime_db");
DataBase userDate = new DataBase(currentDB);
string[] fkey = ConnectionsListBox.SelectedItem.ToString().Split(new char[] { ' ' }, StringSplitOptions.RemoveEmptyEntries);
int connID = Convert.ToInt32(fkey[0]);
string connName = GetConnectionName(connID);
string fKeyAttrName = fkey[1];
string depTName = GetTableName(GetSourceId(fKeyAttrName));
mData.OpenConnection();
userDate.OpenConnection();
bool flag = false;
try
{
MySqlCommand com = new MySqlCommand($"ALTER TABLE `{depTName}` DROP FOREIGN KEY {connName}", userDate.GetConnection());
com.ExecuteNonQuery();
}catch (MySqlException ex)
{
flag = true;
MessageBox.Show("Не получилось удалить связь");
}
if (!flag)
{
MySqlCommand remEntCom = new MySqlCommand($"DELETE FROM `connection` WHERE Connection_ID = {connID}", mData.GetConnection());
remEntCom.ExecuteNonQuery();
MessageBox.Show("Удаление внешнего ключа выполнено успешно!");
DBChangeForm form = new DBChangeForm(currentDB);
form.Show();
this.Hide();
}
}
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 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);
}
}
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 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 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();
}
}