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); }