public static int AddIngredientToRecipe(RecipeIngredLinkModel model)
{
SqlConnection connection = RecipeListDB.GetConnection();
string insertStatement = "INSERT INTO RecipeIngredientLink " +
"(RecipeID, IngredientID) " +
"VALUES(@RecipeID, @IngredientID)";
SqlCommand insertCommand = new SqlCommand(insertStatement, connection);
insertCommand.Parameters.AddWithValue("RecipeID", model.RecipeId);
insertCommand.Parameters.AddWithValue("IngredientID", model.IngredientId);
try
{
connection.Open();
insertCommand.ExecuteNonQuery();
string selectStatement = "SELECT IDENT_CURRENT('RecipeIngredientLink') "
+ "FROM RecipeIngredientLink";
SqlCommand selectCommand =
new SqlCommand(selectStatement, connection);
int ingredientID = Convert.ToInt32(selectCommand.ExecuteScalar().ToString());
return(ingredientID);
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static bool RemoveIngredientFromRecipe(RecipeIngredLinkModel model)
{
SqlConnection connection = RecipeListDB.GetConnection();
string deleteStatement = "DELETE FROM RecipeIngredientLink " +
"WHERE RecIngredID = @RecIngredID";
SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection);
deleteCommand.Parameters.AddWithValue("RecIngredID", model.Id);
try
{
connection.Open();
int rows = deleteCommand.ExecuteNonQuery();
if (rows >= 1)
{
return(true);
}
else
{
return(false);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
}
public static List <RecipeIngredLinkModel> GetIngredientsForRecipe(int recipeId)
{
var ingredients = new List <RecipeIngredLinkModel>();
SqlConnection connection = RecipeListDB.GetConnection();
string selectStatement =
"SELECT i.IngredientID AS 'IngredientID', i.IngredientName, ril.RecipeID AS 'RecipeID', ril.RecIngredID AS 'RecIngredID' "
+ "FROM RecipeIngredientLink ril " +
"JOIN Ingredient i " +
"ON ril.IngredientID = i.IngredientID " +
"WHERE ril.RecipeID = @RecipeID " +
"ORDER BY i.IngredientName ASC";
SqlCommand selectCommand = new SqlCommand(selectStatement, connection);
selectCommand.Parameters.AddWithValue("RecipeID", recipeId);
try
{
connection.Open();
SqlDataReader reader = selectCommand.ExecuteReader();
while (reader.Read())
{
var i = new RecipeIngredLinkModel();
i.Id = Convert.ToInt32(reader["RecIngredID"].ToString());
i.IngredientId = Convert.ToInt32(reader["IngredientID"].ToString());
i.RecipeId = Convert.ToInt32(reader["RecipeID"].ToString());
ingredients.Add(i);
}
}
catch (SqlException ex)
{
throw ex;
}
finally
{
connection.Close();
}
return(ingredients);
}
public static void ui_deleteIngredientsFromCurrentRecipe(int id, int selectedRecipe, int selectedIngredient, ref ObservableCollection <IngredientModel> ingredientList)
{
//int id = Convert.ToInt32(listboxIngredients.SelectedValue);
//get the ingredient
IngredientModel ingredientToDelete = IngredientTableDB.GetIngredient(id);
//delete the meal link
List <RecipeIngredLinkModel> ingred = RecipeIngredLinkTableDB.GetIngredientsForRecipe(selectedRecipe);
RecipeIngredLinkModel linkToDelete = ingred.Find(x => x.IngredientId.Equals(id));
RecipeIngredLinkTableDB.RemoveIngredientFromRecipe(linkToDelete);
//delete the ingredient
IngredientTableDB.RemoveIngredient(ingredientToDelete);
//remove the deleted ingredient from the listbox
ingredientList.RemoveAt(selectedIngredient);
}
private void btnAccept_Click(object sender, RoutedEventArgs e)
{
IngredientModel ingredient = new IngredientModel();
ingredient.Name = tbName.Text.ToString();
int id = IngredientTableDB.AddIngredient(ingredient);
ingredient.Id = id;
ob_ingredientName.Add(ingredient);
//create the link between ingredient and recipe
RecipeIngredLinkModel link = new RecipeIngredLinkModel();
link.IngredientId = id;
link.RecipeId = recipeId;
RecipeIngredLinkTableDB.AddIngredientToRecipe(link);
this.Close();
}
