public Concert GetConcertById(int concertId) { Concert concertToReturn = null; var dbConnectionString = ConstructConcertsConnnectString(); using (var dbConnection = new SqlConnection(dbConnectionString)) { 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()) { concertToReturn = PopulateSingleConcertFromDbReader(reader); } } finally { reader.Close(); } } } finally { dbConnection.Close(); } } return(concertToReturn); }
private Concert populateSingleConcertFromDbReader(SqlDataReader dbReader) { try { var concertPerformer = new Performer { PerformerId = dbReader.GetInt32(dbReader.GetOrdinal(@"performerId")), FirstName = dbReader.GetString(dbReader.GetOrdinal(@"performerFirstName")), LastName = dbReader.GetString(dbReader.GetOrdinal(@"performerLastName")), ShortName = dbReader.GetString(dbReader.GetOrdinal(@"performerShortName")) }; var concertToReturn = new Concert { ConcertId = dbReader.GetInt32(dbReader.GetOrdinal(@"concertId")), ConcertDate = dbReader.GetDateTime(dbReader.GetOrdinal(@"concertDate")), ConcertName = dbReader.GetString(dbReader.GetOrdinal(@"concertName")), VenueId = dbReader.GetInt32(dbReader.GetOrdinal(@"venueId")), Description = dbReader.GetString(dbReader.GetOrdinal(@"concertDescription")), PerformerId = dbReader.GetInt32(dbReader.GetOrdinal(@"concertPerformerId")), Performer = concertPerformer, SaveToDbServer = (dbReader.IsDBNull(dbReader.GetOrdinal(@"saveToDatabase"))) ? ((int)ShardDbServerTargetEnum.Primary) : ((ShardDbServerTargetEnum)dbReader.GetInt32(dbReader.GetOrdinal(@"saveToDatabase"))) }; return concertToReturn; } catch { } return null; }
private static Concert PrepareData(ref int artistId, ref int cityId, ref int venueId, int eventId, List<Concert> eventList, List<Performer> artistList) { #region - Prepare selections - if (artistId == 0) { artistId = -1; } if (cityId == 0) { cityId = -1; } if (venueId == 0) { venueId = -1; } #endregion var selectedConcert = new Concert(); if (eventId > 0 && eventList.Any(a => a.ConcertId == eventId)) { selectedConcert = eventList.First(a => a.ConcertId == eventId); artistId = selectedConcert.PerformerId; artistList.RemoveAll(a => a.PerformerId != selectedConcert.PerformerId); } else if (eventId == 0 || eventId == -1) { selectedConcert.ConcertId = -1; } return selectedConcert; }
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); }