Example #1
0
        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();
                }
            }
        }
Example #2
0
        // 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);
            }
        }