public void Save(Event Event)
{
// Code to save the Event entity is not required in this scenario
RemovePurchasedAndReservedTicketsFrom(Event);
InsertPurchasedTicketsFrom(Event);
InsertReservedTicketsFrom(Event);
}
public static TicketPurchase CreateTicket(Event Event, int tktQty)
{
TicketPurchase ticket = new TicketPurchase();
ticket.Id = Guid.NewGuid();
ticket.Event = Event;
ticket.TicketQuantity = tktQty;
return ticket;
}
public static TicketReservation CreateReservation(Event Event, int tktQty)
{
TicketReservation reservation = new TicketReservation();
reservation.Id = Guid.NewGuid();
reservation.Event = Event;
reservation.ExpiryTime = DateTime.Now.AddMinutes(1);
reservation.TicketQuantity = tktQty;
return reservation;
}
public static TicketReservation CreateReservation(Event Event, int tktQty)
{
TicketReservation reservation = new TicketReservation()
{
Event = Event,
ExpiryTime = DateTime.Now.AddMinutes(1),
HasBeenRedeemed = false,
Id = new Guid(),
TicketQuantity = tktQty
};
return reservation;
}
public void InsertReservedTicketsFrom(Event Event)
{
string insertSQL = "INSERT INTO dbo.ReservedTickets (Id, EventId, TicketQuantity, ExpiryTime, HasBeenRedeemed) " +
"VALUES " +
"(@Id, @EventId, @TicketQuantity, @ExpiryTime, @HasBeenRedeemed);";
foreach (TicketReservation ticket in Event.ReservedTickets)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = insertSQL;
SqlParameter Idparam = new SqlParameter("@Id", ticket.Id.ToString());
command.Parameters.Add(Idparam);
SqlParameter EventIdparam = new SqlParameter("@EventId", ticket.Event.Id.ToString());
command.Parameters.Add(EventIdparam);
SqlParameter TktQtyparam = new SqlParameter("@TicketQuantity", ticket.TicketQuantity);
command.Parameters.Add(TktQtyparam);
SqlParameter Expiryparam = new SqlParameter("@ExpiryTime", ticket.ExpiryTime);
command.Parameters.Add(Expiryparam);
SqlParameter HasBeenRedeemedparam = new SqlParameter("@HasBeenRedeemed", ticket.HasBeenRedeemed);
command.Parameters.Add(HasBeenRedeemedparam);
connection.Open();
command.ExecuteNonQuery();
}
}
}
public Event FindBy(Guid id)
{
Event Event = default(Event);
string queryString = "SELECT * from dbo.Events WHERE Id = @EventId " +
"SELECT * FROM dbo.PurchasedTickets WHERE EventId = @EventId" +
"SELECT * FROM dbo.ReservedTickets WHERE EventId = @EventId;";
using (SqlConnection conn = new SqlConnection(connectionString))
{
SqlCommand command = conn.CreateCommand();
command.CommandText = queryString;
SqlParameter Idparam = new SqlParameter("@EventId", id.ToString());
command.Parameters.Add(Idparam);
conn.Open();
using (SqlDataReader reader = command.ExecuteReader())
{
if (reader.HasRows)
{
reader.Read();
Event = new Event();
Event.PurchasedTickets = new List<TicketPurchase>();
Event.ReservedTickets = new List<TicketReservation>();
Event.Allocation = int.Parse(reader["Allocation"].ToString());
Event.Id = new Guid(reader["Id"].ToString());
Event.Name = reader["Name"].ToString();
if (reader.NextResult())
{
if (reader.HasRows)
{
while (reader.Read())
{
TicketPurchase ticketPurchase = new TicketPurchase();
ticketPurchase.Id = new Guid(reader["Id"].ToString());
ticketPurchase.Event = Event;
ticketPurchase.TicketQuantity = int.Parse(reader["TicketQuantity"].ToString());
Event.PurchasedTickets.Add(ticketPurchase);
}
}
}
if (reader.NextResult())
{
if (reader.HasRows)
{
while (reader.Read())
{
TicketReservation ticketReservation = new TicketReservation();
ticketReservation.Id = new Guid(reader["Id"].ToString());
ticketReservation.Event = Event;
ticketReservation.ExpiryTime = DateTime.Parse(reader["ExpiryTime"].ToString());
ticketReservation.TicketQuantity = int.Parse(reader["TicketQuantity"].ToString());
ticketReservation.HasBeenRedeemed = bool.Parse(reader["HasBeenRedeemed"].ToString());
Event.ReservedTickets.Add(ticketReservation);
}
}
}
}
}
}
return Event;
}
public void RemovePurchasedAndReservedTicketsFrom(Event Event)
{
string deleteSQL = "DELETE PurchasedTickets WHERE EventId = @EventId; " +
"DELETE ReservedTickets WHERE EventId = @EventId;";
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = deleteSQL;
SqlParameter Idparam = new SqlParameter("@EventId", Event.Id.ToString());
command.Parameters.Add(Idparam);
connection.Open();
command.ExecuteNonQuery();
}
}
public void InsertPurchasedTicketsFrom(Event Event)
{
string insertSQL = "INSERT INTO dbo.PurchaseTickets (Id, EventId, TicketQuantity) VALUES " +
"(@Id, @EventId, @TicketQuantity);";
foreach (TicketPurchase ticket in Event.PurchasedTickets)
{
using (SqlConnection connection = new SqlConnection(connectionString))
{
SqlCommand command = connection.CreateCommand();
command.CommandText = insertSQL;
SqlParameter Idparam = new SqlParameter("@Id", ticket.Id.ToString());
command.Parameters.Add(Idparam);
SqlParameter EventIdparam = new SqlParameter("@EventId", ticket.Event.Id.ToString());
command.Parameters.Add(EventIdparam);
SqlParameter TktQtyparam = new SqlParameter("@TicketQuantity", ticket.TicketQuantity);
command.Parameters.Add(TktQtyparam);
connection.Open();
command.ExecuteNonQuery();
}
}
}
public void Save(Event eventEntry)
{
//code to save back into database
RemovePurchasedAndReservedTicketFrom(eventEntry);
InsertPuchasedTicketFrom(eventEntry);
InsertReservedTicketFrom(eventEntry);
}
private void InsertReservedTicketFrom(Event eventEntry)
{
string insertSql = "InsertReservedTickets";
foreach (var reservedTicket in eventEntry.ReservedTickets)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = insertSql;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Id", reservedTicket.Id);
cmd.Parameters.AddWithValue("@ExpiryTime", reservedTicket.ExpiryTime);
cmd.Parameters.AddWithValue("@TicketQuantity", reservedTicket.TicketQuantity);
cmd.Parameters.AddWithValue("@EventId", eventEntry.Id);
cmd.Parameters.AddWithValue("@HasBeenRedeemed", reservedTicket.HasBeenRedeemed);
cn.Open();
cmd.ExecuteNonQuery();
}
}
}
private void InsertPuchasedTicketFrom(Event eventEntry)
{
string sqlString = "InsertPurchasedTickets";
foreach (var purchasedTicket in eventEntry.PurchasedTickets)
{
using (SqlConnection cn = new SqlConnection(connectionString))
{
SqlCommand cmd = cn.CreateCommand();
cmd.CommandText = sqlString;
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@id", SqlDbType.UniqueIdentifier).Value = purchasedTicket.Id;
cmd.Parameters.Add("@tktQUantity", SqlDbType.Int).Value = purchasedTicket.TicketQuantity;
cmd.Parameters.Add("@EventId", SqlDbType.UniqueIdentifier).Value = purchasedTicket.Event.Id;
cn.Open();
cmd.ExecuteNonQuery();
}
}
}