// Function returns the Order with the specified order_id and dish_id from the database
public async Task <Order_Dish> GetById(int order_id, int dish_id)
{
using (NpgsqlConnection sql = new NpgsqlConnection(_connectionString)) // Specifying the database context
{
using (NpgsqlCommand cmd = new NpgsqlCommand("\"spOrder_Dish_GetById\"", sql)) // Specifying stored procedure
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new NpgsqlParameter("order_id", NpgsqlDbType.Integer));
cmd.Parameters.Add(new NpgsqlParameter("dish_id", NpgsqlDbType.Integer));
cmd.Parameters[0].Value = order_id;
cmd.Parameters[1].Value = dish_id;
Order_Dish response = null;
await sql.OpenAsync();
// Parsing the data retrieved from the database
using (var reader = await cmd.ExecuteReaderAsync())
{
while (await reader.ReadAsync())
{
response = MapToValue(reader);
}
}
return(response);
}
}
}
//Ajoute 1 Order_Dish
public Order_Dish AddOrder_Dish(Order_Dish order_Dish)
{
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query = "INSERT into Order_Dish(FK_idOrder, FK_idDish, quantity, total) values( @FK_idOrder, @FK_idDish, @quantity, @total); SELECT SCOPE_IDENTITY()";
SqlCommand cmd = new SqlCommand(query, cn);
//cmd.Parameters.AddWithValue("@quantity", order_Dish.Quantity);
cmd.Parameters.AddWithValue("@FK_idOrder", order_Dish.FK_idOrder);
cmd.Parameters.AddWithValue("@FK_idDish", order_Dish.FK_idDish);
cmd.Parameters.AddWithValue("@quantity", order_Dish.Quantity);
cmd.Parameters.AddWithValue("@total", order_Dish.Total);
cn.Open();
order_Dish.IdOrder_Dish = Convert.ToInt32(cmd.ExecuteScalar());
}
}
catch (Exception e)
{
throw e;
}
return(order_Dish);
}
//Ajoute la quantité à un Order_Dish
public int UpdateOrder_Dish(Order_Dish order_dish)
{
int result = 0;
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query = "UPDATE Order_Dish set quantity=@quantity WHERE Id=@id;";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.AddWithValue("@id", order_dish.IdOrder_Dish);
cmd.Parameters.AddWithValue("@quantity", order_dish.Quantity);
cn.Open();
result = cmd.ExecuteNonQuery();
}
}
catch (Exception e)
{
throw e;
}
return(result);
}
public async Task <ActionResult> Post([FromBody] Order_Dish order_dish)
{
try
{
// Checking if referenced order and dish exist (if not an exception is thrown)
await _orderRepository.GetById(order_dish.Order_ID);
await _dishRepository.GetById(order_dish.Dish_ID);
// Inserting record in the Order_Dish table
await _repository.Insert(order_dish);
// if no exceptionn was thrown by insert above, the record was successfully inserted
// We update the amount in the corresponding transaction
Order order = await _orderRepository.GetById(order_dish.Order_ID);
Dish dish = await _dishRepository.GetById(order_dish.Dish_ID);
await _transationRepository.updateAmount(order.Transaction_ID, await _transationRepository.getAmount(order.Transaction_ID) + dish.Price);
return(Ok("Order_Dish record inserted successfully\n"));
}
catch (Npgsql.PostgresException ex)
{
// Postgres threw an exception
return(BadRequest(ex.Message.ToString()));
}
catch
{
// Unknown error
return(BadRequest("Error: Order_Dish record was not inserted\n"));
}
}
//Retourne un Order_Dish en fonction de son ID
public Order_Dish GetOrder_Dish(int id)
{
Order_Dish order_Dish = null;
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query = "Select * from Order_Dish WHERE Id=@id";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.AddWithValue("@id", id);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
if (dr.Read())
{
if (dr["Id"] != DBNull.Value)
{
order_Dish.IdOrder_Dish = (int)dr["Id"];
}
if (dr["quantity"] != DBNull.Value)
{
order_Dish.Quantity = (int)dr["quantity"];
}
if (dr["FK_idOrder"] != DBNull.Value)
{
order_Dish.FK_idOrder = (int)dr["FK_idOrder"];
}
if (dr["FK_idDish"] != DBNull.Value)
{
order_Dish.FK_idDish = (int)dr["FK_idDish"];
}
if (dr["total"] != DBNull.Value)
{
order_Dish.Total = (int)dr["total"];
}
}
}
}
}
catch (Exception e)
{
throw e;
}
return(order_Dish);
}
//Retourne une liste de tous les Order_Dish en fonction de l'ID de l'ordre
public List <Order_Dish> GetAllOrder_Dish(int idOrder)
{
List <Order_Dish> results = new List <Order_Dish>();
try
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
string query = "SELECT * FROM Order_Dish WHERE FK_idOrder=@idOrder";
SqlCommand cmd = new SqlCommand(query, cn);
cmd.Parameters.AddWithValue("@idOrder", idOrder);
cn.Open();
using (SqlDataReader dr = cmd.ExecuteReader())
{
while (dr.Read())
{
if (results == null)
{
results = new List <Order_Dish>();
}
Order_Dish order_Dish = new Order_Dish();
order_Dish.IdOrder_Dish = (int)dr["Id"];
if (dr["quantity"] != DBNull.Value)
{
order_Dish.Quantity = (int)dr["quantity"];
}
order_Dish.FK_idOrder = (int)dr["FK_idOrder"];
order_Dish.FK_idDish = (int)dr["FK_idDish"];
order_Dish.Total = (int)dr["total"];
results.Add(order_Dish);
}
}
}
}
catch (Exception e)
{
throw e;
}
return(results);
}
// Function inserts an Order_Dish record in the database
public async Task Insert(Order_Dish order_dish)
{
using (NpgsqlConnection sql = new NpgsqlConnection(_connectionString)) // Specifying the database context
{
using (NpgsqlCommand cmd = new NpgsqlCommand("\"spOrder_Dish_InsertValue\"", sql)) // Specifying stored procedure
{
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.Parameters.Add(new NpgsqlParameter("order_id", NpgsqlDbType.Integer));
cmd.Parameters.Add(new NpgsqlParameter("dish_id", NpgsqlDbType.Integer));
cmd.Parameters[0].Value = order_dish.Order_ID;
cmd.Parameters[1].Value = order_dish.Dish_ID;
await sql.OpenAsync();
await cmd.ExecuteNonQueryAsync();
return;
}
}
}
public Order_Dish AddOrder_Dish(Order_Dish order_Dish)
{
return(Order_DishDBObject.AddOrder_Dish(order_Dish));
}
public int UpdateOrder_Dish(Order_Dish order_Dish)
{
return(Order_DishDBObject.UpdateOrder_Dish(order_Dish));
}