public List<Orders> ListOrders()
{
List<Orders> orders = new List<Orders>();
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
string query = @"SELECT*
FROM ORDERS";
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
Orders ord = new Orders();
ord.DateTimeOfTheOrder = (DateTime)reader["DATE TIME OF THE ORDER"];
ord.CustomerID = (int)reader["CUSTOMERID"];
ord.OrderID = (int)reader["ORDERID"];
ord.TotalPrice = (decimal)reader["TOTAL PRICE"];
orders.Add(ord);
}
}
return orders;
}
public Orders GetOrderById(int id)
{
Orders order = new Orders();
string query = @"SELECT*
FROM[ORDERS]
WHERE ORDERID=" + id;
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
var reader = command.ExecuteReader();
while (reader.Read())
{
order.DateTimeOfTheOrder = (DateTime)reader["DATE TIME OF THE ORDER"];
order.CustomerID = (int)reader["CUSTOMERID"];
order.OrderID = (int)reader["ORDERID"];
order.TotalPrice = (decimal)reader["TOTAL PRICE"];
}
}
return order;
}
public void CreateOrAddOreder(Orders oreder)
{
string connectionString = ConfigurationManager.ConnectionStrings["HACK_BULGARIA"].ConnectionString;
string query = @"SELECT COUNT(*)
FROM ORDERS
WHERE ORDERID="+oreder.OrderID;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
var command = new SqlCommand(query, connection);
int counter = (int)command.ExecuteScalar();
if (counter==1)
{
string updateQuery = @"UPDATE ORDERS
SET DATE TIME OF THE ORDER=@time,CUSTOMERID=@customerID,ORDERID=@orderId,TOTAL PRICE=@pirce
WHERE OREDERID="+oreder.OrderID;
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.Parameters.AddWithValue("@time", oreder.DateTimeOfTheOrder);
updateCommand.Parameters.AddWithValue("@customerID", oreder.CustomerID);
updateCommand.Parameters.AddWithValue("@orderId", oreder.OrderID);
updateCommand.Parameters.AddWithValue("@price", oreder.TotalPrice);
updateCommand.ExecuteNonQuery();
}
else
{
string addQuery = @"INSERT INTO ORDERS(DATE TIME OF THE ORDER,CUSTOMERID,ORDERID,TOTAL PRICE)
VALUES(@time,@customerID,@orderId,@price)";
var addCommand = new SqlCommand(addQuery, connection);
addCommand.Parameters.AddWithValue("@time", oreder.DateTimeOfTheOrder);
addCommand.Parameters.AddWithValue("@customerID", oreder.CustomerID);
addCommand.Parameters.AddWithValue("@orderId", oreder.OrderID);
addCommand.Parameters.AddWithValue("@price", oreder.TotalPrice);
addCommand.ExecuteNonQuery();
}
}
}