Beispiel #1
0
        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;
            }
        }
Beispiel #2
0
        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)));
        }