private void button2_Click(object sender, EventArgs e)
{
//connection DataBase
IndexForm connIndex = new IndexForm();
string conn = connIndex.Conn;
string query = $"delete from zapocetmswin2.potraviny where nazev_potraviny='{nazev_potraviny}'";
try
{
using (MySqlConnection mySqlConn = new MySqlConnection(conn))
{
using (MySqlCommand cmd = new MySqlCommand(query, mySqlConn))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
dataGridPotraviny.DataSource = dt;
}
}
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
dataGridPotraviny.DataSource = this.populateDataTable();
}
private void buttonOdebrat_Click(object sender, EventArgs e)
{
//connection DataBase
IndexForm connIndex = new IndexForm();
string conn = connIndex.Conn;
string query =
"delete from zapocetmswin2.kategorie where nazev_kategorie=@nazev";
try
{
using (MySqlConnection mySqlConn = new MySqlConnection(conn))
{
using (MySqlCommand cmd = new MySqlCommand(query, mySqlConn))
{
mySqlConn.Open();
cmd.Parameters.AddWithValue("@nazev", textBoxNazev.Text);
MySqlDataReader reader = cmd.ExecuteReader();
DialogResult dialog = MessageBox.Show("Odebrano");
if (dialog == DialogResult.OK)
{
this.Close();
}
reader.Read();
reader.Close();
mySqlConn.Close();
}
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
}
private void PridatJidloForm_FormClosed(object sender, FormClosedEventArgs e)
{
//connection DataBase
IndexForm connIndex = new IndexForm();
string conn = connIndex.Conn;
try
{
using (MySqlConnection mySqlConn = new MySqlConnection(conn))
{
mySqlConn.Open();
string query = "delete from zapocetmswin2.suroviny where id_jidlo IS NULL;";
using (MySqlCommand cmd = new MySqlCommand(query, mySqlConn))
{
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
reader.Close();
}
mySqlConn.Close();
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
}
private DataTable populateDataTable()
{
//connection DataBase
IndexForm connIndex = new IndexForm();
string conn = connIndex.Conn;
string query = "SELECT nazev_potraviny 'Nazev potraviny', pocet_potravin, jednotka from zapocetmswin2.potraviny";
query += $" WHERE nazev_potraviny LIKE '{hledani.Text}%'";
try
{
using (MySqlConnection mySqlConn = new MySqlConnection(conn))
{
using (MySqlCommand cmd = new MySqlCommand(query, mySqlConn))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
return(dt);
}
}
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
return(null);
}
}
private void dataGridKategorie_CellMouseClick(object sender, DataGridViewCellMouseEventArgs e)
{
//connection DataBase
IndexForm connIndex = new IndexForm();
string conn = connIndex.Conn;
if (e.RowIndex >= 0)
{
try
{
using (MySqlConnection mySqlConn = new MySqlConnection(conn))
{
DataGridViewRow row = dataGridKategorie.Rows[e.RowIndex];
string query =
$"SELECT recept ,nazev_jidlo 'Nazev jidla', vaha 'Vaha (g)', cena from zapocetmswin2.jidlo where id_kategorie = '{row.Cells[0].Value.ToString()}'";
using (MySqlCommand cmd = new MySqlCommand(query, mySqlConn))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
datagridJidlo.DataSource = dt;
datagridJidlo.Columns[0].Visible = false;
}
}
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
}
}
private DataTable PopulateKategorie()
{
//connection DataBase
IndexForm connIndex = new IndexForm();
string conn = connIndex.Conn;
string query = "SELECT id_kategorie, nazev_kategorie 'Nazev' from zapocetmswin2.kategorie";
try
{
using (MySqlConnection mySqlConn = new MySqlConnection(conn))
{
using (MySqlCommand cmd = new MySqlCommand(query, mySqlConn))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
return(dt);
}
}
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
return(null);
}
}
private void pridatSurovinu_Click(object sender, EventArgs e)
{
//connection DataBase
IndexForm connIndex = new IndexForm();
string conn = connIndex.Conn;
try
{
using (MySqlConnection mySqlConn = new MySqlConnection(conn))
{
string queryID =
$"select id_potraviny from zapocetmswin2.potraviny where nazev_potraviny = '{comboBoxSuroviny.Text}'";
string idPotraviny = "";
mySqlConn.Open();
using (MySqlCommand cmd = new MySqlCommand(queryID, mySqlConn))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
idPotraviny = reader.GetString("id_potraviny");
}
}
}
string queryAdd =
$"insert into zapocetmswin2.suroviny (id_potraviny, pocet_suroviny) values ({idPotraviny},{textBoxPocet.Text})";
using (MySqlCommand cmd = new MySqlCommand(queryAdd, mySqlConn))
{
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
reader.Close();
}
string queryIns =
"select nazev_potraviny 'Nazev potraviny', pocet_suroviny 'Pocet suroviny' from zapocetmswin2.potraviny natural join zapocetmswin2.suroviny where id_jidlo is null";
using (MySqlCommand cmd = new MySqlCommand(queryIns, mySqlConn))
{
using (MySqlDataAdapter adapter = new MySqlDataAdapter(cmd))
{
DataTable dt = new DataTable();
adapter.Fill(dt);
dataGridViewSuroviny.DataSource = dt;
}
}
mySqlConn.Close();
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
}
private void PridatJidloForm_Load(object sender, EventArgs e)
{
//connection DataBase
IndexForm connIndex = new IndexForm();
string conn = connIndex.Conn;
string queryKategorie = "select nazev_kategorie from zapocetmswin2.kategorie";
string querySuroviny = "select nazev_potraviny from zapocetmswin2.potraviny";
try
{
using (MySqlConnection mySqlConn = new MySqlConnection(conn))
{
mySqlConn.Open();
using (MySqlCommand cmd = new MySqlCommand(queryKategorie, mySqlConn))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
string name = reader.GetString("nazev_kategorie");
comboBoxKategorie.Items.Add(name);
comboBoxKategorie.DropDownStyle = ComboBoxStyle.DropDownList;
comboBoxKategorie.SelectedIndex = comboBoxKategorie.Items.Count - 1;
}
}
}
using (MySqlCommand cmd = new MySqlCommand(querySuroviny, mySqlConn))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
comboBoxSuroviny.AutoCompleteMode = AutoCompleteMode.SuggestAppend;
comboBoxSuroviny.AutoCompleteSource = AutoCompleteSource.ListItems;
string name = reader.GetString("nazev_potraviny");
comboBoxSuroviny.Items.Add(name);
}
}
}
mySqlConn.Close();
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
}
private void PridaniForm_Load(object sender, EventArgs e)
{
//connection DataBase
IndexForm connIndex = new IndexForm();
string conn = connIndex.Conn;
string query = "select nazev_jednotka from zapocetmswin2.jednotka";
MySqlConnection mysqlcon = new MySqlConnection(conn);
mysqlcon.Open();
MySqlCommand cmd = new MySqlCommand(query, mysqlcon);
MySqlDataReader reader = cmd.ExecuteReader();
while (reader.Read())
{
string name = reader.GetString("nazev_jednotka");
comboBoxJednotka.Items.Add(name);
comboBoxJednotka.DropDownStyle = ComboBoxStyle.DropDownList;
comboBoxJednotka.SelectedIndex = comboBoxJednotka.Items.Count - 1;
}
mysqlcon.Close();
}
private void buttonPridani_Click(object sender, EventArgs e)
{
//connection DataBase
IndexForm connIndex = new IndexForm();
string conn = connIndex.Conn;
string query =
"insert into zapocetmswin2.potraviny (nazev_potraviny, pocet_potravin, jednotka) values (@nazev, @pocet, @jednotka)";
try
{
using (MySqlConnection mySqlConn = new MySqlConnection(conn))
{
using (MySqlCommand cmd = new MySqlCommand(query, mySqlConn))
{
mySqlConn.Open();
cmd.Parameters.AddWithValue("@nazev", nazevPridani.Text);
cmd.Parameters.AddWithValue("@pocet", pocetPridani.Text);
cmd.Parameters.AddWithValue("@jednotka", comboBoxJednotka.Text);
MySqlDataReader reader = cmd.ExecuteReader();
DialogResult dialog = MessageBox.Show("Pridano");
if (dialog == DialogResult.OK)
{
this.Close();
}
reader.Read();
reader.Close();
mySqlConn.Close();
}
}
}
catch (Exception exception)
{
MessageBox.Show(exception.Message);
}
}
private void pridatDB_Click(object sender, EventArgs e)
{
//connection DataBase
IndexForm connIndex = new IndexForm();
string conn = connIndex.Conn;
string idKat = "";
try
{
using (MySqlConnection mySqlConn = new MySqlConnection(conn))
{
mySqlConn.Open();
string queryIDKat =
$"select id_kategorie from zapocetmswin2.kategorie where nazev_kategorie='{comboBoxKategorie.Text}'";
using (MySqlCommand cmd = new MySqlCommand(queryIDKat, mySqlConn))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
idKat = reader.GetString("id_kategorie");
}
}
}
string queryINS =
"insert into zapocetmswin2.jidlo (nazev_jidlo, vaha, cena, recept, id_kategorie) values (@nazev_jidlo, @vaha, @cena, @recept, @id_kategorie)";
using (MySqlCommand cmd = new MySqlCommand(queryINS, mySqlConn))
{
cmd.Parameters.AddWithValue("@nazev_jidlo", textBoxNazevJidla.Text);
cmd.Parameters.AddWithValue("@vaha", textBoxVaha.Text);
cmd.Parameters.AddWithValue("@cena", textBoxCena.Text);
cmd.Parameters.AddWithValue("@recept", textBoxRecept.Text);
cmd.Parameters.AddWithValue("@id_kategorie", idKat);
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
reader.Close();
}
string queryIDJidlo = "SELECT id_jidlo FROM zapocetmswin2.jidlo ORDER BY id_jidlo DESC LIMIT 1";
int idJidlo = 0;
using (MySqlCommand cmd = new MySqlCommand(queryIDJidlo, mySqlConn))
{
using (MySqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
idJidlo = Convert.ToInt32(reader.GetString("id_jidlo"));
}
}
}
string queryUPD =
$"update zapocetmswin2.suroviny set id_jidlo={idJidlo} WHERE suroviny.id_jidlo IS NULL";
using (MySqlCommand cmd = new MySqlCommand(queryUPD, mySqlConn))
{
MySqlDataReader reader = cmd.ExecuteReader();
reader.Read();
reader.Close();
}
DialogResult dialog = MessageBox.Show("Pridano");
if (dialog == DialogResult.OK)
{
this.Close();
}
mySqlConn.Close();
}
}
catch (Exception exception)
{
Console.WriteLine(exception.Message);
MessageBox.Show(exception.Message);
}
}