public static void deleteBarServiceUI(int id)
{
// 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.setDrinkId(id);
// passing data to the DB layer
SomerenDB.DB_deleteBarService(newBarService);
}
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 void DB_deleteBarService(SomerenModel.BarService newBarSrvice)
{
// make a sql connection
SqlConnection connection = openConnectionDB();
// the sql query for deleting data from DB
string sqlQuery = "DELETE FROM BarService WHERE drink_id = @drink_id";
SqlCommand command = new SqlCommand(sqlQuery, connection);
// execute queries
command.Parameters.AddWithValue("@drink_id", newBarSrvice.getDrinkId());
command.ExecuteNonQuery();
// close the connection
connection.Close();
}
public static void DB_addBarService(SomerenModel.BarService newBarSrvice)
{
// make a sql connection
SqlConnection connection = openConnectionDB();
// the sql query for inserting new data to the DB
string sqlQuery = "INSERT INTO BarService(drink_name, price, stock_amount) VALUES(@drink_name, @price, @stock_amount)";
SqlCommand command = new SqlCommand(sqlQuery, connection);
// execute queries
command.Parameters.AddWithValue("@drink_name", newBarSrvice.getDrinkName());
command.Parameters.AddWithValue("@price", newBarSrvice.getDrinkPrice());
command.Parameters.AddWithValue("@stock_amount", newBarSrvice.getStockAmount());
command.ExecuteNonQuery();
// close the connection
connection.Close();
}
public static void DB_uppdateBarService(SomerenModel.BarService newBarSrvice)
{
// make a sql connection
SqlConnection connection = openConnectionDB();
// the sql query for updating data of the DB
string sqlQuery = "UPDATE BarService SET drink_name = @drink_name, price = @price, stock_amount= @stock_amount WHERE drink_id = @drink_id ";
SqlCommand command = new SqlCommand(sqlQuery, connection);
// execute queries
command.Parameters.AddWithValue("@drink_id", newBarSrvice.getDrinkId());
command.Parameters.AddWithValue("@drink_name", newBarSrvice.getDrinkName());
command.Parameters.AddWithValue("@price", newBarSrvice.getDrinkPrice());
command.Parameters.AddWithValue("@stock_amount", newBarSrvice.getStockAmount());
command.ExecuteNonQuery();
// close the connection
connection.Close();
}
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);
}
