public static List <TModelType> ExecuteReader <TModelType>(string sqlScript, Func <SqlDataReader, TModelType> mapper)
{
var items = new List <TModelType>();
// Stop if no script supplied
if (string.IsNullOrEmpty(sqlScript))
{
throw new Exception("No Sql to specified to Execute");
}
// Run the script
using (var connection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
connection.Open();
using (var command = new SqlCommand(sqlScript, connection))
{
var reader = command.ExecuteReader();
while (reader.Read() && mapper != null)
{
items.Add(mapper(reader));
}
}
}
return(items);
}
public SeatSectionModel GetSeatSection(int venueId, string description)
{
var seatSections = new List <SeatSectionModel>();
var query =
$@"SELECT S.SeatSectionId, S.SeatCount, S.Description, S.VenueId, TL.TicketLevelId, TL.TicketPrice, TL.Description AS TicketLevelDescription FROM SeatSection S JOIN TicketLevels TL ON S.SeatSectionId = TL.SeatSectionId WHERE S.VenueId = {
venueId} AND S.Description = '{description}'";
using (var cmd = new SqlCommand(query, WingtipTicketApp.CreateTenantConnectionDatabase1()))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
var dsUser = new DataSet();
sdAdapter.Fill(dsUser);
if (dsUser.Tables.Count > 0)
{
seatSections.AddRange(from DataRow row in dsUser.Tables[0].Rows
select new SeatSectionModel()
{
SeatSectionId = Convert.ToInt32(row["SeatSectionId"]), TicketPrice = Convert.ToDecimal(row["TicketPrice"]), VenueId = Convert.ToInt32(row["VenueId"]), Description = row["Description"].ToString(), SeatCount = Convert.ToInt32(row["SeatCount"]), TicketLevelDescription = row["TicketLevelDescription"].ToString(), TicketLevelId = Convert.ToInt32(row["TicketLevelId"])
});
}
}
}
return(seatSections.FirstOrDefault());
}
public List <ConcertTicketLevel> GetTicketLevels()
{
var ticketLevels = new List <ConcertTicketLevel>();
const string ticketLevelQuery = "SELECT TicketLevelId, Description, SeatSectionId, ConcertId, TicketPrice FROM TicketLevels";
using (var cmd = new SqlCommand(ticketLevelQuery, WingtipTicketApp.CreateTenantConnectionDatabase1()))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
using (var dsTickets = new DataSet())
{
sdAdapter.Fill(dsTickets);
if (dsTickets.Tables.Count > 0 && dsTickets.Tables[0].Rows.Count > 0)
{
foreach (DataRow drTicket in dsTickets.Tables[0].Rows)
{
ticketLevels.Add(new ConcertTicketLevel(Convert.ToInt32(drTicket["TicketLevelId"]), drTicket["Description"].ToString(), Convert.ToInt32(drTicket["ConcertId"]), Convert.ToInt32(drTicket["SeatSectionId"]), Convert.ToDecimal(drTicket["TicketPrice"])));
}
}
}
}
}
return(ticketLevels);
}
public PerformerModel AddNewArtist(String artistName)
{
var dsInsert = new DataSet();
var insertQuery = String.Format("Insert Into Performers (FirstName, LastName, ShortName) Values ('{0}', '{1}', '{0} {1}') Select @@Identity", artistName.Split(' ')[0], artistName.Split(' ')[1]);
using (var insertCommand = new SqlCommand(insertQuery, WingtipTicketApp.CreateTenantConnectionDatabase1()))
{
using (var insertData = new SqlDataAdapter(insertCommand))
{
insertData.Fill(dsInsert);
if (dsInsert.Tables.Count > 0 && dsInsert.Tables[0].Rows.Count > 0 && dsInsert.Tables[0].Rows[0][0] != DBNull.Value)
{
return(new PerformerModel
{
PerformerId = Int32.Parse(dsInsert.Tables[0].Rows[0][0].ToString()),
ShortName = artistName,
FirstName = artistName.Split(' ')[0],
LastName = artistName.Split(' ')[1]
});
}
}
}
return(null);
}
public List <ConcertTicket> ReturnPurchasedTicketsByCustomerId(int customerId)
{
var ticketList = new List <ConcertTicket>();
var ticketsPurchasedByCustomerIdQuery = String.Format(@"SELECT TicketId, CustomerId, Name, TicketLevelId, ConcertId, PurchaseDate, SeatNumber FROM Tickets WHERE (CustomerId=" + customerId + ")");
using (var cmd = new SqlCommand(ticketsPurchasedByCustomerIdQuery, WingtipTicketApp.CreateTenantConnectionDatabase1()))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
using (var dsTickets = new DataSet())
{
sdAdapter.Fill(dsTickets);
ticketList.AddRange(from DataRow drTicket in dsTickets.Tables[0].Rows
select new ConcertTicket(
Convert.ToInt32(drTicket[0].ToString()),
Convert.ToInt32(drTicket[1].ToString()),
drTicket[2].ToString(),
Convert.ToInt32(drTicket[4].ToString()),
Convert.ToInt32(drTicket[3].ToString()),
0,
Convert.ToDateTime(drTicket[5].ToString()),
drTicket[6].ToString()));
}
}
}
return(ticketList);
}
public SeatSectionModel GetSeatSectionDetails(int seatSectionId)
{
var seatSections = new List <SeatSectionModel>();
var query =
$@"SELECT * FROM SeatSection WHERE SeatSectionId = {seatSectionId}";
using (var cmd = new SqlCommand(query, WingtipTicketApp.CreateTenantConnectionDatabase1()))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
var dsUser = new DataSet();
sdAdapter.Fill(dsUser);
if (dsUser.Tables.Count > 0)
{
seatSections.AddRange(from DataRow row in dsUser.Tables[0].Rows
select new SeatSectionModel()
{
SeatSectionId = Convert.ToInt32(row["SeatSectionId"]),
VenueId = Convert.ToInt32(row["VenueId"]),
Description = row["Description"].ToString(),
SeatCount = Convert.ToInt32(row["SeatCount"])
});
}
}
}
return(seatSections.FirstOrDefault());
}
public AllSeatsModel GetSeatDetails(string seatDescription, int tminusDaysToConcert)
{
var seatDetails = new AllSeatsModel();
var sqlQuery = $@"SELECT * FROM AllSeats WHERE SeatDescription = '{seatDescription}' AND TMinusDaysToConcert = {tminusDaysToConcert}";
using (var cmd = new SqlCommand(sqlQuery, WingtipTicketApp.CreateTenantConnectionDatabase1()))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
using (var ds = new DataSet())
{
sdAdapter.Fill(ds);
seatDetails = (from DataRow dr in ds.Tables[0].Rows
select
new AllSeatsModel(Convert.ToInt32(dr[0].ToString()), dr[1].ToString(),
Convert.ToInt32(dr[2].ToString()), Convert.ToInt32(dr[3].ToString()),
Convert.ToInt32(dr[4].ToString()), Convert.ToInt32(dr[5].ToString()),
Convert.ToInt32(dr[6].ToString()), Convert.ToInt32(dr[7].ToString())))
.FirstOrDefault();
}
}
}
return(seatDetails);
}
public List <ConcertTicket> WriteNewTicketToDb(List <PurchaseTicketsModel> model)
{
List <ConcertTicket> purchasedTickets = new List <ConcertTicket>();
using (var insertConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
insertConnection.Open();
for (var i = 0; i < model.Count; i++)
{
var ticketName = String.Format("Ticket ({0} of {1}) for user {2} to concert-{3}", (i + 1), model.Count, model[i].CustomerName, model[i].ConcertId);
var insertQuery = String.Format(@"INSERT INTO Tickets (CustomerId, Name, TicketLevelId, ConcertId, PurchaseDate, SeatNumber) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", model[i].CustomerId, ticketName, model[i].SeatSectionId, model[i].ConcertId, DateTime.Now, model[i].Seat);
using (var insertCommand = new SqlCommand(insertQuery, insertConnection))
{
insertCommand.ExecuteNonQuery();
}
var tickets = ReturnPurchasedTicketsByConcertId(model[i].CustomerId, model[i].ConcertId);
purchasedTickets.AddRange(tickets);
}
insertConnection.Close();
insertConnection.Dispose();
}
return(purchasedTickets);
}
public List <CustomerModel> GetUsers()
{
var customers = new List <CustomerModel>();
var query = String.Format(@"Select CustomerId, FirstName, LastName, Email, ContactNbr from Customers Where Email <> '*****@*****.**'");
using (var cmd = new SqlCommand(query, WingtipTicketApp.CreateTenantConnectionDatabase1()))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
var dsUser = new DataSet();
sdAdapter.Fill(dsUser);
if (dsUser.Tables.Count > 0)
{
foreach (DataRow row in dsUser.Tables[0].Rows)
{
customers.Add(new CustomerModel()
{
CustomerId = Convert.ToInt32(row["CustomerId"]),
FirstName = row["FirstName"].ToString(),
LastName = row["LastName"].ToString(),
Email = row["Email"].ToString(),
PhoneNumber = row["ContactNbr"].ToString()
});
}
}
}
}
return(customers);
}
public List <PerformerModel> GetArtists()
{
var artistList = new List <PerformerModel>();
using (var dbConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
try
{
dbConnection.Open();
var queryCommand = new SqlCommand("Select PerformerId, FirstName, LastName, ShortName From Performers", dbConnection);
using (var reader = queryCommand.ExecuteReader())
{
while (reader.Read())
{
var tempArtist = PopulateSingleArtistFromDbReader(reader);
if (tempArtist != null)
{
artistList.Add(tempArtist);
}
}
}
}
finally
{
dbConnection.Close();
}
}
return(artistList);
}
public PerformerModel GetArtistByName(String artistName)
{
PerformerModel artistToReturn = null;
using (var dbConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
try
{
dbConnection.Open();
var queryCommand = new SqlCommand(String.Format("Select Top(1) * From Performers Where ShortName='{0}'", artistName), dbConnection);
using (var reader = queryCommand.ExecuteReader())
{
if (reader.Read())
{
artistToReturn = PopulateSingleArtistFromDbReader(reader);
}
}
}
finally
{
dbConnection.Close();
}
}
return(artistToReturn);
}
public bool UserExists(string email)
{
using (var conn = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
using (var dbReader = new SqlCommand(String.Format(@"Select CustomerId From Customers Where Email={0}", email), conn).ExecuteReader())
{
return(dbReader.HasRows);
}
}
}
public List <ConcertModel> GetConcerts(int venueId = 0, bool orderByName = false)
{
var concertsList = new List <ConcertModel>();
using (var dbConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
try
{
dbConnection.Open();
SqlCommand queryCommand;
if (venueId == 0)
{
queryCommand = new SqlCommand(String.Format("{0}", ConstGetAllConcertsQuery), dbConnection);
}
else
{
var query = String.Format("{0} WHERE concerts.VenueId={1} {2}", ConstGetAllConcertsQuery, venueId, orderByName ? ConstOrderByConcertName : ConstOrderByConcertDate);
queryCommand = new SqlCommand(query, dbConnection);
}
using (var reader = queryCommand.ExecuteReader())
{
try
{
while (reader.Read())
{
var tempConcert = PopulateSingleConcertFromDbReader(reader);
if (tempConcert != null)
{
concertsList.Add(tempConcert);
}
}
}
finally
{
reader.Close();
}
}
}
finally
{
dbConnection.Close();
}
}
return(concertsList);
}
public void DeleteAllTicketsForConcert(int concertId)
{
// Delete all tickets and ticket levels for this concert
using (var dbConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
dbConnection.Open();
using (var cmd = new SqlCommand(String.Format(@"DELETE FROM [TicketLevels] WHERE ConcertId = {0}", concertId), dbConnection))
{
cmd.ExecuteNonQuery();
}
using (var cmd = new SqlCommand(String.Format(@"DELETE FROM [Tickets] WHERE ConcertId = {0}", concertId), dbConnection))
{
cmd.ExecuteNonQuery();
}
}
}
public static void ExecuteNonQuery(string sqlScript)
{
// Stop if no script supplied
if (string.IsNullOrEmpty(sqlScript))
{
throw new Exception("No Sql to specified to Execute");
}
// Run the script
using (var conn = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
conn.Open();
using (var cmd = new SqlCommand(sqlScript, conn))
{
cmd.ExecuteNonQuery();
}
}
}
public List <DiscountedSeatModel> GetDiscountedSeat(int seatSectionId, int seatNumber)
{
var discountedSeatModels = new List <DiscountedSeatModel>();
var sqlQuery = $@"SELECT * FROM Discount WHERE SeatSectionId = {seatSectionId} AND SeatNumber = {seatNumber} ORDER BY DiscountId DESC";
using (var cmd = new SqlCommand(sqlQuery, WingtipTicketApp.CreateTenantConnectionDatabase1()))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
using (var ds = new DataSet())
{
sdAdapter.Fill(ds);
discountedSeatModels.AddRange(
from DataRow dr in ds.Tables[0].Rows
select new DiscountedSeatModel(Convert.ToInt32(dr[0].ToString()), Convert.ToInt32(dr[1].ToString()), Convert.ToInt32(dr[2].ToString()), Convert.ToDecimal(dr[3].ToString()), Convert.ToInt32(dr[4].ToString()), Convert.ToDecimal(dr[5].ToString())));
}
}
}
return(discountedSeatModels);
}
public DiscountedSeatModel ApplyDiscount(DiscountModel discountModel)
{
using (var insertConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
insertConnection.Open();
var insertQuery =
$@"INSERT INTO Discount (SeatSectionId, SeatNumber, InitialPrice, Discount, FinalPrice) VALUES ('{
discountModel.SeatSectionId}', '{discountModel.SeatNumber}', '{discountModel.InitialPrice}', '{
discountModel.Discount}', '{discountModel.FinalPrice}')";
using (var insertCommand = new SqlCommand(insertQuery, insertConnection))
{
insertCommand.ExecuteNonQuery();
}
insertConnection.Close();
insertConnection.Dispose();
}
return(GetDiscountedSeat(discountModel.SeatSectionId, discountModel.SeatNumber).First());
}
public List <Tuple <int, int> > GetConcertIdToDbSaveMap()
{
var saveToDbMap = new List <Tuple <int, int> >();
const string queryCommandString = @"Select ConcertId as concertId, SaveToDbServerType as saveToDatabase From [Concerts] Order By concertId";
using (var sqlConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
sqlConnection.Open();
using (var sqlCommand = new SqlCommand(queryCommandString, sqlConnection))
{
using (var reader = sqlCommand.ExecuteReader())
{
while (reader.Read())
{
saveToDbMap.Add(new Tuple <int, int>(reader.GetInt32(0), reader.GetInt32(1)));
}
}
}
}
return(saveToDbMap);
}
public Boolean DeleteConcert(int concertId)
{
using (var dbConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
try
{
dbConnection.Open();
var getSingleConcertQuery = String.Format("DELETE FROM Concerts WHERE concerts.ConcertId={0}", concertId);
var queryCommand = new SqlCommand(getSingleConcertQuery, dbConnection);
queryCommand.ExecuteNonQuery();
}
finally
{
dbConnection.Close();
}
}
LogAction("Deleted concert " + concertId);
return(true);
}
public int UpdateSeatDetails(int discount, string seatDescription, int tMinusDaysToConcert, int count)
{
string columnName = "[0%]";
switch (discount)
{
case 10:
columnName = "[10%]";
break;
case 20:
columnName = "[20%]";
break;
case 30:
columnName = "[30%]";
break;
}
using (var connection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
connection.Open();
var sqlQuery =
$"UPDATE AllSeats SET {columnName} = {count} WHERE SeatDescription = '{seatDescription}' AND TMinusDaysToConcert = {tMinusDaysToConcert}";
using (var insertCommand = new SqlCommand(sqlQuery, connection))
{
insertCommand.ExecuteNonQuery();
}
connection.Close();
connection.Dispose();
}
return(1);
}
public bool Login(string email, string password)
{
var query = String.Format(@"Select FirstName, LastName, CustomerId from Customers Where Email='{0}' and Password='******'", email, password);
using (var cmd = new SqlCommand(query, WingtipTicketApp.CreateTenantConnectionDatabase1()))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
var dsUser = new DataSet();
sdAdapter.Fill(dsUser);
if (dsUser.Tables.Count > 0 && dsUser.Tables[0].Rows.Count > 0)
{
var newUser = new CustomerModel
{
FirstName = dsUser.Tables[0].Rows[0]["FirstName"].ToString(),
LastName = dsUser.Tables[0].Rows[0]["LastName"].ToString(),
Email = email,
CustomerId = Convert.ToInt32(dsUser.Tables[0].Rows[0]["CustomerId"])
};
HttpContext.Current.Session["SessionUser"] = newUser;
if (Startup.SessionUsers.Any(a => a.Email != null && a.Email.ToUpper() == email.ToUpper()))
{
Startup.SessionUsers.Remove(Startup.SessionUsers.First(a => a.Email.ToUpper() == email.ToUpper()));
}
Startup.SessionUsers.Add(newUser);
return(true);
}
}
}
return(false);
}
public static int ExecuteInsert(string sqlScript)
{
var entityId = 0;
var dataSet = new DataSet();
using (var connection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
using (var command = new SqlCommand(sqlScript, connection))
{
using (var adapter = new SqlDataAdapter(command))
{
adapter.Fill(dataSet);
// Capture id of the new entity
if (dataSet.Tables.Count > 0 && dataSet.Tables[0].Rows.Count > 0 && dataSet.Tables[0].Rows[0][0] != DBNull.Value)
{
Int32.TryParse(dataSet.Tables[0].Rows[0][0].ToString(), out entityId);
}
return(entityId);
}
}
}
}
public ConcertModel GetConcertById(int concertId)
{
ConcertModel concertModelToReturn = null;
using (var dbConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
try
{
dbConnection.Open();
var getSingleConcertQuery = String.Format("{0} WHERE concerts.ConcertId={1} {2}", ConstGetAllConcertsQuery, concertId, ConstOrderByConcertDate);
var queryCommand = new SqlCommand(getSingleConcertQuery, dbConnection);
using (var reader = queryCommand.ExecuteReader())
{
try
{
if (reader.Read())
{
concertModelToReturn = PopulateSingleConcertFromDbReader(reader);
}
}
finally
{
reader.Close();
}
}
}
finally
{
dbConnection.Close();
}
}
return(concertModelToReturn);
}
public int GetVenueIdByVenueName(string venueName)
{
var sqlScript = $"SELECT VenueId FROM Venues WHERE VenueName = '{venueName}'";
var venueIds = new List <int>();
using (var cmd = new SqlCommand(sqlScript, WingtipTicketApp.CreateTenantConnectionDatabase1()))
{
using (var sdAdapter = new SqlDataAdapter(cmd))
{
var dsUser = new DataSet();
sdAdapter.Fill(dsUser);
if (dsUser.Tables.Count > 0)
{
foreach (DataRow row in dsUser.Tables[0].Rows)
{
venueIds.Add(Convert.ToInt32(row["VenueId"]));
}
}
}
}
return(venueIds.First());
}
public bool CreateUser(string firstName, string lastName, string email, string phonenumber, string password)
{
var database1Result = CreateUserOnDatabase(firstName, lastName, email, phonenumber, password, true, WingtipTicketApp.CreateTenantConnectionDatabase1());
var database2Result = CreateUserOnDatabase(firstName, lastName, email, phonenumber, password, false, WingtipTicketApp.CreateTenantConnectionDatabase2());
return(database1Result);
}
public ConcertModel SaveNewConcert(String concertName, String concertDescription, DateTime concertDateTime, ServerTargetEnum saveToDatabase, int concertVenueId, int performerId)
{
ConcertModel concertModelToReturn = null;
var tempDs = new DataSet();
#region Insert
var insertQuery = String.Format(ConstInsertNewConcert, concertName, concertDescription, concertDateTime, ConstConcertduration, concertVenueId, performerId, (int)saveToDatabase);
using (var insertConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
insertConnection.Open();
using (var insertCommand = new SqlCommand(insertQuery, insertConnection))
{
insertCommand.ExecuteNonQuery();
}
insertConnection.Close();
insertConnection.Dispose();
}
#endregion
#region Get Information
var getCommandQuery = string.Format("{0} WHERE (concerts.ConcertName='{1}' AND concerts.VenueId={2} AND concerts.PerformerId={3}) {4}", ConstGetAllConcertsQuery, concertName, concertVenueId, performerId, ConstOrderByConcertDate);
using (var getConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
getConnection.Open();
using (var reader = new SqlCommand(getCommandQuery, getConnection).ExecuteReader())
{
if (reader.Read())
{
concertModelToReturn = PopulateSingleConcertFromDbReader(reader);
}
}
getConnection.Close();
getConnection.Dispose();
}
#endregion
#region Populate Ticket Levels
var i = 1;
var seatSectionQuery = string.Format(@"SELECT * FROM [SeatSection] Where VenueId={0}", concertVenueId);
using (var seatCommand = new SqlCommand(seatSectionQuery, WingtipTicketApp.CreateTenantConnectionDatabase1()))
{
using (var seatDataAdapter = new SqlDataAdapter(seatCommand))
{
seatDataAdapter.Fill(tempDs);
if (tempDs.Tables.Count > 0 && tempDs.Tables[0].Rows.Count > 0)
{
foreach (DataRow drSeat in tempDs.Tables[0].Rows)
{
var ticketLevelInsert = string.Format(@"INSERT INTO [TicketLevels] (Description, SeatSectionId, ConcertId, TicketPrice) Values('Level-{0}', {0}, {1}, '{2}')", drSeat["SeatSectionId"].ToString(), concertModelToReturn.ConcertId, (50 + (5 * i++)).ToString() + ".00");
using (var ticketConnection = WingtipTicketApp.CreateTenantConnectionDatabase1())
{
ticketConnection.Open();
using (var ticketCommand = new SqlCommand(ticketLevelInsert, ticketConnection))
{
ticketCommand.ExecuteNonQuery();
}
ticketConnection.Close();
ticketConnection.Dispose();
}
}
}
}
}
#endregion
LogAction("Added new concert " + concertName + " for venueId " + concertVenueId);
return(concertModelToReturn);
}