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);
}
private void doTheItemsExist()
{
connection = DBReader.connectionCreator(DBName);
try
{
DBReader.connectionOpen(connection);
if (DBReader.procedureStatus(connection, tableName).Count() > 0)
{
buttonShowItems.Enabled = true;
items = DBReader.selectItems(connection, tableName);
}
else
{
buttonShowItems.Enabled = false;
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
}
private void refreshTable()
{
try
{
DBReader.connectionOpen(connection);
adapter = new MySqlDataAdapter();
adapter.SelectCommand = DBReader.commandForTheWholeTable(connection, itemsTableName);
dataSet = new DataTable();
adapter.Fill(dataSet);
BindingSource source = new BindingSource();
source.DataSource = dataSet;
dataGridViewItems.DataSource = source;
adapter.Update(dataSet);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
}
/// <summary>
/// Refreshes values in DataGridView
/// </summary>
private void refreshTable()
{
try
{
DBReader.connectionOpen(connection);
adapter = new MySqlDataAdapter();
if (textBoxSelectedPlayer.Text == "For all")
{
adapter.SelectCommand = DBReader.commandForTheWholeTable(connection, entityName);
}
else if (textBoxSelectedPlayer.Text.StartsWith("Player "))
{
playersID = DBReader.selectAllPlayersID(connection, DBName);
int ID = playersID[Int32.Parse(textBoxSelectedPlayer.Text.Substring(7)) - 1];
adapter.SelectCommand = DBReader.commandForSelectedPlayer(connection, entityName, ID);
}
dataSet = new DataTable();
adapter.Fill(dataSet);
BindingSource source = new BindingSource();
source.DataSource = dataSet;
tableEntity.DataSource = source;
adapter.Update(dataSet);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
}
/// <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>
/// 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);
}
}
private void newConnection(string databaseName)
{
connection = DBReader.connectionCreator(databaseName);
try
{
DBReader.connectionOpen(connection);
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
}
private void DataBaseNameSelector_Load(object sender, EventArgs e)
{
connection = DBReader.connectionCreatorWithNoDatabase();
try
{
DBReader.connectionOpen(connection);
comboBoxFill();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
}
/// <summary>
/// Rotates players when clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonRightArrow_Click(object sender, EventArgs e)
{
playersID = new List <int>();
try
{
DBReader.connectionOpen(connection);
playersID = DBReader.selectAllPlayersID(connection, DBName);
if (textBoxSelectedPlayer.Text == "For all")
{
buttonInsUp.Enabled = false;
currentID = 0;
textBoxSelectedPlayer.Text = "Player " + playersID[0];
}
else if (textBoxSelectedPlayer.Text == "Player " + playersID[playersID.Count() - 1])
{
if (textBoxSelectedItem.Text != "")
{
buttonInsUp.Enabled = true;
}
else
{
buttonInsUp.Enabled = false;
}
currentID = -1;
textBoxSelectedPlayer.Text = "For all";
}
else if (textBoxSelectedPlayer.Text.StartsWith("Player "))
{
buttonInsUp.Enabled = false;
currentID++;
textBoxSelectedPlayer.Text = "Player " + playersID[currentID];
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
}
/// <summary>
/// Drops the base when clicked
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void buttonDropTheBase_Click(object sender, EventArgs e)
{
var response = MessageBox.Show("Do you want to delete " + textBoxSelectedDatabase.Text + " session?", "Are you sure?", MessageBoxButtons.YesNo);
{
if (response == DialogResult.Yes)
{
var response2 = MessageBox.Show("Are you sure?", "Are you sure?", MessageBoxButtons.YesNo);
{
if (response == DialogResult.Yes)
{
string commandText = "drop database " + DBName + ";";
MySqlCommand dropCommand = new MySqlCommand(commandText, connection);
MySqlDataReader reader;
try
{
DBReader.connectionOpen(connection);
reader = dropCommand.ExecuteReader();
MessageBox.Show("Session was dropped!");
textBoxSelectedDatabase.Text = "";
textBoxSelectedItem.Text = "";
tableEntity.DataSource = null;
tableEntity.Refresh();
while (reader.Read())
{
}
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
}
}
}
}
}
/// <summary>
/// Opens everything when EntityManager is loaded
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void EntityManager_Load(object sender, EventArgs e)
{
namesToTextBoxes();
connection = DBReader.connectionCreator(DBName);
try
{
DBReader.connectionOpen(connection);
addNewRowEditor();
placeRowEditors();
}
catch (MySqlException ex)
{
MessageBox.Show(ex.Message);
}
finally
{
DBReader.connectionEnd(connection);
}
doTheItemsExist();
}
/// <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);
}
}