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);
}
