public IActionResult PostCsv([FromForm] CsvUploadDTO csvUpload)
{
try
{
if (csvUpload.File.Length <= 0)
{
return(StatusCode(400, "Invalid .csv file"));
}
if (csvUpload.DeleteAndReplace == null)
{
return(StatusCode(400, "DeleteAndReplace boolean must be included as part of the request"));
}
using var reader = new StreamReader(csvUpload.File.OpenReadStream());
using var csv = new CsvReader(reader, CultureInfo.CreateSpecificCulture("en-GB"));
csv.Configuration.RegisterClassMap <ServiceCsvDTOmap>();
var services = csv.GetRecords <ServiceCsvDTO>();
if (csvUpload.DeleteAndReplace == true)
{
_context.ServiceTable.RemoveRange(_context.ServiceTable.ToList());
_context.SaveChanges();
}
foreach (ServiceCsvDTO service in services)
{
var exists = _context.ServiceTable.AsNoTracking().FirstOrDefault(s => s.ServiceId == service.ServiceId);
if (csvUpload.DeleteAndReplace == false && exists != null)
{
_context.ServiceTable.Update(
new ServiceTable
{
ServiceId = service.ServiceId,
ServiceName = service.ServiceName,
ServiceDescription = service.ServiceDescription,
ServiceOwner_RoleId = service.ServiceOwner_RoleId
}
);
}
else
{
_context.ServiceTable.Add(
new ServiceTable
{
ServiceId = service.ServiceId,
ServiceName = service.ServiceName,
ServiceDescription = service.ServiceDescription,
ServiceOwner_RoleId = service.ServiceOwner_RoleId
}
);
}
}
_context.SaveChanges();
return(StatusCode(201));
}
catch (DbUpdateException ex)
{
_logger.LogDebug(ex.ToString());
if (ex.InnerException != null)
{
if (ex.InnerException is SqlException sqlEx)
{
return(sqlEx.Number switch
{
// Constraint check violation
547 => StatusCode(400, "Constraint check violation (a chosen ServiceOwner_RoleId probably doesn't exist, or a PrivTable entry relies on the ServiceId you are amending/deleting): " + sqlEx.Message.ToString()),
// Duplicated key row error / Constraint violation exception
2601 => StatusCode(400, "Duplicate key or constraint violation: " + sqlEx.Message.ToString()),
// Unique constraint error
2627 => StatusCode(400, "Unique constraint error (a chosen ServiceId probably already exists): " + sqlEx.Message.ToString()),
_ => StatusCode(400, "An unexpected error occured: " + sqlEx.Message.ToString()),
});
public IActionResult PostCsv([FromForm] CsvUploadDTO csvUpload)
{
try
{
if (csvUpload.File.Length <= 0)
{
return(StatusCode(400, "Invalid .csv file"));
}
if (csvUpload.DeleteAndReplace == null)
{
return(StatusCode(400, "DeleteAndReplace boolean must be included as part of the request"));
}
using var reader = new StreamReader(csvUpload.File.OpenReadStream());
using var csv = new CsvReader(reader, CultureInfo.CreateSpecificCulture("en-GB"));
csv.Configuration.RegisterClassMap <UserCsvDTOmap>();
var users = csv.GetRecords <UserCsvDTO>();
if (csvUpload.DeleteAndReplace == true)
{
_context.UserTable.RemoveRange(_context.UserTable.ToList());
_context.SaveChanges();
}
foreach (UserCsvDTO user in users)
{
var exists = _context.UserTable.AsNoTracking().FirstOrDefault(u => u.UserId == user.UserId);
if (csvUpload.DeleteAndReplace == false && exists != null)
{
_context.UserTable.Update(
new UserTable
{
UserId = user.UserId,
UserFullName = user.UserFullName,
RoleId = user.RoleId,
LastCertifiedBy = user.LastCertifiedBy,
LastCertifiedDate = user.LastCertifiedDate
}
);
}
else
{
_context.UserTable.Add(
new UserTable
{
UserId = user.UserId,
UserFullName = user.UserFullName,
RoleId = user.RoleId,
LastCertifiedBy = user.LastCertifiedBy,
LastCertifiedDate = user.LastCertifiedDate
}
);
}
}
_context.SaveChanges();
return(StatusCode(201));
}
catch (DbUpdateException ex)
{
_logger.LogDebug(ex.ToString());
if (ex.InnerException != null)
{
if (ex.InnerException is SqlException sqlEx)
{
return(sqlEx.Number switch
{
// Constraint check violation
547 => StatusCode(400, "Constraint check violation (a chosen RoleId probably doesn't exist): " + sqlEx.Message.ToString()),
// Duplicated key row error / Constraint violation exception
2601 => StatusCode(400, "Duplicate key or constraint violation: " + sqlEx.Message.ToString()),
// Unique constraint error
2627 => StatusCode(400, "Unique constraint error (a chosen UserId probably already exists): " + sqlEx.Message.ToString()),
_ => StatusCode(400, "An unexpected error occured: " + sqlEx.Message.ToString()),
});
