public Boolean DeleteConcert(int concertId) { var dbConnectionString = ConstructConcertsConnnectString(); using (var dbConnection = new SqlConnection(dbConnectionString)) { 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(); } } VenuesDbContext.LogAction("Deleted concert " + concertId); return(true); }
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, 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 = firstName, LastName = lastName, Email = email, PhoneNumber = phonenumber, CustomerId = Convert.ToInt32(dsUser.Tables[0].Rows[0]["Identity"]) }; HttpContext.Current.Session["SessionUser"] = newUser; Startup.SessionUsers.Add(newUser); } } } VenuesDbContext.LogAction("Added new user - " + firstName + " " + lastName); return(true); }
public Concert SaveNewConcert(String concertName, String concertDescription, DateTime concertDateTime, ShardDbServerTargetEnum saveToDatabase, int concertVenueId, int performerId) { Concert concertToReturn = null; var tempDs = new DataSet(); #region Insert var insertQuery = String.Format(ConstInsertNewConcert, concertName, concertDescription, concertDateTime, ConstConcertduration, concertVenueId, performerId, (int)saveToDatabase); using (var insertConnection = new SqlConnection(ConstructConcertsConnnectString())) { 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 = new SqlConnection(ConstructConcertsConnnectString())) { getConnection.Open(); using (var reader = new SqlCommand(getCommandQuery, getConnection).ExecuteReader()) { if (reader.Read()) { concertToReturn = 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, new SqlConnection(ConstructVenuesConnectString()))) { 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(), concertToReturn.ConcertId, (50 + (5 * i++)).ToString() + ".00"); using (var ticketConnection = new SqlConnection(ConstructTicketConnectString())) { ticketConnection.Open(); using (var ticketCommand = new SqlCommand(ticketLevelInsert, ticketConnection)) { ticketCommand.ExecuteNonQuery(); } ticketConnection.Close(); ticketConnection.Dispose(); } } } } } #endregion VenuesDbContext.LogAction("Added new concert " + concertName + " for venueId " + concertVenueId); return(concertToReturn); }