private List<AllergiesRecipes> GetAllAllergiesFromRecipe(int recipeId) { string query = "SELECT a.id, a.recipeId, a.allergyId, g.name FROM allergiesrecipes a RIGHT JOIN allergies g ON a.allergyId = g.id WHERE a.recipeId = @P0 ORDER BY g.name"; //SqlCommand mSqlCommand = new SqlCommand(query, mSqlConnection); //mSqlCommand.Parameters.AddWithValue("P0", recipeId); OleDbCommand command = new OleDbCommand(query, mConnection); command.Parameters.AddWithValue("P0", recipeId); //SqlDataReader mSqlDataReader = null; OleDbDataReader dataReader = null; List<AllergiesRecipes> allergyList = new List<AllergiesRecipes>(); try { //mSqlConnection.Open(); //mSqlDataReader = mSqlCommand.ExecuteReader(); mConnection.Open(); dataReader = command.ExecuteReader(); // Check is the reader has any rows at all before starting to read. //if (mSqlDataReader.HasRows) if (dataReader.HasRows) { // Read advances to the next row. //while (mSqlDataReader.Read()) while (dataReader.Read()) { AllergiesRecipes allergy = new AllergiesRecipes(); allergy.Id = dataReader.GetInt32(dataReader.GetOrdinal("id")); allergy.RecipeId = dataReader.GetInt32(dataReader.GetOrdinal("recipeId")); allergy.AllergyId = dataReader.GetInt32(dataReader.GetOrdinal("allergyId")); allergy.Name = dataReader.GetString(dataReader.GetOrdinal("name")); allergyList.Add(allergy); } } } //catch (SqlException ex) catch (OleDbException ex) { MessageBox.Show(ex.Message, "Error:" + ex.ErrorCode, MessageBoxButtons.OK, MessageBoxIcon.Error); } finally { //mSqlDataReader.Close(); //mSqlConnection.Close(); dataReader.Close(); mConnection.Close(); } return allergyList; }
private void bAddAllergieRecipe_Click(object sender, EventArgs e) { Allergy allergy = (Allergy) cbbAllergy.SelectedItem; AllergiesRecipes allergyRecipe = new AllergiesRecipes(); allergyRecipe.RecipeId = currentRecipeId; allergyRecipe.AllergyId = allergy.Id; bool successfull = true; try { successfull = mDatabaseConnection.ExecuteNonReturnQuery(allergyRecipe.GetInsertQuery()); } catch (SqlException ex) { successfull = false; MessageBox.Show(ex.Message, "Error:" + ex.Number, MessageBoxButtons.OK, MessageBoxIcon.Error); } if (successfull) { ReloadAllergiesRecipes(); } }