public async Task <IActionResult> Post([FromBody] ScheduledFlight model)
{
int?newScheduledFlightId = null;
using (var connection = new SqlConnection(_connectionString))
{
await connection.OpenAsync();
var transaction = connection.BeginTransaction();
try
{
var insertScheduledFlightSql = @"
INSERT INTO [dbo].[ScheduledFlight]
([FlightNumber]
,[DepartureAirportId]
,[DepartureHour]
,[DepartureMinute]
,[ArrivalAirportId]
,[ArrivalHour]
,[ArrivalMinute]
,[IsSundayFlight]
,[IsMondayFlight]
,[IsTuesdayFlight]
,[IsWednesdayFlight]
,[IsThursdayFlight]
,[IsFridayFlight]
,[IsSaturdayFlight])
VALUES
(@FlightNumber
,@DepartureAirportId
,@DepartureHour
,@DepartureMinute
,@ArrivalAirportId
,@ArrivalHour
,@ArrivalMinute
,@IsSundayFlight
,@IsMondayFlight
,@IsTuesdayFlight
,@IsWednesdayFlight
,@IsThursdayFlight
,@IsFridayFlight
,@IsSaturdayFlight);
SELECT CAST(SCOPE_IDENTITY() as int)";
newScheduledFlightId = await connection.ExecuteScalarAsync <int>(insertScheduledFlightSql, model, transaction);
model.Id = newScheduledFlightId.Value;
var flights = model.GenerateFlights(DateTime.Now, DateTime.Now.AddMonths(12));
var insertFlightsSql = @"INSERT INTO [dbo].[Flight]
([ScheduledFlightId]
,[Day]
,[ScheduledDeparture]
,[ActualDeparture]
,[ScheduledArrival]
,[ActualArrival])
VALUES
(@ScheduledFlightId
,@Day
,@ScheduledDeparture
,@ActualDeparture
,@ScheduledArrival
,@ActualArrival)";
await connection.ExecuteAsync(insertFlightsSql, flights, transaction);
throw new Exception("OH NOES!");
transaction.Commit();
}
catch (Exception ex)
{
//Log the exception (ex)
try
{
transaction.Rollback();
}
catch (Exception ex2)
{
// Handle any errors that may have occurred
// on the server that would cause the rollback to fail, such as
// a closed connection.
// Log the exception ex2
}
return(StatusCode(500));
}
}
return(Ok(newScheduledFlightId));
}
