private List <string> getColumnsInBothTables(string normalName, string itemsName)
{
connection = DBReader.connectionCreator(DBName);
try
{
DBReader.connectionOpen(connection);
columnsInBothTables = new List <string>();
columnsInNormalTable = new List <string>();
columnsInItemsTable = new List <string>();
columnsInNormalTable = DBReader.selectAllColumnNames(connection, normalName);
columnsInItemsTable = DBReader.selectAllColumnNames(connection, itemsName);
foreach (string value in columnsInNormalTable)
{
for (int i = 0; i < columnsInItemsTable.Count(); i++)
{
if (value == columnsInItemsTable[i])
{
columnsInBothTables.Add(value);
}
}
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
return(columnsInBothTables);
}
/// <summary>
/// Deletes values in table when clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonDelete_Click(object sender, EventArgs e)
{
List <RowEditor> listOfRowEditors = new List <RowEditor>();
List <string> listOfColumns = new List <string>();
try
{
DBReader.connectionOpen(connection);
listOfColumns = DBReader.selectAllColumnNames(connection, tableName);
foreach (RowEditor editorControl in splitContainer.Panel2.Controls)
{
listOfRowEditors.Add(editorControl);
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
string idValue = "";
foreach (RowEditor editorControl in listOfRowEditors)
{
if (editorControl.labelName == "id_")
{
idValue = editorControl.valueName;
}
}
string commandText = "delete from " + tableName + " where id_ ='" + idValue + "' ;";
MySqlCommand deleteCommand = new MySqlCommand(commandText, connection);
MySqlDataReader reader;
try
{
DBReader.connectionOpen(connection);
reader = deleteCommand.ExecuteReader();
MessageBox.Show("Data was deleted");
while (reader.Read())
{
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
}
/// <summary>
/// Adds new RowEditor controls to splitContainer's Panel2
/// </summary>
private void addNewRowEditor()
{
List <string> allColumns = DBReader.selectAllColumnNames(connection, tableName);
for (int i = 0; i < allColumns.Count(); i++)
{
RowEditor editor = new RowEditor(allColumns[i], textBoxFor.Text);
splitContainer.Panel2.Controls.Add(editor);
}
}
/// <summary>
/// Inserts values in table when clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonInsert_Click(object sender, EventArgs e)
{
string columnValuesForInsert = "";
string textBoxValuesForInsert = "'";
try
{
DBReader.connectionOpen(connection);
foreach (string columnName in DBReader.selectAllColumnNames(connection, tableName))
{
columnValuesForInsert += columnName + ", ";
}
columnValuesForInsert = columnValuesForInsert.Remove(columnValuesForInsert.Length - 2);
foreach (RowEditor editorControl in splitContainer.Panel2.Controls)
{
textBoxValuesForInsert += editorControl.valueName + "','";
}
textBoxValuesForInsert = textBoxValuesForInsert.Remove(textBoxValuesForInsert.Length - 2);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
string commandText = "insert into " + tableName + " (" + columnValuesForInsert + ") values(" + textBoxValuesForInsert + ");";
MySqlCommand insertCommand = new MySqlCommand(commandText, connection);
MySqlDataReader reader;
try
{
DBReader.connectionOpen(connection);
reader = insertCommand.ExecuteReader();
MessageBox.Show("Data was added to the " + textBoxEntity.Text);
while (reader.Read())
{
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
}
/// <summary>
/// Updates values in table when clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonUpdate_Click(object sender, EventArgs e)
{
string columnAndTextBoxValuesForUpdate = "";
List <RowEditor> listOfRowEditors = new List <RowEditor>();
List <string> listOfColumns = new List <string>();
try
{
DBReader.connectionOpen(connection);
listOfColumns = DBReader.selectAllColumnNames(connection, tableName);
foreach (RowEditor editorControl in splitContainer.Panel2.Controls)
{
listOfRowEditors.Add(editorControl);
}
for (int i = 0; i < listOfColumns.Count(); i++)
{
columnAndTextBoxValuesForUpdate += DBReader.selectAllColumnNames(connection, tableName)[i] + "='" + listOfRowEditors[i].valueName + "',";
}
columnAndTextBoxValuesForUpdate = columnAndTextBoxValuesForUpdate.Remove(columnAndTextBoxValuesForUpdate.Length - 1);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
string idValue = "";
foreach (RowEditor editorControl in listOfRowEditors)
{
if (editorControl.labelName == "id_")
{
idValue = editorControl.valueName;
}
}
string commandText = "update " + tableName + " set " + columnAndTextBoxValuesForUpdate + " where id_ ='" + idValue + "' ;";
MySqlCommand updateCommand = new MySqlCommand(commandText, connection);
MySqlDataReader reader;
try
{
DBReader.connectionOpen(connection);
reader = updateCommand.ExecuteReader();
MessageBox.Show("Data was updated");
while (reader.Read())
{
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
}