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