public static void addBarServiceUI(string name, decimal price, int amount) { // make a new variable of type SomerenModel.BarService in order to store data to it SomerenModel.BarService newBarService = new SomerenModel.BarService(); // store data to the newBarSrvice variable newBarService.setDrinkName(name); newBarService.setDrinkPrice(price); newBarService.setStockAmount(amount); // passing data to the DB layer SomerenDB.DB_addBarService(newBarService); }
public static List <SomerenModel.BarService> DB_getBarServices() { // make a sql connection SqlConnection connection = openConnectionDB(); // make a list to store data from DB into it List <SomerenModel.BarService> barServiceList = new List <SomerenModel.BarService>(); // the sql queries string sqlOne = "SELECT DISTINCT m.drink_id , m.drink_name, m.stock_amount, m.price, p.drink_sold"; string sqlTwo = " FROM BarService AS m LEFT JOIN studentbarservice AS p on m.drink_id = p.drink_id"; string sqlThree = " WHERE m.stock_amount > 1 AND m.price > 1"; string sqlFour = " AND m.drink_name not IN('Water', 'Orangeade', 'Cherry juice')"; string sqlFive = " ORDER BY 3 ,4, 5"; string finalQuery = sqlOne + sqlTwo + sqlThree + sqlFour + sqlFive; SqlCommand command = new SqlCommand(finalQuery, connection); SqlDataReader reader = command.ExecuteReader(); // retrieving data from DB while (reader.Read()) { // store data to the list SomerenModel.BarService barService = new SomerenModel.BarService(); barService.setDrinkId((int)reader["drink_id"]); barService.setDrinkName((string)reader["drink_name"]); barService.setDrinkPrice((decimal)reader["price"]); // use decimal to avoid any conflict with DB barService.setStockAmount((int)reader["stock_amount"]); barServiceList.Add(barService); } // close all connections reader.Close(); connection.Close(); return(barServiceList); }