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 List <PerformerModel> GetArtists()
{
var artistList = new List <PerformerModel>();
using (var dbConnection = WingtipTicketApp.CreateTenantSqlConnection())
{
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 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.CreateTenantSqlConnection())
{
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 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.CreateTenantSqlConnection()))
{
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 PerformerModel GetArtistByName(String artistName)
{
PerformerModel artistToReturn = null;
using (var dbConnection = WingtipTicketApp.CreateTenantSqlConnection())
{
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 <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.CreateTenantSqlConnection()))
{
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 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.CreateTenantSqlConnection()))
{
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 <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.CreateTenantSqlConnection()))
{
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 UserExists(string email)
{
using (var conn = WingtipTicketApp.CreateTenantSqlConnection())
{
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.CreateTenantSqlConnection())
{
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.CreateTenantSqlConnection())
{
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.CreateTenantSqlConnection())
{
conn.Open();
using (var cmd = new SqlCommand(sqlScript, conn))
{
cmd.ExecuteNonQuery();
}
}
}
public Boolean DeleteConcert(int concertId)
{
using (var dbConnection = WingtipTicketApp.CreateTenantSqlConnection())
{
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 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.CreateTenantSqlConnection())
{
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 List <ConcertTicket> WriteNewTicketToDb(PurchaseTicketsModel model)
{
using (var insertConnection = WingtipTicketApp.CreateTenantSqlConnection())
{
insertConnection.Open();
for (var i = 0; i < model.Quantity; i++)
{
var ticketName = String.Format("Ticket ({0} of {1}) for user {2} to concert-{3}", (i + 1), model.Quantity, model.CustomerName, model.ConcertId);
var insertQuery = String.Format(@"INSERT INTO Tickets (CustomerId, Name, TicketLevelId, ConcertId, PurchaseDate, SeatNumber) VALUES ('{0}', '{1}', '{2}', '{3}', '{4}', '{5}')", model.CustomerId, ticketName, model.SeatSectionId, model.ConcertId, DateTime.Now, model.Seats[i]);
using (var insertCommand = new SqlCommand(insertQuery, insertConnection))
{
insertCommand.ExecuteNonQuery();
}
}
insertConnection.Close();
insertConnection.Dispose();
}
return(ReturnPurchasedTicketsByConcertId(model.CustomerId, model.ConcertId));
}
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.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 = 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.CreateTenantSqlConnection())
{
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.CreateTenantSqlConnection())
{
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 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.CreateTenantSqlConnection())
{
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.CreateTenantSqlConnection())
{
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.CreateTenantSqlConnection()))
{
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.CreateTenantSqlConnection())
{
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);
}
