public bool AddRecipeIngredients(int recipeID, List <RecipeIngredient> recipeIngredients)
{
try
{
string sql = "Insert RecipeIngredient values(@recipe_id, @ingre_id, @amount_ingre, @note)";
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
SqlTransaction transaction = cnn.BeginTransaction();
using (cmd = new SqlCommand(sql, cnn))
{
cmd.Transaction = transaction;
foreach (RecipeIngredient recipeIngredient in recipeIngredients)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@recipe_id", recipeID);
cmd.Parameters.AddWithValue("@ingre_id", recipeIngredient.IngredientID);
cmd.Parameters.AddWithValue("@amount_ingre", recipeIngredient.AmountIngredient);
cmd.Parameters.AddWithValue("@note", recipeIngredient.Note);
cmd.ExecuteNonQuery();
}
transaction.Commit();
return(true);
}
}
}
catch (SqlException se)
{
throw new Exception(se.Message);
}
}
public bool DeleteAllRecipeIngredients(int recipeID)
{
bool check = false;
try
{
string sql = "DELETE FROM dbo.RecipeIngredient WHERE recipe_id = @id";
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
using (cmd = new SqlCommand(sql, cnn))
{
cmd.Parameters.AddWithValue("@id", recipeID);
check = cmd.ExecuteNonQuery() > 0;
}
}
}
catch (SqlException se)
{
throw se;
}
return(check);
}
public string GetIngredientUnitByName(string name)
{
string unit = null;
string sql = "Select unit From Ingredient Where name = @name";
try
{
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
using (cmd = new SqlCommand(sql, cnn))
{
cmd.Parameters.AddWithValue("@name", name);
using (reader = cmd.ExecuteReader())
{
if (reader.Read())
{
unit = reader["unit"].ToString();
}
}
}
}
}
catch (SqlException se)
{
throw new Exception(se.Message);
}
return(unit);
}
public string GetIngredientNameByID(int id)
{
string name = null;
string sql = "Select name From Ingredient Where ingre_id = @id";
try
{
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
using (cmd = new SqlCommand(sql, cnn))
{
cmd.Parameters.AddWithValue("@id", id);
using (reader = cmd.ExecuteReader())
{
if (reader.Read())
{
name = (string)reader["name"];
}
}
}
}
}
catch (SqlException se)
{
throw new Exception(se.Message);
}
return(name);
}
public int GetIngredientIDByName(string name)
{
int id = -1;
string sql = "Select ingre_id From Ingredient Where name = @name";
try
{
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
using (cmd = new SqlCommand(sql, cnn))
{
cmd.Parameters.AddWithValue("@name", name);
using (reader = cmd.ExecuteReader())
{
if (reader.Read())
{
id = int.Parse(reader["ingre_id"].ToString());
}
}
}
}
}
catch (SqlException se)
{
throw new Exception(se.Message);
}
return(id);
}
public bool UpdateRecipe(Recipe recipe)
{
bool check = false;
string sql = @"UPDATE dbo.Recipe SET title = @title, description = @desc,
image = @image WHERE recipe_id = @id";
try
{
using (conn = MyConnection.GetMyConnection())
{
conn.Open();
using (cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@title", recipe.Title);
cmd.Parameters.AddWithValue("@desc", recipe.Description);
cmd.Parameters.AddWithValue("@image", recipe.Image);
cmd.Parameters.AddWithValue("@id", recipe.Id);
check = cmd.ExecuteNonQuery() > 0;
}
}
}
catch (SqlException se)
{
throw se;
}
return(check);
}
public bool DeleteRecipe(Recipe recipe)
{
bool check = false;
string sql = @"UPDATE dbo.Recipe SET status = 'deleted' WHERE recipe_id = @id";
try
{
using (conn = MyConnection.GetMyConnection())
{
conn.Open();
using (cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@id", recipe.Id);
check = cmd.ExecuteNonQuery() > 0;
}
}
}
catch (SqlException se)
{
throw se;
}
return(check);
}
public bool AddUser(User user)
{
bool check = false;
string SQL = "Insert [User] values(@email, @username, @password, @role, @image, @status)";
try
{
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
using (SqlCommand cmd = new SqlCommand(SQL, cnn))
{
cmd.Parameters.AddWithValue("@email", user.Email);
cmd.Parameters.AddWithValue("@username", user.Username);
cmd.Parameters.AddWithValue("@password", user.Password);
cmd.Parameters.AddWithValue("@role", user.Role);
cmd.Parameters.AddWithValue("@image", user.Image);
cmd.Parameters.AddWithValue("@status", user.Status);
check = cmd.ExecuteNonQuery() > 0;
}
}
} catch (SqlException se)
{
throw new Exception(se.Message);
}
return(check);
}
public int AddRecipe(Recipe recipe)
{
try
{
string sql = "Insert Recipe output INSERTED.recipe_id values(@email, @title, @description, @status, @date, @image, @category)";
using (conn = MyConnection.GetMyConnection())
{
conn.Open();
using (cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@email", recipe.Email);
cmd.Parameters.AddWithValue("@title", recipe.Title);
cmd.Parameters.AddWithValue("@description", recipe.Description);
cmd.Parameters.AddWithValue("@status", recipe.Status);
cmd.Parameters.AddWithValue("@date", recipe.Date);
cmd.Parameters.AddWithValue("@image", recipe.Image);
cmd.Parameters.AddWithValue("@category", "");
//return cmd.ExecuteNonQuery() > 0;
return((int)cmd.ExecuteScalar());
}
}
}
catch (SqlException se)
{
throw new Exception(se.Message);
}
}
public bool UpdateUser(User user)
{
bool check = false;
string sql = "Update [User] set username = @username, password = @password where email = @email";
try
{
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
using (cmd = new SqlCommand(sql, cnn))
{
cmd.Parameters.AddWithValue("@email", user.Email);
cmd.Parameters.AddWithValue("@username", user.Username);
cmd.Parameters.AddWithValue("@password", user.Password);
check = cmd.ExecuteNonQuery() > 0;
}
}
}
catch (SqlException se)
{
throw new Exception(se.Message);
}
return(check);
}
public bool AddRecipeSteps(int recipeID, List <RecipeStep> steps)
{
try
{
string sql = "Insert RecipeStep values(@recipe_id, @description, @image)";
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
SqlTransaction transaction = cnn.BeginTransaction();
using (cmd = new SqlCommand(sql, cnn))
{
cmd.Transaction = transaction;
foreach (RecipeStep step in steps)
{
cmd.Parameters.Clear();
cmd.Parameters.AddWithValue("@recipe_id", recipeID);
cmd.Parameters.AddWithValue("@description", step.Description);
cmd.Parameters.AddWithValue("@image", step.Image);
cmd.ExecuteNonQuery();
}
transaction.Commit();
return(true);
}
}
}
catch (SqlException se)
{
throw new Exception(se.Message);
}
}
public SinhVienDTO GetInforSV(int maSV)
{
SinhVienDTO dto = null;
try
{
con = MyConnection.GetMyConnection();
con.Open();
string sql = "Select TEN, MAKH, NGAYSINH, GIOITINH from SVIEN where MASV = @MaSV";
command = new SqlCommand(sql, con);
SqlParameter param = new SqlParameter();
command.Parameters.AddWithValue("@MaSV", maSV);
dataReader = command.ExecuteReader();
if (dataReader.Read())
{
dto = new SinhVienDTO(maSV, dataReader.GetValue(0).ToString(), dataReader.GetValue(1).ToString(),
dataReader.GetValue(3).ToString(), DateTime.Parse(dataReader.GetValue(2).ToString()));
}
}
finally
{
CloseConnection();
}
return(dto);
}
public List <Recipe> GetRecipesByIngredients(List <string> ingredients)
{
List <Recipe> list = null;
try
{
using (conn = MyConnection.GetMyConnection())
{
conn.Open();
using (cmd = new SqlCommand())
{
cmd.Connection = conn;
string[] parameters = new string[ingredients.Count];
int i = 0;
foreach (string ingredient in ingredients)
{
parameters[i] = string.Format("@Ingredient{0}", i);
cmd.Parameters.AddWithValue(parameters[i], ingredients[i]);
++i;
}
string sql = "SELECT r.recipe_id, r.title, r.image, r.date, COUNT(r.recipe_id) AS search_rate\n" +
"FROM dbo.Recipe r JOIN dbo.RecipeIngredient ri ON ri.recipe_id = r.recipe_id\n" +
"JOIN dbo.Ingredient i ON i.ingre_id = ri.ingre_id\n" +
"WHERE i.name IN ({0})\n" +
"GROUP BY r.recipe_id, r.title, r.image, r.date\n" +
"ORDER BY search_rate DESC\n";
cmd.CommandText = string.Format(sql, string.Join(", ", parameters));
using (reader = cmd.ExecuteReader())
{
list = new List <Recipe>();
while (reader.Read())
{
int id = (int)reader["recipe_id"];
string title = (string)reader["title"];
DateTime date = DateTime.Parse(reader["date"].ToString());
string image = reader["image"] as string;
Recipe recipe = new Recipe(id, title, date, image);
list.Add(recipe);
}
}
}
}
}
catch (SqlException se)
{
throw se;
}
return(list);
}
public List <Ingredient> GetAllIngredients()
{
List <Ingredient> ingredients = null;
Ingredient ingredient;
int id;
string name, description, status;
string image = null, unit = null;
string sql = "Select ingre_id, name, description, unit, image, status From Ingredient ORDER BY name ASC";
try
{
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
using (cmd = new SqlCommand(sql, cnn))
{
using (reader = cmd.ExecuteReader())
{
ingredients = new List <Ingredient>();
while (reader.Read())
{
id = (int)reader["ingre_id"];
name = (string)reader["name"];
description = (string)reader["description"];
if (!reader.IsDBNull(reader.GetOrdinal("unit")))
{
unit = (string)reader["unit"];
}
if (!reader.IsDBNull(reader.GetOrdinal("image")))
{
image = (string)reader["image"];
}
status = (string)reader["status"];
ingredient = new Ingredient(id, name, description, unit, image, status);
ingredients.Add(ingredient);
}
}
}
}
}
catch (SqlException se)
{
throw new Exception(se.Message);
}
return(ingredients);
}
public List <Recipe> GetAllRecipes()
{
List <Recipe> list = null;
string sql = "SELECT recipe_id, title, date, image, email, description " +
"FROM Recipe WHERE status NOT LIKE 'deleted'";
try
{
using (conn = MyConnection.GetMyConnection())
{
conn.Open();
using (cmd = new SqlCommand(sql, conn))
{
using (reader = cmd.ExecuteReader())
{
Recipe recipe;
while (reader.Read())
{
int id = (int)reader["recipe_id"];
string title = reader["title"] as string;
DateTime date = reader.GetDateTime(2);
string image = reader["image"] as string;
string email = reader["email"] as string;
string desc = reader["description"] as string;
recipe = new Recipe(id, title, date, image);
recipe.Email = email;
recipe.Description = desc;
if (list == null)
{
list = new List <Recipe>();
}
list.Add(recipe);
}
}
}
}
}
catch (SqlException se)
{
throw new Exception(se.Message);
}
return(list);
}
public static List <RecipeStep> GetRecipeStepsByRecipe(Recipe recipe)
{
List <RecipeStep> steps = null;
try
{
string sql = @"SELECT step_id, [description], image
FROM dbo.RecipeStep
WHERE recipe_id = @id";
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
using (cmd = new SqlCommand(sql, cnn))
{
cmd.Parameters.AddWithValue("@id", recipe.Id);
using (reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int id = (int)reader["step_id"];
string desc = reader["description"] as string;
string image = reader["image"] as string;
RecipeStep step = new RecipeStep(id, desc, image);
step.RecipeID = recipe.Id;
if (steps == null)
{
steps = new List <RecipeStep>();
}
steps.Add(step);
}
}
}
}
}
catch (Exception e)
{
throw e;
}
return(steps);
}
public List <RecipeIngredient> GetRecipeIngredientsByRecipe(Recipe recipe)
{
List <RecipeIngredient> recipeIngredients = new List <RecipeIngredient>();
try
{
string sql = @" SELECT Ingredient.ingre_id, amount_ingre, note
FROM dbo.Recipe, dbo.RecipeIngredient, dbo.Ingredient
WHERE Recipe.recipe_id = RecipeIngredient.recipe_id
AND Ingredient.ingre_id = RecipeIngredient.ingre_id
AND Recipe.recipe_id = @id";
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
using (cmd = new SqlCommand(sql, cnn))
{
cmd.Parameters.AddWithValue("@id", recipe.Id);
using (reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int ingreId = (int)reader["ingre_id"];
string note = reader["note"] as string;
string amount = reader["amount_ingre"] as string;
RecipeIngredient recipeIngredient = new RecipeIngredient(ingreId, amount, note);
recipeIngredient.RecipeID = recipe.Id;
recipeIngredients.Add(recipeIngredient);
}
}
}
}
}
catch (Exception e)
{
throw e;
}
return(recipeIngredients);
}
public List <Ingredient> GetAllIngredientsByRecipe(Recipe recipe)
{
List <Ingredient> ingredients = new List <Ingredient>();
try
{
string sql = @"SELECT Ingredient.ingre_id, [name], Ingredient.description, amount_ingre
FROM dbo.Recipe, dbo.RecipeIngredient, dbo.Ingredient
WHERE Recipe.recipe_id = RecipeIngredient.recipe_id
AND Ingredient.ingre_id = RecipeIngredient.ingre_id
AND Recipe.recipe_id = @id";
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
using (cmd = new SqlCommand(sql, cnn))
{
cmd.Parameters.AddWithValue("@id", recipe.Id);
using (reader = cmd.ExecuteReader())
{
while (reader.Read())
{
int ingreId = (int)reader["ingre_id"];
string name = reader["name"] as string;
string description = reader["description"] as string;
string amount = reader["amount_ingre"] as string;
Ingredient ingredient = new Ingredient(ingreId, name, description, amount);
ingredients.Add(ingredient);
}
}
}
}
}
catch (Exception e)
{
throw e;
}
return(ingredients);
}
public List <KetQuaDTO> GetScores(int maSV)
{
List <KetQuaDTO> list = null;
try
{
con = MyConnection.GetMyConnection();
con.Open();
string sql = "select kq.DIEM, a.TENMH, a.MAMH "
+ "from KQUA kq, (select hp.MAHP, a.TENMH, a.MAMH"
+ " from HPHAN hp, (select TENMH, MAMH from MHOC where MAKH = (select MAKH from SVIEN where MASV = @MASV)) a" +
" where hp.MAMH = a.MAMH ) a"
+ " where kq.MASV = @MASV and kq.MAHP = a.MAHP";
// câu query mẫu
// select kq.DIEM, a.TENMH, a.MAMH
// from KQUA kq, (select hp.MAHP, a.TENMH, a.MAMH
// from HPHAN hp, (select TENMH, MAMH from MHOC where MAKH = (select MAKH from SVIEN where MASV = 2)) a
// where hp.MAMH = a.MAMH ) a
//where kq.MASV = 2 and kq.MAHP = a.MAHP
command = new SqlCommand(sql, con);
SqlParameter param = new SqlParameter();
command.Parameters.AddWithValue("@MaSV", maSV);
dataReader = command.ExecuteReader();
while (dataReader.Read())
{
int diem = int.Parse(dataReader.GetValue(0).ToString());
string tenMH = dataReader.GetValue(1).ToString();
string maMH = dataReader.GetValue(0).ToString();
if (list == null)
{
list = new List <KetQuaDTO>();
}
list.Add(new KetQuaDTO(diem, maMH, tenMH));
}
}
finally
{
CloseConnection();
}
return(list);
}
public List <Recipe> GetRecipesBySearch(string search)
{
List <Recipe> list = null;
string sql = "SELECT recipe_id, title, date, image, email " +
"FROM Recipe WHERE status NOT LIKE 'deleted' AND title LIKE @search";
try
{
using (conn = MyConnection.GetMyConnection())
{
conn.Open();
using (cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@search", "%" + search + "%");
using (reader = cmd.ExecuteReader())
{
list = new List <Recipe>();
while (reader.Read())
{
int id = (int)reader["recipe_id"];
string title = reader["title"] as string;
DateTime date = reader.GetDateTime(2);
string image = reader["image"] as string;
string email = reader["email"] as string;
Recipe recipe = new Recipe(id, title, date, image);
recipe.Email = email;
list.Add(recipe);
}
}
}
}
}
catch (SqlException se)
{
throw new Exception(se.Message);
}
return(list);
}
public int getFirstMaSV()
{
int number = 0;
try
{
con = MyConnection.GetMyConnection();
con.Open();
string sql = "select min(MASV) as firstMaSV from SVIEN";
command = new SqlCommand(sql, con);
dataReader = command.ExecuteReader();
if (dataReader.Read())
{
number = int.Parse(dataReader.GetValue(0).ToString());
}
}
finally
{
CloseConnection();
}
return(number);
}
public User FindUserByEmail(string email)
{
User dto = null;
string sql = "SELECT username, password, role, image, status FROM [User] WHERE email = @email";
try
{
using (cnn = MyConnection.GetMyConnection())
{
cnn.Open();
using (cmd = new SqlCommand(sql, cnn))
{
cmd = new SqlCommand(sql, cnn);
cmd.Parameters.AddWithValue("@email", email);
using (SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.SingleResult))
{
if (reader.HasRows)
{
if (reader.Read())
{
string username = reader["username"] as string;
string password = reader["password"] as string;
string role = reader["role"] as string;
string image = reader["image"] as string;
string status = reader["status"] as string;
dto = new User(email, username, password, role, image, status);
}
}
}
}
}
}
catch (SqlException se)
{
throw new Exception(se.Message);
}
return(dto);
}
public bool UpdateImagePath(int recipeID, string imageName)
{
string imagePath = @"img\" + recipeID + @"\" + imageName;
string sql = "UPDATE dbo.Recipe SET image = @imagePath WHERE recipe_id = @id";
try
{
using (conn = MyConnection.GetMyConnection())
{
conn.Open();
using (cmd = new SqlCommand(sql, conn))
{
cmd.Parameters.AddWithValue("@id", recipeID);
cmd.Parameters.AddWithValue("@imagePath", imagePath);
return(cmd.ExecuteNonQuery() > 0);
}
}
}
catch (SqlException se)
{
throw se;
}
}
public List <string> GetAllStudentID()
{
List <string> list = null;
try
{
con = MyConnection.GetMyConnection();
con.Open();
string sql = "Select MASV from SVIEN";
command = new SqlCommand(sql, con);
dataReader = command.ExecuteReader();
list = new List <string>();
while (dataReader.Read())
{
list.Add(dataReader.GetValue(0).ToString().Trim());
}
}
finally
{
CloseConnection();
}
return(list);
}