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;
}