public OrderProduct GetOrderProductByID(int id)
{
OrderProduct order = new OrderProduct();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
string query = @"SELECT*
FROM ORDERPRODUCT
WHERE ORDERID=" + id;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
order.OrderID = (int)reader["ORDERID"];
order.ProductID = (int)reader["PRODCUTID"];
order.Quantity = (int)reader["QUANTITY"];
}
}
return order;
}
public void CreateOrAddOrderProduct(OrderProduct ordp)
{
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = @"SELECT COUNT(*)
FROM ORDERPRODUCT
WHERE ORDERID=" + ordp.OrderID;
var command = new SqlCommand(query,connection);
int counter = (int)command.ExecuteScalar();
if (counter == 1)
{
string updateQuery = @"UPDATE ORDERPRODUCT
SET ORDERID =@orderID,PRODUCTID=@productID,QUANTITY=@qunatity
WHERE ORDERID="+ordp.OrderID;
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.Parameters.AddWithValue("@orderID", ordp.OrderID);
updateCommand.Parameters.AddWithValue("@productID", ordp.ProductID);
updateCommand.Parameters.AddWithValue("@qunatity", ordp.Quantity);
updateCommand.ExecuteNonQuery();
}
else
{
string addQuery = @"INSERT INTO ORDERPRODUCT(ORDERID,PRODUCTID,QUANTITY)
VALUES(@orderID,@productID,@quantity)";
var addCommand = new SqlCommand(addQuery, connection);
addCommand.Parameters.AddWithValue("@orderID", ordp.OrderID);
addCommand.Parameters.AddWithValue("@productID", ordp.ProductID);
addCommand.Parameters.AddWithValue("@qunatity", ordp.Quantity);
addCommand.ExecuteNonQuery();
}
}
}
public List<OrderProduct> ListOrederProducts()
{
List<OrderProduct> orderProd = new List<OrderProduct>();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
string query = @"SELECT*
FROM ORDERPRODUCT";
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
OrderProduct ordp = new OrderProduct();
ordp.OrderID = (int)reader["ORDERID"];
ordp.ProductID = (int)reader["PRODCUTID"];
ordp.Quantity = (int)reader["QUANTITY"];
orderProd.Add(ordp);
}
}
return orderProd;
}
public void UpdateOrInsert(OrderProduct orderProduct)
{
string query = $@"IF EXISTS (SELECT TOP 1 *
FROM OrderProducts
WHERE OrderID = '{orderProduct.OrderId}' AND ProductID = '{orderProduct.ProductId}')
BEGIN
UPDATE OrderProducts
SET Quantity = '{orderProduct.Quantity}'
WHERE OrderID = '{orderProduct.OrderId}' AND ProductID = '{orderProduct.ProductId}'
END
ELSE
BEGIN
SET IDENTITY_INSERT OrderProducts ON
INSERT INTO OrderProducts
VALUES ('{orderProduct.OrderId}', '{orderProduct.ProductId}', '{orderProduct.Quantity}')
SET Identity_INSERT OrderProducts OFF
END";
ExecuteNonQuery(query);
}