public void UpdateMealsVsLunchPlans(MealsVsLunchPlans mealsVsLunchPlans)
{
string Id = mealsVsLunchPlans.Id.ToString();
string lunchPlanId = mealsVsLunchPlans.LunchPlanId.ToString();
string mealId = mealsVsLunchPlans.MealId.ToString();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
using (SqlCommand command = new SqlCommand("UPDATE Meals SET LunchPlanId = @lunchPlanId, MealId = @mealId, Weekday = @weekday WHERE Id = @Id", conn))
{
command.Parameters.AddWithValue("@Id", Id);
command.Parameters.AddWithValue("@lunchPlanId", lunchPlanId);
command.Parameters.AddWithValue("@mealId", mealId);
command.Parameters.AddWithValue("@weekday", mealsVsLunchPlans.Weekday);
command.ExecuteNonQuery();
}
}
}
catch (Exception eSql)
{
Debug.WriteLine("Exception: " + eSql.Message);
}
}
public void AddMealsVsLunchPlans(MealsVsLunchPlans mealsVsLunchPlans)
{
string AddMealsVsLunchPlansQuery = $"INSERT into MealsVsLunchPlans (LunchPlanId, MealId, Weekday) VALUES (@LunchPlanId, @MealId, @Weekday)";
string LunchPlanId = mealsVsLunchPlans.LunchPlanId.ToString();
string MealId = mealsVsLunchPlans.MealId.ToString();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = AddMealsVsLunchPlansQuery;
cmd.Parameters.AddWithValue("@LunchPlanId", LunchPlanId);
cmd.Parameters.AddWithValue("@MealId", MealId);
cmd.Parameters.AddWithValue("@Weekday", mealsVsLunchPlans.Weekday);
cmd.ExecuteNonQuery();
}
}
}
}
catch (Exception eSql)
{
Debug.WriteLine("Exception: " + eSql.Message);
}
}
/// <summary>
/// Adds MealVsLunchPlan to DB.
/// </summary>
/// <param name="lunchPlanId"></param>
/// <param name="mealId"></param>
/// <param name="weekday"></param>
public void AddMealsVsLunchPlans(int lunchPlanId, int mealId, string weekday)
{
MealsVsLunchPlans mvs = new MealsVsLunchPlans
{
LunchPlanId = lunchPlanId,
MealId = mealId,
Weekday = weekday
};
//I don't know if this is included to Model anywhere else? Doing that might make some calls to the DB unnecessary.
lunchPlanHandler.AddMealVsLunchPlan(mvs);
}
public bool AddMealVsLunchPlan(MealsVsLunchPlans mealsVsLunchPlans)
{
try
{
DbAccess.AddMealsVsLunchPlans(mealsVsLunchPlans);
Model.MealsVsLunchPlans.Add(mealsVsLunchPlans);
return(true);
}
catch (Exception e)
{
Debug.WriteLine(e.Message);
return(false);
}
}
public Model GetDataAndCreateModel()
{
const string GetAdminsQuery = "SELECT * from Admins";
const string GetLunchPlansQuery = "SELECT * from LunchPlans";
const string GetMessagesQuery = "SELECT * from Messages";
const string GetMealsQuery = "SELECT * from Meals";
const string GetMealsVsLunchPlansQuery = "SELECT * from MealsVsLunchPlans";
const string GetIpQuery = "SELECT * FROM DeviceIP";
var admins = new ObservableCollection <Admin>();
var lunchPlans = new ObservableCollection <LunchPlan>();
var messages = new ObservableCollection <Message>();
var meals = new ObservableCollection <Meal>();
var mealsVsLunchPlansCollection = new ObservableCollection <MealsVsLunchPlans>();
var ip = new ObservableCollection <IpAddress>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = GetAdminsQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var admin = new Admin();
admin.Id = reader.GetInt32(0);
admin.Username = reader.GetString(1);
admin.PasswordSalt = (byte[])reader.GetValue(2);
admin.PasswordHash = (byte[])reader.GetValue(3);
admins.Add(admin);
}
}
cmd.CommandText = GetMessagesQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var message = new Message();
message.Id = reader.GetInt32(0);
message.AdminId = reader.GetInt32(1);
message.Date = reader.GetDateTime(2);
message.Text = reader.GetString(3);
message.Header = reader.GetString(4);
messages.Add(message);
}
}
cmd.CommandText = GetMealsQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var meal = new Meal();
meal.Id = reader.GetInt32(0);
meal.Description = reader.GetString(1);
meal.TimesChosen = reader.GetInt32(2);
meals.Add(meal);
}
}
cmd.CommandText = GetLunchPlansQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var lunchPlan = new LunchPlan();
lunchPlan.Id = reader.GetInt32(0);
lunchPlan.Week = reader.GetInt32(1);
lunchPlans.Add(lunchPlan);
}
}
cmd.CommandText = GetMealsVsLunchPlansQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var mealsVsLunchPlans = new MealsVsLunchPlans();
mealsVsLunchPlans.Id = reader.GetInt32(0);
mealsVsLunchPlans.LunchPlanId = reader.GetInt32(1);
mealsVsLunchPlans.MealId = reader.GetInt32(2);
mealsVsLunchPlans.Weekday = reader.GetString(3);
mealsVsLunchPlansCollection.Add(mealsVsLunchPlans);
}
}
cmd.CommandText = GetIpQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var address = new IpAddress
{
Id = reader.GetInt32(0),
Ip = reader.GetString(1)
};
ip.Add(address);
}
}
}
}
}
}
catch (Exception eSql)
{
Debug.WriteLine("Exception: " + eSql.Message);
}
Model model = new Model(admins, lunchPlans, messages, meals, mealsVsLunchPlansCollection, ip);
return(model);
}
public Model GetDataAndCreateModel()
{
const string GetLunchPlansQuery = "SELECT * from LunchPlans";
const string GetMessagesQuery = "SELECT * from Messages";
const string GetMealsVsLunchPlansQuery = "SELECT * from MealsVsLunchPlans";
const string GetMealsQuery = "SELECT * from Meals";
const string GetIpQuery = "SELECT * FROM DeviceIP";
var lunchPlans = new ObservableCollection <LunchPlan>();
var messages = new ObservableCollection <Message>();
var mealsVsLunchPlansCollection = new ObservableCollection <MealsVsLunchPlans>();
var meals = new ObservableCollection <Meal>();
var ip = new ObservableCollection <IpAddress>();
try
{
using (SqlConnection conn = new SqlConnection(connectionString))
{
conn.Open();
if (conn.State == System.Data.ConnectionState.Open)
{
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandText = GetMealsVsLunchPlansQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var mealsVsLunchPlans = new MealsVsLunchPlans
{
Id = reader.GetInt32(0),
LunchPlanId = reader.GetInt32(1),
MealId = reader.GetInt32(2),
Weekday = reader.GetString(3)
};
mealsVsLunchPlansCollection.Add(mealsVsLunchPlans);
}
}
cmd.CommandText = GetLunchPlansQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var lunchPlan = new LunchPlan
{
Id = reader.GetInt32(0),
Week = reader.GetInt32(1)
};
lunchPlans.Add(lunchPlan);
}
}
cmd.CommandText = GetMessagesQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var message = new Message
{
Id = reader.GetInt32(0),
AdminId = reader.GetInt32(1),
Date = reader.GetDateTime(2),
Text = reader.GetString(3),
Header = reader.GetString(4)
};
messages.Add(message);
}
}
cmd.CommandText = GetMealsQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var meal = new Meal
{
Id = reader.GetInt32(0),
Description = reader.GetString(1),
TimesChosen = reader.GetInt32(2)
};
meals.Add(meal);
}
}
cmd.CommandText = GetIpQuery;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
var address = new IpAddress
{
Id = reader.GetInt32(0),
Ip = reader.GetString(1)
};
ip.Add(address);
}
}
}
}
}
}
catch (Exception eSql)
{
DataValidation.SaveError(eSql.ToString());
//DataValidation.SaveError("Exception: " + eSql.GetType() + "\n " + eSql.Message + "\r\n" + eSql.StackTrace + "\r\n In " + ToString());
}
Model model = new Model(lunchPlans, messages, meals, mealsVsLunchPlansCollection, ip);
return(model);
}
