internal async Task <bool> CreateNew(CreateWorkShopModel workshop)
{
using (var conn = new MySqlConnection(GetConnectionString()))
{
MySqlCommand myCommand = conn.CreateCommand();
MySqlTransaction myTrans;
// Start a local transaction
await conn.OpenAsync();
myTrans = conn.BeginTransaction();
// Must assign both transaction object and connection
// to Command object for a pending local transaction
myCommand.Connection = conn;
myCommand.Transaction = myTrans;
try
{
myCommand.CommandText = "INSERT INTO vehicles (serial, model, brand, year, created_at) VALUES (@serial , @model, @brand, @year, NOW())";
myCommand.Parameters.AddWithValue("@serial", workshop.vehicle.serial);
myCommand.Parameters.AddWithValue("@model", workshop.vehicle.model);
myCommand.Parameters.AddWithValue("@brand", workshop.vehicle.brand);
myCommand.Parameters.AddWithValue("@year", workshop.vehicle.year);
await myCommand.ExecuteNonQueryAsync();
long vehicleId = myCommand.LastInsertedId;
myCommand.CommandText = "INSERT INTO workshop_records (client_id, workshop_record_state_id, vehicle_id) VALUES (@client_id, @workshop_record_state_id, @vehicle_id)";
myCommand.Parameters.AddWithValue("@client_id", workshop.client_id);
myCommand.Parameters.AddWithValue("@workshop_record_state_id", workshop.workshop_record_sate_id);
myCommand.Parameters.AddWithValue("@vehicle_id", vehicleId);
await myCommand.ExecuteNonQueryAsync();
long workshopRecordId = myCommand.LastInsertedId;
myCommand.CommandText = "INSERT INTO tasks (description, workshop_records_id, task_states_id) VALUES (@description, @workshop_records_id, @task_states_id)";
myCommand.Parameters.AddWithValue("@description", "");
myCommand.Parameters.AddWithValue("@workshop_records_id", 0);
myCommand.Parameters.AddWithValue("@task_states_id", 0);
foreach (var task in workshop.tasks)
{
myCommand.Parameters["@description"].Value = task.description;
myCommand.Parameters["@workshop_records_id"].Value = workshopRecordId;
myCommand.Parameters["@task_states_id"].Value = 1;
await myCommand.ExecuteNonQueryAsync();
}
myTrans.Commit();
Console.WriteLine("Both records are written to database.");
return(true);
}
catch (Exception e)
{
try
{
myTrans.Rollback();
}
catch (MySqlException ex)
{
if (myTrans.Connection != null)
{
Console.WriteLine("An exception of type " + ex.GetType() +
" was encountered while attempting to roll back the transaction.");
Console.WriteLine(ex);
return(false);
}
}
Console.WriteLine("An exception of type " + e.GetType() +
" was encountered while inserting the data.");
Console.WriteLine("Neither record was written to database.");
Console.WriteLine(e);
return(false);
}
finally
{
conn.Close();
}
}
}
public async Task <IActionResult> Post([FromBody] CreateWorkShopModel workshop)
{
var result = workshops.CreateNew(workshop);
return(Ok(result));
}
