public List <SeatSectionLayoutViewModel> GetSeatSectionLayout(int concertId, int ticketLevelId)
{
using (var context = new WingTipTicketsEntities(WingtipTicketApp.GetTenantConnectionString(WingtipTicketApp.Config.TenantDatabase1)))
{
var ticketLevel = context.TicketLevels.First(t => t.TicketLevelId == ticketLevelId);
var result = context.SeatSectionLayouts.Where(l => l.SeatSectionId == ticketLevel.SeatSectionId).Select(l => new SeatSectionLayoutViewModel()
{
RowNumber = (int)l.RowNumber,
SkipCount = (int)l.SkipCount,
StartNumber = (int)l.StartNumber,
EndNumber = (int)l.EndNumber,
SelectedSeats = context.Tickets
.Where(t => t.TicketLevelId == ticketLevelId &&
t.ConcertId == concertId &&
t.SeatNumber >= (int)l.StartNumber &&
t.SeatNumber <= (int)l.EndNumber)
.Select(t => (int)t.SeatNumber)
.Distinct()
.ToList()
}).ToList();
return(result);
}
}
public Promotion GetPromotion(Int64 customerId, Int64 productId)
{
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT DISTINCT CustomerId, ProductId, NewPrice, Promotion FROM Promotions WHERE CustomerId = @CustomerId AND ProductId = @ProductId";
cmd.Parameters.Add(new SqlParameter("CustomerId", SqlDbType.BigInt)
{
Value = customerId
});
cmd.Parameters.Add(new SqlParameter("ProductId", SqlDbType.BigInt)
{
Value = productId
});
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
return(new Promotion
{
CustomerId = (Int64)reader["CustomerId"],
ProductId = (Int64)reader["ProductId"],
PromotionDiscount = reader["Promotion"].ToString(),
NewPrice = (int)reader["NewPrice"]
});
}
}
}
}
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 Product GetProduct(Int64 id)
{
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Products WHERE Id = @Id";
cmd.Parameters.Add(new SqlParameter("Id", SqlDbType.BigInt)
{
Value = id
});
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
return(new Product
{
Id = (Int64)reader["Id"],
Name = reader["Name"].ToString(),
Description = reader["Description"].ToString(),
Title1 = reader["Title1"].ToString(),
Title2 = reader["Title2"].ToString(),
TitlesCount = (int)(reader["TitlesCount"] ?? 0),
Price = (int)reader["Price"]
});
}
}
}
}
public CustomerRec GetCustomerById(Int64 id)
{
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT * FROM Customers WHERE Id = @Id";
cmd.Parameters.Add(new SqlParameter("Id", SqlDbType.BigInt)
{
Value = id
});
using (var reader = cmd.ExecuteReader())
{
if (!reader.Read())
{
return(null);
}
return(new CustomerRec
{
CustomerId = (Int64)reader["Id"],
UserName = reader["Name"].ToString(),
Region = reader["Region"].ToString()
});
}
}
}
}
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 bool CreateUser(string firstName, string lastName, string email, string phonenumber, string password)
{
var query = String.Format(@"Insert into Customers (FirstName, LastName, Email, ContactNbr, Password)
Values ('{0}', '{1}', '{2}', '{3}', '{4}');
Select @@Identity as 'Identity'", firstName, lastName, email, phonenumber, password);
using (var cmd = new SqlCommand(query, WingtipTicketApp.CreateTenantSqlConnection()))
{
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 = firstName,
LastName = lastName,
Email = email,
PhoneNumber = phonenumber,
CustomerId = Convert.ToInt32(dsUser.Tables[0].Rows[0]["Identity"])
};
Startup.SessionUsers.Add(newUser);
HttpContext.Current.Session["SessionUser"] = newUser;
LogAction("Added new user - " + firstName + " " + lastName);
return(true);
}
}
}
return(false);
}
public string GetApplicationDefault(string code)
{
using (var context = new WingTipTicketsEntities(WingtipTicketApp.GetTenantConnectionString()))
{
return(context.ApplicationDefaults.First(a => a.Code.Equals(code)).Value);
}
}
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 IEnumerable <Product> GetRecommendedProducts(Int64 customerId)
{
var products = new List <Product>();
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT DISTINCT Top 10 Related.RecommendedProductId, Products.Name, Products.Price, Products.TitlesCount FROM PersonalizedRecommendations AS Related INNER JOIN Products ON Related.RecommendedProductId = Products.Id WHERE Related.CustomerId = @CustomerId";
cmd.Parameters.Add(new SqlParameter("CustomerId", SqlDbType.BigInt)
{
Value = customerId
});
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
products.Add(
new Product
{
Id = (Int64)reader["RecommendedProductId"],
Name = reader["Name"].ToString(),
Price = (int)reader["Price"],
TitlesCount = (int)reader["TitlesCount"]
});
}
}
}
}
return(products);
}
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 IEnumerable <Product> GetProducts()
{
var products = new List <Product>();
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT top 20 * FROM Products";
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
products.Add(
new Product
{
Id = (Int64)reader["Id"],
Name = reader["Name"].ToString(),
Description = reader["Description"].ToString(),
Title1 = reader["Title1"].ToString(),
Title2 = reader["Title2"].ToString(),
TitlesCount = (int)reader["TitlesCount"],
Price = (int)reader["Price"]
});
}
}
}
}
return(products);
}
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 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 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, new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName))))
{
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 Customer
{
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);
}
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 FindSeatsViewModel GetFindSeatsData(int concertId)
{
using (var context = new WingTipTicketsEntities(WingtipTicketApp.GetTenantConnectionString(WingtipTicketApp.Config.TenantDatabase1)))
{
var concert = context.Concerts.First(c => c.ConcertId == concertId);
var venue = context.Venues.First(v => v.VenueId == concert.VenueId);
var performer = context.Performers.First(p => p.PerformerId == concert.PerformerId);
var seatSections = context.TicketLevels.Where(t => t.ConcertId == concertId).ToList();
// Map to ViewModel
var viewModel = new FindSeatsViewModel()
{
// Main Models
Concert = new FindSeatsViewModel.ConcertViewModel()
{
ConcertId = concert.ConcertId,
ConcertName = concert.ConcertName,
ConcertDate = (DateTime)concert.ConcertDate,
VenueId = venue.VenueId,
VenueName = venue.VenueName,
PerformerName = performer.ShortName
},
// Collections
SeatSections = new SelectList(seatSections, "SeatSectionId", "Description", null),
};
return(viewModel);
}
}
public IEnumerable <Promotion> GetPromotions(Int64 customerId)
{
var promotions = new List <Promotion>();
using (var conn = WingtipTicketApp.CreateRecommendationSqlConnection())
{
conn.Open();
using (var cmd = conn.CreateCommand())
{
cmd.CommandText = "SELECT DISTINCT CustomerId, ProductId, NewPrice, Promotion FROM Promotions WHERE CustomerId = @CustomerId";
cmd.Parameters.Add(new SqlParameter("CustomerId", SqlDbType.BigInt)
{
Value = customerId
});
using (var reader = cmd.ExecuteReader())
{
while (reader.Read())
{
promotions.Add(
new Promotion
{
CustomerId = (Int64)reader["CustomerId"],
ProductId = (Int64)reader["ProductId"],
PromotionDiscount = reader["Promotion"].ToString(),
NewPrice = (int)reader["NewPrice"]
});
}
}
}
}
return(promotions);
}
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 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> 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 bool UserExists(string email)
{
using (var conn = new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName)))
{
using (var dbReader = new SqlCommand(String.Format(@"Select CustomerId From Customers Where Email={0}", email), conn).ExecuteReader())
{
return(dbReader.HasRows);
}
}
}
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 void SetApplicationDefault(string code, string value)
{
using (var context = new WingTipTicketsEntities(WingtipTicketApp.GetTenantConnectionString()))
{
var setting = context.ApplicationDefaults.First(a => a.Code.Equals(code));
setting.Value = value;
context.SaveChanges();
}
}
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 bool RefreshConcerts(bool fullReset)
{
#region Full Reset - Trim Extra Concerts
if (fullReset)
{
var trimSql = ReadSqlFromFile(HttpContext.Current.Server.MapPath("~/SqlScripts/TrimConcerts.sql"));
if (!string.IsNullOrEmpty(trimSql))
{
using (var conn = new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName)))
{
conn.Open();
using (var cmd = new SqlCommand(trimSql, conn))
{
cmd.ExecuteNonQuery();
}
}
}
}
#endregion Full Reset - Trim Extra Concerts
#region Push Concert Dates to Future
var resetDatesSql = ReadSqlFromFile(HttpContext.Current.Server.MapPath("~/SqlScripts/ResetConcertDates.sql"));
if (!string.IsNullOrEmpty(resetDatesSql))
{
using (var conn = new SqlConnection(WingtipTicketApp.ConstructConnection(WingtipTicketApp.Config.PrimaryDatabaseServer, WingtipTicketApp.Config.TenantDbName)))
{
conn.Open();
using (var cmd = new SqlCommand(resetDatesSql, conn))
{
cmd.ExecuteNonQuery();
}
}
}
#endregion Push Concert Dates to Future
return(true);
}
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 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());
}