public Concert SaveNewConcert(String concertName, String concertDescription, DateTime concertDateTime, ShardDbServerTargetEnum saveToDatabase, int concertVenueId, int performerId) { Concert concertToReturn = null; DataSet tempDS = new DataSet(); #region Insert string insertQuery = String.Format(CONST_InsertNewConcert, concertName, concertDescription, concertDateTime, CONST_CONCERTDURATION, 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 Insert #region Get Information string getCommandQuery = string.Format("{0} WHERE (concerts.ConcertName='{1}' AND concerts.VenueId={2} AND concerts.PerformerId={3}) {4}", CONST_GetAllConcertsQuery, concertName, concertVenueId, performerId, CONST_OrderByConcertDate); 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 Get Information #region Populate Ticket Levels int i = 1; string 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) { string 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 Populate Ticket Levels VenuesDbContext.LogAction("Added new concert " + concertName + " for venueId " + concertVenueId); return concertToReturn; }
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); }