public void AddCustomerToDb(Order order)
{
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string countCommand = "SELECT COUNT(*) FROM Order WHERE id=" + order.Id;
SqlCommand sqlCountCommand = new SqlCommand(countCommand, connection);
var count = (int)sqlCountCommand.ExecuteScalar();
if (count >= 1)
{
string updateCommand = "UPDATE Order SET Date=@date,Customer_Id=@customerId,TotalPrice = @totalPrice WHERE Id=" + order.Id;
AddOrEditOrders(updateCommand, order, connection);
}
else
{
string addCommand = "INSERT INTO Order (Date,Customer_Id,TotalPrice) VALUES (@date,@customerId,@totalPrice)";
AddOrEditOrders(addCommand, order, connection);
SqlCommand getLastIdCmd = new SqlCommand("SELECT @@IDENTITY", connection);
int insertedRecordId = (int)(decimal)getLastIdCmd.ExecuteScalar();
order.Id = insertedRecordId;
}
}
}
public List<Order> GetAllOrders()
{
var ordersList = new List<Order>();
using (var connection = new SqlConnection(connectionString))
{
connection.Open();
string command = "SELECT * FROM Order";
SqlCommand sqlCommand = new SqlCommand(command, connection);
var reader = sqlCommand.ExecuteReader();
while (reader.Read())
{
var order = new Order();
order.Date = (DateTime)reader["Date"];
order.Id = (int)reader["Id"];
order.TotalPrice = (decimal)reader["TotalPrice"];
order.CustomerId = (int)reader["Customer_Id"];
ordersList.Add(order);
}
}
return ordersList.ToList();
}
private void AddOrEditOrders(string command, Order order, SqlConnection connection)
{
SqlCommand sqlCommand = new SqlCommand(command, connection);
sqlCommand.Parameters.AddWithValue("@date", order.Date);
sqlCommand.Parameters.AddWithValue("@customerId", order.CustomerId);
sqlCommand.Parameters.AddWithValue("@totalPrice", order.TotalPrice);
var rows = sqlCommand.ExecuteNonQuery();
Console.WriteLine("Rows Affected: {0}", rows);
}