public static List <Meal> RetrieveAll(Booking booking) { List <Meal> result = new List <Meal>(); try { SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM bookings_meals WHERE book_id = @book_id", Database.GetConnection()); sda.SelectCommand.Parameters.AddWithValue("@book_id", booking.ID); DataTable dataResult = new DataTable(); sda.Fill(dataResult); foreach (DataRow record in dataResult.Rows) { result.Add(new Meal( Booking.RetrieveById((int)record["book_id"]), FoodMenu.RetrieveById((int)record["menu_id"]), (int)record["no_of_meals"] )); } return(result); } catch (Exception ex) { Console.WriteLine("ERROR: {0}", ex.Message); //return an empty list on error return(new List <Meal>()); } }
/// <summary> /// Returns a FoodMenu from the DB with a particular id /// </summary> /// <param name="id"></param> /// <returns>FoodMenu or null if no such menu is found</returns> public static FoodMenu RetrieveById(int id) { try { //using INNER JOIN to connect the menu and raw_stock tables string cmdText = "SELECT menu_id, meat.stock_name, veg1.stock_name, veg2.stock_name, drink.stock_name, menus.menu_price " + "FROM menus INNER JOIN raw_stock meat ON menus.menu_meat_id = meat.stock_id " + "INNER JOIN raw_stock veg1 ON menus.menu_veg_1_id = veg1.stock_id " + "INNER JOIN raw_stock veg2 ON menus.menu_veg_2_id = veg2.stock_id " + "INNER JOIN raw_stock drink ON menus.menu_drink_id = drink.stock_id WHERE menu_id = @menu_id"; SqlDataAdapter sda = new SqlDataAdapter(cmdText, Database.GetConnection()); sda.SelectCommand.Parameters.AddWithValue("@menu_id", id); DataTable dataResult = new DataTable(); sda.Fill(dataResult); //grab the customer record DataRow custRec = dataResult.Rows[0]; FoodMenu menu = new FoodMenu( (int)custRec[0], custRec[1].ToString(), custRec[2].ToString(), custRec[3].ToString(), custRec[4].ToString(), (decimal)custRec[5] ); return(menu); } catch (Exception ex) { Console.WriteLine("ERROR: {0}", ex.Message); //null is returned when information isn't found or if it cannot be processed return(null); } }
public Meal(Booking booking, FoodMenu menu, int quanity) { Booking = booking; Menu = menu; Quantity = quanity; }