private void fillDataGrid() { this.Enabled = false; LoadingWindow loadingWindow = new LoadingWindow(); loadingWindow.Show(); Application.DoEvents(); DatabaseControl.ConnectDB(); string sql = "SELECT * FROM recipes ORDER BY title ASC"; SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); SQLiteDataReader reader = command.ExecuteReader(); dataGridView1.Rows.Clear(); dataGridView1.Refresh(); while (reader.Read()) { dataGridView1.Rows.Add(); dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["id"].Value = reader["id"]; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Title"].Value = reader["title"]; Bitmap img; try { System.Net.WebRequest request = System.Net.WebRequest.Create(reader["img"].ToString()); request.Timeout = 500; System.Net.WebResponse response = request.GetResponse(); System.IO.Stream responseStream = response.GetResponseStream(); img = new Bitmap(responseStream); } catch (Exception ex) { img = null; } dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Image"].Value = img; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Time"].Value = reader["time"]; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Difficulty_level"].Value = reader["difficulty_level"]; } ((DataGridViewImageColumn)dataGridView1.Columns[2]).ImageLayout = DataGridViewImageCellLayout.Stretch; DatabaseControl.DisonnectDB(); this.Enabled = true; loadingWindow.Close(); }
public MyIngredientsWindow() { InitializeComponent(); ToolTip toolTip1 = new ToolTip(); ToolTip toolTip2 = new ToolTip(); toolTip1.SetToolTip(this.addIngredientButton, "Dodaj wiersz do listy"); toolTip1.SetToolTip(this.delIngredientButton, "Usuń wiersz z listy"); foreach (DataGridViewColumn col in dataGridView1.Columns) { col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter; } IngredientsListName = new Dictionary <string, Ingredient>(); IngredientsListId = new Dictionary <string, Ingredient>(); usedIngredientsList = new Dictionary <string, Ingredient>(); DatabaseControl.ConnectDB(); string sql = "SELECT * FROM ingredients ORDER BY name ASC"; SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { IngredientsListName.Add(reader["name"].ToString(), new Ingredient(reader["id"].ToString(), reader["name"].ToString(), reader["unit"].ToString())); IngredientsListId.Add(reader["id"].ToString(), new Ingredient(reader["id"].ToString(), reader["name"].ToString(), reader["unit"].ToString())); } sql = "SELECT * FROM my_ingredients"; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); reader = command.ExecuteReader(); dataGridView1.Rows.Clear(); dataGridView1.Refresh(); while (reader.Read()) { Debug.WriteLine("Liczba wierszy: " + dataGridView1.RowCount); dataGridView1.Rows.Add(reader["id"], new DataGridViewComboBoxCell(), reader["count"], IngredientsListId[reader["id"].ToString()].unit); DataGridViewComboBoxCell TempComboCell = (DataGridViewComboBoxCell)dataGridView1.Rows[dataGridView1.Rows.Count - 2].Cells["IngridientName"]; TempComboCell.Items.Add(IngredientsListId[reader["id"].ToString()].name); TempComboCell.Value = IngredientsListId[reader["id"].ToString()].name; usedIngredientsList.Add(IngredientsListId[reader["id"].ToString()].name, IngredientsListId[reader["id"].ToString()]); } DatabaseControl.DisonnectDB(); }
private void acceptButton_Click(object sender, EventArgs e) { if (id != -1) { if (ingredient_name != ingredientNameTextBox.Text && IsinDB(ingredientNameTextBox.Text)) { MessageBox.Show("Podany składnik już istnieje!"); return; } GC.Collect(); GC.WaitForPendingFinalizers(); DatabaseControl.ConnectDB(); string sql = "UPDATE ingredients SET name = '" + ingredientNameTextBox.Text + "', unit = '" + ingredientUnitTextBox.Text + "' WHERE id = " + id; SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); DatabaseControl.DisonnectDB(); } else { if (IsinDB(ingredientNameTextBox.Text)) { MessageBox.Show("Podany składnik już istnieje!"); return; } GC.Collect(); GC.WaitForPendingFinalizers(); DatabaseControl.ConnectDB(); string sql = "INSERT INTO ingredients (name, unit) VALUES ('" + ingredientNameTextBox.Text + "', '" + ingredientUnitTextBox.Text + "')"; SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); string ingredient_id = DatabaseControl.m_dbConnection.LastInsertRowId.ToString(); sql = "ALTER TABLE recipes ADD COLUMN ingredient_" + ingredient_id + " REAL DEFAULT 0.0"; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); DatabaseControl.DisonnectDB(); } edited(true); this.Close(); }
private void fillDataGrid() { DatabaseControl.ConnectDB(); string sql = "SELECT * FROM ingredients ORDER BY name ASC"; SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); SQLiteDataReader reader = command.ExecuteReader(); dataGridView1.Rows.Clear(); dataGridView1.Refresh(); while (reader.Read()) { dataGridView1.Rows.Add(reader["id"], reader["name"], reader["unit"]); } DatabaseControl.DisonnectDB(); }
private void AcceptButton_Click(object sender, EventArgs e) { bool isAllIngredientsFilled = true; DataGridViewRow Row; for (int i = 0; i < dataGridView1.RowCount - 1; ++i) { Row = dataGridView1.Rows[i]; if (Row.Cells[1].Value == null || Row.Cells[2].Value == null) { isAllIngredientsFilled = false; break; } } if (!isAllIngredientsFilled) { MessageBox.Show("UZUPEŁNIJ WSZYSTKIE WARTOŚCI DLA SKŁADNIKÓW!!!"); return; } DatabaseControl.ConnectDB(); SQLiteCommand command = new SQLiteCommand("DROP TABLE my_ingredients", DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); command = new SQLiteCommand("CREATE TABLE my_ingredients (id INTEGER NOT NULL," + "count REAL DEFAULT 0.0)", DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); if (dataGridView1.RowCount > 1) { for (int i = 0; i < dataGridView1.RowCount - 1; ++i) { Row = dataGridView1.Rows[i]; command = new SQLiteCommand("INSERT INTO my_ingredients (id, count) VALUES (@id, @count)", DatabaseControl.m_dbConnection); command.Parameters.AddWithValue("@id", Row.Cells[0].Value.ToString()); command.Parameters.AddWithValue("@count", Row.Cells[2].Value.ToString().Replace(',', '.')); command.ExecuteNonQuery(); } } DatabaseControl.DisonnectDB(); this.Close(); }
private void createIngredientsList(Dictionary <string, Ingredient> keyValuePairs) { DatabaseControl.ConnectDB(); keyValuePairs.Clear(); string sql = "SELECT * FROM ingredients ORDER BY name ASC"; SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { keyValuePairs.Add(reader["name"].ToString(), new Ingredient(reader["id"].ToString(), reader["name"].ToString(), reader["unit"].ToString())); } DatabaseControl.DisonnectDB(); }
private bool IsinDB(string name) { GC.Collect(); GC.WaitForPendingFinalizers(); DatabaseControl.ConnectDB(); SQLiteCommand command = new SQLiteCommand("SELECT * FROM ingredients WHERE name = @name", DatabaseControl.m_dbConnection); command.Parameters.AddWithValue("@name", name); SQLiteDataReader reader = command.ExecuteReader(); if (reader.HasRows) { DatabaseControl.DisonnectDB(); return(true); } else { DatabaseControl.DisonnectDB(); return(false); } }
public IngredientForm(int id = -1) { InitializeComponent(); this.id = id; if (id != -1) { DatabaseControl.ConnectDB(); string sql = "SELECT * FROM ingredients WHERE id = " + id; SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); SQLiteDataReader reader = command.ExecuteReader(); reader.Read(); ingredient_name = reader["name"].ToString(); string ingredient_unit = reader["unit"].ToString(); ingredientNameTextBox.Text = ingredient_name; ingredientUnitTextBox.Text = ingredient_unit; DatabaseControl.DisonnectDB(); } }
private void delButton_Click(object sender, EventArgs e) { DialogResult dialogResult = MessageBox.Show("Czy na pewno usunąć przepis?", "Usuń przepis", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { DataGridViewSelectedRowCollection row = dataGridView1.SelectedRows; string id = row[0].Cells["id"].Value.ToString(); GC.Collect(); GC.WaitForPendingFinalizers(); DatabaseControl.ConnectDB(); string sql = "DELETE FROM recipes WHERE id = " + id.ToString(); SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); DatabaseControl.DisonnectDB(); dataGridView1.Rows.RemoveAt(dataGridView1.CurrentCell.RowIndex); editedRecipesList(true); } }
private void delButton_Click(object sender, EventArgs e) { DialogResult dialogResult = MessageBox.Show("Czy na pewno usunąć składnik?\nZOSTANĄ USUNIĘTE RÓWNIEŻ PRZEPISY ZAWIERAJĄCE TEN SKŁADNIK ORAZ ELEMENTY Z TWOJEJ LISTY SKŁADNIKÓW!!!", "Usuń składnik", MessageBoxButtons.YesNo); if (dialogResult == DialogResult.Yes) { DataGridViewSelectedRowCollection row = dataGridView1.SelectedRows; string id = row[0].Cells["id"].Value.ToString(); GC.Collect(); GC.WaitForPendingFinalizers(); DatabaseControl.ConnectDB(); string sql = "DELETE FROM ingredients WHERE id = " + id.ToString(); SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); sql = "DELETE FROM recipes WHERE ingredient_" + id + " <> 0.0"; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); sql = "DELETE FROM my_ingredients WHERE id = " + id; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); string ColumnNames = "pragma table_info(recipes)"; command = new SQLiteCommand(ColumnNames, DatabaseControl.m_dbConnection); SQLiteDataReader reader = command.ExecuteReader(); sql = "CREATE TABLE recipes_backup (id INTEGER PRIMARY KEY AUTOINCREMENT, " + "title TEXT NOT NULL, " + "description TEXT NOT NULL," + "img TEXT, " + "time INTEGER NOT NULL, "; for (int i = 0; i < 6; ++i) { reader.Read(); } sql += reader["name"].ToString() + " INTEGER NOT NULL"; reader.Read(); do { if (String.Compare(reader["name"].ToString(), "ingredient_" + id) != 0) { sql += ", " + reader["name"].ToString() + " REAL DEFAULT 0.0"; } } while (reader.Read()); sql += ")"; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); command = new SQLiteCommand(ColumnNames, DatabaseControl.m_dbConnection); reader = command.ExecuteReader(); sql = "INSERT INTO recipes_backup SELECT "; reader.Read(); sql += reader["name"].ToString(); reader.Read(); do { if (String.Compare(reader["name"].ToString(), "ingredient_" + id) != 0) { sql += ", " + reader["name"].ToString(); } } while (reader.Read()); sql += " FROM recipes"; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); DatabaseControl.DisonnectDB(); DatabaseControl.ConnectDB(); sql = "DROP TABLE recipes"; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); sql = "ALTER TABLE recipes_backup RENAME TO recipes"; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); DatabaseControl.DisonnectDB(); //fillDataGrid(); dataGridView1.Rows.RemoveAt(dataGridView1.CurrentCell.RowIndex); editedRecipesWindow(true); } }
public ViewRecipeWindow(int id = -1) { InitializeComponent(); foreach (DataGridViewColumn col in dataGridView1.Columns) { col.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter; } this.id = id; DatabaseControl.ConnectDB(); IngredientsList = new Dictionary <string, Ingredient>(); Dictionary <string, double> MyIngredientsList = new Dictionary <string, double>(); string sql = "SELECT * FROM my_ingredients"; SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); SQLiteDataReader reader = command.ExecuteReader(); while (reader.Read()) { MyIngredientsList.Add(reader["id"].ToString(), double.Parse(reader["Count"].ToString())); } sql = "SELECT * FROM ingredients ORDER BY name ASC"; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); reader = command.ExecuteReader(); while (reader.Read()) { IngredientsList.Add(reader["id"].ToString(), new Ingredient(reader["id"].ToString(), reader["name"].ToString(), reader["unit"].ToString())); } sql = "SELECT * FROM recipes WHERE id = " + id; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); reader = command.ExecuteReader(); reader.Read(); TitleBox.Text = reader["title"].ToString(); DescriptionBox.Text = reader["description"].ToString(); TimeLabel.Text = "Czas przygotowania: " + reader["time"] + " min"; DifficultyLevelLabel.Text = "Poziom trudności: " + reader["difficulty_level"]; for (int i = 6; i < reader.FieldCount; ++i) { if (!string.Equals(reader[i].ToString(), "0.0") && !string.Equals(reader[i].ToString(), "0")) { string tempId = ColumnNameToId(reader.GetName(i)); Ingredient tempIngredient = IngredientsList[tempId]; dataGridView1.Rows.Add(tempIngredient.name, reader[i].ToString(), tempIngredient.unit); if (MyIngredientsList.ContainsKey(tempId)) { if (double.Parse(reader[i].ToString()) > MyIngredientsList[tempId]) { dataGridView1.Rows[dataGridView1.RowCount - 1].DefaultCellStyle.BackColor = Color.Red; dataGridView1.Rows[dataGridView1.RowCount - 1].Cells["Count"].Value = reader[i].ToString() + " (-" + (double.Parse(reader[i].ToString()) - MyIngredientsList[tempId]).ToString() + ")"; } } else { dataGridView1.Rows[dataGridView1.RowCount - 1].DefaultCellStyle.BackColor = Color.Red; dataGridView1.Rows[dataGridView1.RowCount - 1].Cells["Count"].Value = reader[i].ToString() + " (-" + reader[i].ToString() + ")"; } } } Bitmap img; try { System.Net.WebRequest request = System.Net.WebRequest.Create(reader["img"].ToString()); request.Timeout = 2000; System.Net.WebResponse response = request.GetResponse(); System.IO.Stream responseStream = response.GetResponseStream(); img = new Bitmap(responseStream); ImgBox.Image = img; } catch (Exception ex) { //img = null; } DatabaseControl.DisonnectDB(); }
private void acceptButton_Click(object sender, EventArgs e) { bool isAllIngredientsFilled = true; DataGridViewRow Row; for (int i = 0; i < dataGridView1.RowCount - 1; ++i) { Row = dataGridView1.Rows[i]; if (Row.Cells[1].Value == null || Row.Cells[2].Value == null) { isAllIngredientsFilled = false; break; } } if (!isAllIngredientsFilled) { MessageBox.Show("UZUPEŁNIJ WSZYSTKIE WARTOŚCI DLA SKŁADNIKÓW!!!"); return; } DatabaseControl.ConnectDB(); if (id != -1) { } else { SQLiteCommand command = new SQLiteCommand("INSERT INTO recipes (title, description, img, time, difficulty_level) VALUES (@title, @description, @img, @time, @difficulty_level)", DatabaseControl.m_dbConnection); command.Parameters.AddWithValue("@title", TitleBox.Text); command.Parameters.AddWithValue("@description", DescriptionBox.Text); command.Parameters.AddWithValue("@img", ImgBox.Text == "www.jakasstrona.pl/obrazek.jpg" ? "" : ImgBox.Text); command.Parameters.AddWithValue("@time", TimeBox.Text); command.Parameters.AddWithValue("@difficulty_level", DifficultyLevelBox.Text); command.ExecuteNonQuery(); Debug.WriteLine("ID: " + DatabaseControl.m_dbConnection.LastInsertRowId); if (dataGridView1.RowCount > 1) { string sql = "UPDATE recipes SET "; Row = dataGridView1.Rows[0]; sql += "ingredient_" + Row.Cells[0].Value.ToString() + " = " + Row.Cells[2].Value.ToString().Replace(',', '.'); for (int i = 1; i < dataGridView1.RowCount - 1; ++i) { Row = dataGridView1.Rows[i]; sql += ", ingredient_" + Row.Cells[0].Value.ToString() + " = " + Row.Cells[2].Value.ToString().Replace(',', '.'); } sql += " WHERE id = " + DatabaseControl.m_dbConnection.LastInsertRowId; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); command.ExecuteNonQuery(); } edited(true); } DatabaseControl.DisonnectDB(); this.Close(); }
private void runAlgorithmButton_Click(object sender, EventArgs e) { Dictionary <string, string> MyIngredientsList = new Dictionary <string, string>(); this.Enabled = false; LoadingWindow loadingWindow = new LoadingWindow(); loadingWindow.Show(); Application.DoEvents(); DatabaseControl.ConnectDB(); string sql = "SELECT * FROM my_ingredients"; SQLiteCommand command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); SQLiteDataReader reader = command.ExecuteReader(); if (reader.HasRows) { while (reader.Read()) { MyIngredientsList.Add(reader["id"].ToString(), reader["count"].ToString()); } DatabaseControl.DisonnectDB(); DatabaseControl.ConnectDB(); string ColumnNames = "pragma table_info(recipes)"; command = new SQLiteCommand(ColumnNames, DatabaseControl.m_dbConnection); reader = command.ExecuteReader(); sql = "SELECT * FROM recipes WHERE "; for (int i = 0; i < 7; ++i) { reader.Read(); } if (reader.HasRows) { string IngredientColumn = reader["name"].ToString(); if (MyIngredientsList.ContainsKey(ColumnNameToId(IngredientColumn))) { sql += IngredientColumn + " <= " + MyIngredientsList[ColumnNameToId(IngredientColumn)].Replace(',', '.'); } else { sql += IngredientColumn + " = 0.0"; } while (reader.Read()) { IngredientColumn = reader["name"].ToString(); if (MyIngredientsList.ContainsKey(ColumnNameToId(IngredientColumn))) { sql += " AND " + IngredientColumn + " <= " + MyIngredientsList[ColumnNameToId(IngredientColumn)].Replace(',', '.'); } else { sql += " AND " + IngredientColumn + " = 0.0"; } } sql += " ORDER BY title ASC"; command = new SQLiteCommand(sql, DatabaseControl.m_dbConnection); reader = command.ExecuteReader(); if (reader.HasRows) { dataGridView1.Rows.Clear(); while (reader.Read()) { Bitmap img; try { System.Net.WebRequest request = System.Net.WebRequest.Create(reader["img"].ToString()); request.Timeout = 500; System.Net.WebResponse response = request.GetResponse(); System.IO.Stream responseStream = response.GetResponseStream(); img = new Bitmap(responseStream); } catch (Exception ex) { img = null; } dataGridView1.Rows.Add(); dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["id"].Value = reader["id"]; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Title"].Value = reader["title"]; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Image"].Value = img; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Time"].Value = reader["time"]; dataGridView1.Rows[dataGridView1.Rows.Count - 1].Cells["Difficulty_level"].Value = reader["difficulty_level"]; } ((DataGridViewImageColumn)dataGridView1.Columns[2]).ImageLayout = DataGridViewImageCellLayout.Stretch; dataGridView1.Refresh(); this.Enabled = true; loadingWindow.Close(); } else { this.Enabled = true; loadingWindow.Close(); dataGridView1.Rows.Clear(); MessageBox.Show("Nie znaleziono żadnych przepisów!", "Brak przepisów", MessageBoxButtons.OK); } DatabaseControl.DisonnectDB(); } else { this.Enabled = true; loadingWindow.Close(); dataGridView1.Rows.Clear(); this.Show(); MessageBox.Show("Nie znaleziono żadnych przepisów!", "Brak przepisów", MessageBoxButtons.OK); } } else { DatabaseControl.DisonnectDB(); DatabaseControl.ConnectDB(); this.Enabled = true; loadingWindow.Close(); dataGridView1.Rows.Clear(); MessageBox.Show("Nie posiadasz żadnych składników!", "Brak składników", MessageBoxButtons.OK); } }