private static IList <ImportFileDto> GetImportFilesFromDatabase()
{
try
{
var files = new List <ImportFileDto>();
return(DisposableService.Using(() => new SqlConnection(cs.GetDbConnStr()), connection =>
{
return DisposableService.Using(() => new SqlCommand("dbo.uspGetImportFile", connection),
sqlCommand =>
{
sqlCommand.CommandType = CommandType.StoredProcedure;
sqlCommand.CommandTimeout = 900;
if (connection.State != ConnectionState.Open)
{
connection.Open();
}
return DisposableService.Using(sqlCommand.ExecuteReader, reader =>
{
var importFileIdOrdinal = reader.GetOrdinal("ImportFileID");
var fileNameOrdinal = reader.GetOrdinal("FileName");
var fileExtensionOrdinal = reader.GetOrdinal("FileExtension");
var fileSizeOrdinal = reader.GetOrdinal("FileSize");
var fileTypeOrdinal = reader.GetOrdinal("FileType");
var processedOrdinal = reader.GetOrdinal("Processed");
var createdOnLocalOrdinal = reader.GetOrdinal("CreatedOnLocal");
while (reader.Read())
{
var file = new ImportFileDto
{
ImportFileId = reader.GetInt32(importFileIdOrdinal),
FileName = reader.GetString(fileNameOrdinal),
FileSize = reader.GetString(fileSizeOrdinal),
FileType = reader.GetString(fileTypeOrdinal),
Processed = reader.GetBoolean(processedOrdinal),
CreatedOn = !reader.IsDBNull(createdOnLocalOrdinal) ? reader.GetDateTime(createdOnLocalOrdinal) : DateTime.Now
};
if (!reader.IsDBNull(fileExtensionOrdinal))
{
file.FileExtension = reader.GetString(fileExtensionOrdinal);
}
if (file.FileType == c.LakerFileTypeName || file.FileType == c.EnvisionFileTypeName)
{
files.Add(file);
}
}
var retList = files.ToList();
return retList;
});
});
}));
}
catch (Exception ex)
{
Logger.Error(ex);
throw;
}
}
public async Task <ActionResult> ImportDataFromFile(ImportFileDto file)
{
var listOfImportedGroups = await _importService.ImportFileAsync(file);
return(listOfImportedGroups.ToActionResult());
}
public async Task <Result <List <StudentGroupFileModel> > > ImportFileAsync(ImportFileDto file)
{
var wb = new XLWorkbook(file.url);
var wsGroups = wb.Worksheet("Groups");
List <StudentGroupFileModel> importedGroups = new List <StudentGroupFileModel>();
Type sgType = typeof(StudentGroupFileModel);
char charPointer = 'A';
int rowCounter = 2;
var properties = sgType.GetProperties();
foreach (PropertyInfo property in properties)
{
if (property.Name != Convert.ToString(wsGroups.Cell($"{charPointer}1").Value))
{
return(Result <List <StudentGroupFileModel> > .GetError(ErrorCode.ValidationError,
"The format of the downloaded file is not suitable."
+ "Check headers in the file."));
}
charPointer++;
}
while (!IsEndOfFile(rowCounter, wsGroups))
{
try
{
StudentGroupFileModel fileLine = new StudentGroupFileModel();
fileLine.CourseId = wsGroups.Cell($"B{rowCounter}").Value.ToString();
fileLine.Name = wsGroups.Cell($"C{rowCounter}").Value.ToString();
fileLine.StartDate = Convert
.ToDateTime(wsGroups.Cell($"D{rowCounter}").Value);
fileLine.FinishDate = Convert
.ToDateTime(wsGroups.Cell($"E{rowCounter}").Value);
await IsValueValid(fileLine, rowCounter);
StudentGroup group = new StudentGroup
{
CourseId = Convert.ToInt32(fileLine.CourseId),
Name = fileLine.Name,
StartDate = fileLine.StartDate,
FinishDate = fileLine.FinishDate,
};
importedGroups.Add(fileLine);
_unitOfWork.StudentGroupRepository.Add(group);
rowCounter++;
}
catch (FormatException ex)
{
_unitOfWork.Rollback();
return(Result <List <StudentGroupFileModel> > .GetError(ErrorCode.ValidationError,
"The format of the inputed data is incorrect.\n" + ex.Message));
}
catch (DbUpdateException ex)
{
_unitOfWork.Rollback();
return(Result <List <StudentGroupFileModel> > .GetError(ErrorCode.ValidationError,
"Inputed data is incorrect.\n" + ex.Message));
}
}
await _unitOfWork.CommitAsync();
return(Result <List <StudentGroupFileModel> >
.GetSuccess(_mapper.Map <List <StudentGroupFileModel> >(importedGroups)));
}