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