/// <summary> /// Delete DailyMeals from Database /// </summary> /// <param name="dailyMeals">Daily Meals</param> public void DailyMealsDataDelete(DailyMeal dailyMeals) { connection.Open(); string query = "DELETE FROM DailyMeals WHERE Id = @USERID"; SqlCommand cmd = new SqlCommand(query, connection); cmd.Parameters.Add("@USERID", SqlDbType.Int); cmd.Parameters["@USERID"].Value = dailyMeals.Id; cmd.ExecuteNonQuery(); connection.Close(); }
/// <summary> /// Add new DailyMeals to Database /// </summary> /// <param name="dailyMeals">Daily Meals</param> /// <param name="user">User</param> public void DailyMealsDataAdd(DailyMeal dailyMeals, User user) { connection.Open(); string query = "INSERT INTO DailyMeals(Meal, Date, UserId) VALUES (@MEAL, @DATE, @USERID)"; SqlCommand cmd = new SqlCommand(query, connection); cmd.Parameters.Add("@MEAL", SqlDbType.Int); cmd.Parameters.Add("@DATE", SqlDbType.DateTime); cmd.Parameters.Add("@USERID", SqlDbType.Int); cmd.Parameters["@MEAL"].Value = dailyMeals.Meal.Id; cmd.Parameters["@DATE"].Value = dailyMeals.Date; cmd.Parameters["@USERID"].Value = user.Id; cmd.ExecuteNonQuery(); connection.Close(); }
/// <summary> /// Collect all of Daily Meals form Database, which are connected to User Id, at specified Date /// </summary> /// <param name="user">User</param> /// <param name="date">Date</param> /// <returns></returns> public List <DailyMeal> DailyMealsDataCollectionDate(User user, DateTime date) { List <DailyMeal> dailyMeals = new List <DailyMeal>(); connection.Open(); string query = "SELECT * FROM DailyMeals WHERE UserId = @USERID and Date = @DATE"; SqlCommand cmd = new SqlCommand(query, connection); cmd.Parameters.Add("@USERID", SqlDbType.Int); cmd.Parameters.Add("@DATE", SqlDbType.DateTime); cmd.Parameters["@USERID"].Value = user.Id; cmd.Parameters["@DATE"].Value = date.Date; SqlDataReader reader = cmd.ExecuteReader(); while (reader.Read()) { Meal meal = new Meal(1, "a", "b", 1, 1, 1, 1); int mealId = reader.GetInt32(1); string query2 = "SELECT * FROM Meal WHERE Id = @ID"; SqlCommand cmd2 = new SqlCommand(query2, connection); cmd2.Parameters.Add("@ID", SqlDbType.Int); cmd2.Parameters["@ID"].Value = mealId; SqlDataReader reader2 = cmd2.ExecuteReader(); int a = 0; while (reader2.Read() && a == 0) { Meal newMeal = new Meal((uint)reader2.GetInt32(0), reader2.GetString(1), reader2.GetString(2), reader2.GetInt32(3), reader2.GetInt32(4), reader2.GetInt32(5), reader2.GetInt32(6)); a++; meal = newMeal; } reader2.Close(); DailyMeal dailyMeal = new DailyMeal((uint)reader.GetInt32(0), meal, reader.GetDateTime(2)); dailyMeals.Add(dailyMeal); } reader.Close(); connection.Close(); return(dailyMeals); }
/// <summary> /// Update DailyMeals in Database /// </summary> /// <param name="meal">Meal</param> /// <param name="dailyMeals">Daily Meals</param> /// <param name="user">User</param> public void DailyMealsDataUpdate(Meal meal, DailyMeal dailyMeals, User user) { connection.Open(); string query = "UPDATE DailyMeals SET Meal = @MEAL, Date = @DATE, UserId = @USERID WHERE Id = @ID"; SqlCommand cmd = new SqlCommand(query, connection); cmd.Parameters.Add("@MEAL", SqlDbType.Int); cmd.Parameters.Add("@DATE", SqlDbType.DateTime); cmd.Parameters.Add("@USERID", SqlDbType.Int); cmd.Parameters.Add("@ID", SqlDbType.Int); cmd.Parameters["@MEAL"].Value = meal.Id; cmd.Parameters["@DATE"].Value = dailyMeals.Date; cmd.Parameters["@USERID"].Value = user.Id; cmd.Parameters["@ID"].Value = dailyMeals.Id; cmd.ExecuteNonQuery(); connection.Close(); }