public void Cancel(Order order)
{
DbEvent dbEvent = new DbEvent();
Order currentOrder = (Order)Get(order.OrderId);
int Quantity = currentOrder.Quantity;
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
using (SqlCommand command = connection.CreateCommand())
{
SqlCommand deleteTicket = connection.CreateCommand();
SqlCommand deleteTicketAndSeat = connection.CreateCommand();
SqlCommand updateEventTickets = connection.CreateCommand();
command.CommandText = "Delete from OrderItems where OrderId = @id; Delete from Orders where OrderId = @id;";
command.Parameters.AddWithValue("id", order.OrderId);
List <Ticket> tickets = GetOrderTickets(order.OrderId);
command.ExecuteNonQuery();
foreach (var item in tickets)
{
deleteTicketAndSeat.CommandText = "Delete from Ticket where TicketId = @TicketId; Delete from Seat where SeatId = @SeatId";
deleteTicketAndSeat.Parameters.AddWithValue("TicketId", item.TicketId);
deleteTicketAndSeat.Parameters.AddWithValue("SeatId", item.SeatId);
deleteTicketAndSeat.ExecuteNonQuery();
deleteTicketAndSeat.Parameters.Clear();
}
Event orderEvent = (Event)dbEvent.Get(order.EventId);
int AvailableTickets = orderEvent.AvailableTickets;
updateEventTickets.CommandText = "Update Event set AvailableTickets = @newAvailableTickets where EventId = @EventId";
updateEventTickets.Parameters.AddWithValue("newAvailableTickets", Quantity + AvailableTickets);
updateEventTickets.Parameters.AddWithValue("EventId", order.EventId);
updateEventTickets.ExecuteNonQuery();
}
}
}
// Create Order
public int Create(object obj)
{
int insertedOrderId;
TransactionOptions options = new TransactionOptions();
options.IsolationLevel = IsolationLevel.Serializable;
try
{
using (TransactionScope scope = new TransactionScope(TransactionScopeOption.Required, options))
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
connection.Open();
// SqlTransaction transaction = connection.BeginTransaction();
using (SqlCommand command = new SqlCommand("", connection))
{
#region method logic
Order myOrder = (Order)obj;
Event myEvent = new Event();
Event currentEvent = new Event();
DbEvent dbEvent = new DbEvent();
currentEvent = (Event)dbEvent.Get(myOrder.EventId, connection);
int availableTicketsOfCurrentEvent = currentEvent.AvailableTickets;
command.CommandText = "Insert into Orders (TotalPrice, Date, Quantity, CustomerId, EventId) values (@TotalPrice, @Date, @Quantity, @CustomerId, @EventId); SELECT SCOPE_IDENTITY()";
command.Parameters.AddWithValue("TotalPrice", myOrder.TotalPrice);
command.Parameters.AddWithValue("Date", myOrder.Date);
command.Parameters.AddWithValue("Quantity", myOrder.Quantity);
command.Parameters.AddWithValue("CustomerId", myOrder.CustomerId);
command.Parameters.AddWithValue("EventId", myOrder.EventId);
insertedOrderId = Convert.ToInt32(command.ExecuteScalar());
#endregion
int x = myOrder.Quantity;
while (x > 0)
{
#region create seats and tickets
SqlCommand command1 = new SqlCommand("", connection);
Seat newSeat = new Seat();
myEvent = (Event)dbEvent.Get(myOrder.EventId, connection);
int availableTickets = myEvent.AvailableTickets;
newSeat.SeatNumber = availableTickets;
newSeat.Available = true;
newSeat.EventId = myOrder.EventId;
DbSeat dbSeat = new DbSeat();
int inseretedSeatId = dbSeat.Create(newSeat, connection);
Ticket newTicket = new Ticket();
newTicket.EventId = myOrder.EventId;
newTicket.SeatId = inseretedSeatId;
newTicket.CustomerId = myOrder.CustomerId;
DbTicket dbTicket = new DbTicket();
int TicketId = dbTicket.Create(newTicket, connection);
int EventId2 = myOrder.EventId;
x--;
availableTickets--;
// Minus 1 from available tickets on
command1.CommandText = "Insert into OrderItems (OrderId, TicketId) values (@OrderId, @TicketId); UPDATE Event set AvailableTickets = @availableTickets WHERE EventId = @EventId2";
command1.Parameters.AddWithValue("OrderId", insertedOrderId);
command1.Parameters.AddWithValue("TicketId", TicketId);
command1.Parameters.AddWithValue("availableTickets", availableTickets);
command1.Parameters.AddWithValue("EventId2", EventId2);
command1.ExecuteNonQuery();
command1.Parameters.Clear();
#endregion
}
if (myOrder.Quantity <= availableTicketsOfCurrentEvent && myOrder.Quantity > 0)
{
scope.Complete();
connection.Close();
}
else
{
scope.Dispose();
}
}
return(insertedOrderId);
}
}
}
catch (Exception e)
{
Console.WriteLine(e.StackTrace);
return(0);
}
}