public void Update(string newName, string newInstruction, int newRating)
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("UPDATE recipes SET name = @NewName, instruction = @NewInstruction, rating = @NewRating WHERE recipes.id = @TargetId;", conn);
cmd.Parameters.Add(new SqlParameter("@NewName", newName));
cmd.Parameters.Add(new SqlParameter("@NewInstruction", newInstruction));
cmd.Parameters.Add(new SqlParameter("@NewRating", newRating.ToString()));
cmd.Parameters.Add(new SqlParameter("@TargetId", this.GetId()));
cmd.ExecuteNonQuery();
this.SetName(newName);
this.SetInstruction(newInstruction);
this.SetRating(newRating);
DB.CloseSqlConnection(conn);
}
public void Save()
{
int potentialId = this.IsNewCategory();
if (potentialId == -1)
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO categories (name) OUTPUT INSERTED.id VALUES (@CategoryName) ;", conn);
cmd.Parameters.Add(new SqlParameter("@CategoryName", this.GetName()));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
potentialId = rdr.GetInt32(0);
}
DB.CloseSqlConnection(conn, rdr);
}
this.SetId(potentialId);
}
public static List <Category> GetAll()
{
List <Category> allCategories = new List <Category> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM categories;", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int categoryId = rdr.GetInt32(0);
string categoryName = rdr.GetString(1);
Category newCategory = new Category(categoryName, categoryId);
allCategories.Add(newCategory);
}
DB.CloseSqlConnection(conn, rdr);
return(allCategories);
}
public int IsNewCategory()
{
// Checks to see if an ingredient exists in the database already. If it does, returns the id. Otherwise, returns -1
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT id FROM categories WHERE name = @CategoryName", conn);
cmd.Parameters.Add(new SqlParameter("@CategoryName", this.GetName()));
SqlDataReader rdr = cmd.ExecuteReader();
int foundId = -1;
while (rdr.Read())
{
foundId = rdr.GetInt32(0);
}
DB.CloseSqlConnection(conn, rdr);
return(foundId);
}
public static List <Ingredient> GetAll()
{
List <Ingredient> allIngredients = new List <Ingredient> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM ingredients;", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int ingredientId = rdr.GetInt32(0);
string ingredientName = rdr.GetString(1);
Ingredient newIngredient = new Ingredient(ingredientName, ingredientId);
allIngredients.Add(newIngredient);
}
DB.CloseSqlConnection(conn, rdr);
return(allIngredients);
}
public static List <Category> SearchName(string categoryName)
{
List <Category> foundCategories = new List <Category> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM categories WHERE name LIKE @CategoryName", conn);
cmd.Parameters.Add(new SqlParameter("@CategoryName", "%" + categoryName + "%"));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int categoryId = rdr.GetInt32(0);
string newName = rdr.GetString(1);
Category foundCategory = new Category(newName, categoryId);
foundCategories.Add(foundCategory);
}
DB.CloseSqlConnection(conn, rdr);
return(foundCategories);
}
public List <Category> GetCategory()
{
List <Category> allCategories = new List <Category> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT categories.* FROM ingredients JOIN ingredients_categories ON (ingredients.id = ingredients_categories.ingredient_id) JOIN categories ON (categories.id = ingredients_categories.category_id) WHERE ingredients.id = @IngredientId;", conn);
cmd.Parameters.Add(new SqlParameter("@IngredientId", this.GetId().ToString()));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int Id = rdr.GetInt32(0);
string categoryName = rdr.GetString(1);
Category newCategory = new Category(categoryName, Id);
allCategories.Add(newCategory);
}
DB.CloseSqlConnection(conn, rdr);
return(allCategories);
}
public static List <Ingredient> SearchName(string ingredientName)
{
List <Ingredient> foundIngredients = new List <Ingredient> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM ingredients WHERE name LIKE @IngredientName", conn);
cmd.Parameters.Add(new SqlParameter("@IngredientName", "%" + ingredientName + "%"));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int ingredientId = rdr.GetInt32(0);
string newName = rdr.GetString(1);
Ingredient foundIngredient = new Ingredient(newName, ingredientId);
foundIngredients.Add(foundIngredient);
}
DB.CloseSqlConnection(conn, rdr);
return(foundIngredients);
}
public static List <Recipe> GetAll()
{
List <Recipe> allRecipes = new List <Recipe> {
};
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM recipes ORDER BY rating DESC;", conn);
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
int recipeId = rdr.GetInt32(0);
string recipeName = rdr.GetString(1);
string instruction = rdr.GetString(2);
int recipeRating = rdr.GetInt32(3);
Recipe newRecipe = new Recipe(recipeName, instruction, recipeRating, recipeId);
allRecipes.Add(newRecipe);
}
DB.CloseSqlConnection(conn, rdr);
return(allRecipes);
}
public void Save()
{
int potentialId = this.IsNewRecipe();
if (potentialId == -1)
{
SqlConnection conn = DB.Connection();
conn.Open();
SqlCommand cmd = new SqlCommand("INSERT INTO recipes (name, instruction, rating) OUTPUT INSERTED.id VALUES (@RecipeName, @Instruction, @Rating) ;", conn);
cmd.Parameters.Add(new SqlParameter("@RecipeName", this.GetName()));
cmd.Parameters.Add(new SqlParameter("@Instruction", this.GetInstruction()));
cmd.Parameters.Add(new SqlParameter("@Rating", this.GetRating()));
SqlDataReader rdr = cmd.ExecuteReader();
while (rdr.Read())
{
potentialId = rdr.GetInt32(0);
}
DB.CloseSqlConnection(conn, rdr);
}
this.SetId(potentialId);
}
