public bool Insert(Order order)
{
using (var connection = new SqlConnection(this.connectionString))
{
connection.Open();
string query = @"SELECT count(*) FROM [Order] c WHERE c.Id='" + order.Id + "'";
var command = new SqlCommand(query, connection);
int rows = (int)command.ExecuteScalar();
if (rows == 1)
{
string formattedDate = order.Date.ToString("yyyyMMdd HH:mm:ss");
string updateQuery =
string.Format(
"UPDATE [Order] SET Date='{0}',CustomerId='{1}', totalPrice='{2}' WHERE Id={3}",
formattedDate,
order.CustomerId,
order.TotalPrice,
order.Id);
var updateCommand = new SqlCommand(updateQuery, connection);
updateCommand.ExecuteNonQuery();
return true;
}
else if (rows == 0)
{
string formattedDate = order.Date.ToString("yyyyMMdd HH:mm:ss");
string insertQuery = string.Format(
"INSERT INTO [Order] VALUES('{0}','{1}','{2}')",
formattedDate,
order.CustomerId,
order.TotalPrice);
var insertCommand = new SqlCommand(insertQuery, connection);
insertCommand.ExecuteNonQuery();
order.Id = this.GetLastId("[Order]");
}
}
return true;
}
public void UpdateOrInsert(Order order)
{
string query = $@"IF EXISTS (SELECT TOP 1 *
FROM Orders
WHERE OrderID = '{order.OrderId}')
BEGIN
UPDATE Orders
SET DateOfOrder = '{order.DateOfOrder}'
SET CustomerID = '{order.CustomerId}'
SET TotalPrice = '{order.TotalPrice}'
WHERE OrderID = '{order.OrderId}'
END
ELSE
BEGIN
SET IDENTITY_INSERT Orders ON
INSERT INTO Orders
VALUES ('{order.OrderId}', '{order.DateOfOrder}', '{order.CustomerId}', '{order.TotalPrice}')
SET IDENTITY_INSERT Orders OFF
END";
ExecuteNonQuery(query);
}