public static bool RemoveRecipe(RecipeModel recipe) { SqlConnection connection = RecipeListDB.GetConnection(); string deleteStatement = "DELETE FROM Recipe " + "WHERE RecipeID = @RecipeID"; SqlCommand deleteCommand = new SqlCommand(deleteStatement, connection); deleteCommand.Parameters.AddWithValue("RecipeID", recipe.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 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 int AddRecipe(RecipeModel recipe) { SqlConnection connection = RecipeListDB.GetConnection(); string insertStatement = "INSERT INTO Recipe " + "(RecipeName, RecipeInstructions) " + "VALUES(@Name, @Instructions)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("@Name", recipe.Name); insertCommand.Parameters.AddWithValue("@Instructions", recipe.Instructions); try { connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Recipe') " + "FROM Recipe"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); int recipeID = Convert.ToInt32(selectCommand.ExecuteScalar().ToString()); return(recipeID); } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static int AddIngredient(IngredientModel ingredient) { SqlConnection connection = RecipeListDB.GetConnection(); string insertStatement = "INSERT INTO Ingredient " + "(IngredientName) " + "VALUES(@Name)"; SqlCommand insertCommand = new SqlCommand(insertStatement, connection); insertCommand.Parameters.AddWithValue("Name", ingredient.Name); try { connection.Open(); insertCommand.ExecuteNonQuery(); string selectStatement = "SELECT IDENT_CURRENT('Ingredient') " + "FROM Ingredient"; 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 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 List <RecipeModel> GetRecipeList() { var recipeList = new List <RecipeModel>(); SqlConnection connection = RecipeListDB.GetConnection(); string selectStatement = "SELECT RecipeID, RecipeName, RecipeInstructions " + "FROM Recipe ORDER BY RecipeName ASC"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { RecipeModel r = new RecipeModel(); r.Id = Convert.ToInt32(reader["RecipeID"].ToString()); r.Name = reader["RecipeName"].ToString(); r.Instructions = reader["RecipeInstructions"].ToString(); recipeList.Add(r); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(recipeList); }
public static IngredientModel GetIngredient(int ingredientId) { SqlConnection connection = RecipeListDB.GetConnection(); string selectStatement = "SELECT IngredientID, IngredientName " + "FROM Ingredient " + "WHERE IngredientID = @IngredientID"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); selectCommand.Parameters.AddWithValue("IngredientID", ingredientId); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); if (reader.Read()) { var ingredient = new IngredientModel(); ingredient.Id = Convert.ToInt32(reader["IngredientID"].ToString()); ingredient.Name = reader["IngredientName"].ToString(); return(ingredient); } else { return(null); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
public static List <IngredientModel> GetIngredientList() { var ingredients = new List <IngredientModel>(); SqlConnection connection = RecipeListDB.GetConnection(); string selectStatement = "SELECT IngredientID, IngredientName " + "FROM Ingredient ORDER BY IngredientName ASC"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { IngredientModel i = new IngredientModel(); i.Id = Convert.ToInt32(reader["IngredientID"].ToString()); i.Name = reader["IngredientName"].ToString(); ingredients.Add(i); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(ingredients); }
//Updates the instructions of a recipe from the given recipeID. //Requires the "id" and "Instructions" field in the RecipeModel class. public static bool UpdateRecipeInstructions(RecipeModel recipe) { SqlConnection connection = RecipeListDB.GetConnection(); String updateStatement = "UPDATE Recipe " + "SET RecipeInstructions= @Instructions " + "WHERE RecipeID = @RecipeID"; SqlCommand updateCommand = new SqlCommand(updateStatement, connection); updateCommand.Parameters.AddWithValue("Instructions", recipe.Instructions); updateCommand.Parameters.AddWithValue("RecipeID", recipe.Id); try { connection.Open(); int rows = updateCommand.ExecuteNonQuery(); if (rows >= 1) { return(true); } else { return(false); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } }
//Searches available recipes that have include one or more of the ingredients in the list. The exclusive parameter // to tell the search to exclude the meals that contain those ingredients like if you are allergic to eggs or something. public static List <RecipeModel> SearchAvailableRecipe(List <IngredientModel> ingredients, bool exclusive) { var recipeList = new List <RecipeModel>(); //if there isn't any ingredients then just return all of the recipes if (ingredients == null || ingredients.Count == 0) { return(GetRecipeList()); } SqlConnection connection = RecipeListDB.GetConnection(); string selectStatement = null; String likeString = "LIKE "; String boolString = "OR "; if (exclusive) { selectStatement = "SELECT r.RecipeID, r.RecipeName, r.RecipeInstructions " + "FROM Recipe r " + "WHERE r.RecipeID NOT IN " + "(SELECT Distinct link.RecipeID " + "FROM RecipeIngredientLink link " + "JOIN Ingredient i " + "ON link.IngredientID = i.IngredientID " + "WHERE "; } else { selectStatement = "SELECT DISTINCT r.RecipeID, r.RecipeName, r.RecipeInstructions " + "FROM Recipe r " + "JOIN RecipeIngredientLink link " + "ON r.RecipeID = link.RecipeID " + "JOIN Ingredient i " + "ON link.IngredientID = i.IngredientID " + "WHERE "; } for (int i = 0; i < ingredients.Count; i++) { selectStatement += "i.IngredientName " + likeString + "@Parameter" + i + " "; if (i < ingredients.Count - 1) { selectStatement += boolString; } } if (exclusive) { selectStatement += ")"; } selectStatement += " "; selectStatement += "ORDER BY r.RecipeName ASC"; SqlCommand selectCommand = new SqlCommand(selectStatement, connection); for (int i = 0; i < ingredients.Count; i++) { selectCommand.Parameters.AddWithValue("Parameter" + i, "%" + ingredients[i].Name + "%"); } try { connection.Open(); SqlDataReader reader = selectCommand.ExecuteReader(); while (reader.Read()) { RecipeModel r = new RecipeModel(); r.Id = Convert.ToInt32(reader["RecipeID"].ToString()); r.Name = reader["RecipeName"].ToString(); r.Instructions = reader["RecipeInstructions"].ToString(); recipeList.Add(r); } } catch (SqlException ex) { throw ex; } finally { connection.Close(); } return(recipeList); }