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"; var lunchPlans = new ObservableCollection <LunchPlan>(); var messages = new ObservableCollection <Message>(); var mealsVsLunchPlansCollection = new ObservableCollection <MealsVsLunchPlans>(); var meals = new ObservableCollection <Meal>(); 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(); 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 = 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 = 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); } } } } } } catch (Exception eSql) { Debug.WriteLine("Exception: " + eSql.Message); } Model model = new Model(lunchPlans, messages, meals, mealsVsLunchPlansCollection); 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); }