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);
}
