Пример #1
0
 /// <summary>
 ///
 /// </summary>
 /// <typeparam name="TEntity">The type of EF Entity</typeparam>
 /// <param name="matchingData">Specification for how to match spreadsheet to entity</param>
 /// <param name="saveBehavior">Optional configuration to change the save behavior. See ImportSaveBehavior</param>
 /// <returns></returns>
 public async Task <ImportResult> ImportColumnData <TEntity>(DataMatchesForImport matchingData, ImportSaveBehavior saveBehavior = null)
     where TEntity : class, new()
 {
     return(await ImportColumnData <TEntity, string>(matchingData, null, null, null, saveBehavior));
 }
Пример #2
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TEntity">The type of EF Entity</typeparam>
        /// <typeparam name="TId">Type of the item to use for finding</typeparam>
        /// <param name="matchingData">Specification for how to match spreadsheet to entity</param>
        /// <param name="finder">A func to look for an existing copy of the entity id. default will use "find". Runs against the DB via EF.</param>
        /// <param name="idPropertyName">The unique identifier property. Leave blank unless you are using an overrider to use something other than the real key.</param>
        /// <param name="overridingMapper">A custom mapper for mapping between excel columns and an entity. </param>
        /// <param name="saveBehavior">Optional configuration to change the save behavior. See ImportSaveBehavior</param>
        /// <param name="validator">Optional method to run custom validation on the modified entity before saving</param>
        /// <returns></returns>
        public async Task <ImportResult> ImportColumnData <TEntity, TId>(DataMatchesForImport matchingData, Func <TId, Expression <Func <TEntity, bool> > > finder = null, string idPropertyName = null, UpdatePropertyOverrider <TEntity> overridingMapper = null, ImportSaveBehavior saveBehavior = null, IEntityValidator <TEntity> validator = null)
            where TEntity : class, new()
        {
            if (saveBehavior == null)
            {
                saveBehavior = new ImportSaveBehavior();
            }

            var selctedDict = BuildDictionaryFromSelected(matchingData.Selected);

            var keyInfo = GetEntityKeys(typeof(TEntity));

            EnsureImportingEntityHasSingleKey(keyInfo);
            var  pk     = keyInfo[0];
            Type idType = ((PrimitiveType)pk.TypeUsage.EdmType).ClrEquivalentType;


            if (idPropertyName == null)
            {
                idPropertyName = pk.Name;
            }

            var isImportingEntityId  = selctedDict.ContainsKey(idPropertyName);
            var isAutoIncrementingId = IsIdAutoIncrementing(typeof(TEntity));

            EnsureNoIdColumnIncludedWhenCreatingAutoIncrementEntites(saveBehavior.RecordMode, isAutoIncrementingId, isImportingEntityId);

            var importResult = new ImportResult {
                RowErrorDetails = new Dictionary <string, string>()
            };

            var excelRows = await GetExcelRows(matchingData);

            var foundErrors = false;

            for (var index = 0; index < excelRows.Count; index++)
            {
                var     excelRow       = excelRows[index];
                var     rowNumber      = index + 2; // add 2 to reach the first data row because the first row is a header, excel row numbers start with 1 not 0
                TEntity entityToUpdate = null;
                try
                {
                    if (ExcelRowIsBlank(excelRow))
                    {
                        continue;
                    }

                    string idValue = null;
                    if (isImportingEntityId)
                    {
                        var xlsxIdColName = selctedDict[idPropertyName];
                        var idStringValue = excelRow[xlsxIdColName];
                        entityToUpdate = await GetMatchedDbObject(finder, idStringValue, idType);

                        ValidateDbResult(entityToUpdate, saveBehavior.RecordMode, xlsxIdColName, idStringValue);
                    }

                    if (entityToUpdate == null)
                    {
                        EnsureNoEntityCreationWithIdWhenAutoIncrementIdType(idPropertyName, isAutoIncrementingId, idValue);
                        entityToUpdate = new TEntity();
                        _dbContext.Set <TEntity>().Add(entityToUpdate);
                    }

                    await MapIntoEntity(selctedDict, idPropertyName, overridingMapper, entityToUpdate, excelRow, isAutoIncrementingId, saveBehavior.RecordMode);

                    if (validator != null)
                    {
                        var errors = validator.GetValidationErrors(entityToUpdate);

                        if (errors.Any())
                        {
                            throw new RowInvalidException(errors);
                        }
                    }
                    else
                    {
                        importResult.SuccessCount++;
                    }
                }
                catch (RowParseException e)
                {
                    HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Error: " + e.Message);
                    foundErrors = true;
                }
                catch (RowInvalidException e)
                {
                    HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Error: " + e.Message);
                    foundErrors = true;
                }
                catch (Exception)
                {
                    HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Cannot be updated - error importing");
                    foundErrors = true;
                }

                if (saveBehavior.CommitMode == CommitMode.AnySuccessfulOneAtATime)
                {
                    await _dbContext.SaveChangesAsync();
                }
            }

            if ((saveBehavior.CommitMode == CommitMode.AnySuccessfulAtEndAsBulk) ||
                (saveBehavior.CommitMode == CommitMode.CommitAllAtEndIfAllGoodOrRejectAll && !foundErrors))
            {
                await _dbContext.SaveChangesAsync();
            }

            return(importResult);
        }
Пример #3
0
        /// <summary>
        ///
        /// </summary>
        /// <typeparam name="TEntity">The type of EF Entity</typeparam>
        /// <typeparam name="TId">Type of the item to use for finding</typeparam>
        /// <param name="matchingData">Specification for how to match spreadsheet to entity</param>
        /// <param name="finder">A func to look for an existing copy of the entity id. default will use "find". Runs against the DB via EF.</param>
        /// <param name="idPropertyName">The unique identifier property. Leave blank unless you are using an overrider to use something other than the real key.</param>
        /// <param name="overridingMapper">A custom mapper for mapping between excel columns and an entity. </param>
        /// <param name="saveBehavior">Optional configuration to change the save behavior. See ImportSaveBehavior</param>
        /// <param name="validator">Optional method to run custom validation on the modified entity before saving</param>
        /// <returns></returns>
        public async Task <ImportResult> ImportColumnData <TEntity, TId>(DataMatchesForImport matchingData, Func <TId, Expression <Func <TEntity, bool> > > finder = null, string idPropertyName = null, UpdatePropertyOverrider <TEntity> overridingMapper = null, ImportSaveBehavior saveBehavior = null, IEntityValidator <TEntity> validator = null)
            where TEntity : class, new()
        {
            if (saveBehavior == null)
            {
                saveBehavior = new ImportSaveBehavior();
            }

            var selctedDict = BuildDictionaryFromSelected(matchingData.Selected);

            var keyInfo = GetEntityKeys(typeof(TEntity));

            EnsureImportingEntityHasSingleKey(keyInfo);
            var pk     = keyInfo[0];
            var idType = ((PrimitiveType)pk.TypeUsage.EdmType).ClrEquivalentType;

            if (idPropertyName == null)
            {
                idPropertyName = pk.Name;
            }
            var tableObj             = GetTable(typeof(TEntity));
            var tableName            = $"[{tableObj.Schema}].[{tableObj.Table}]";
            var isImportingEntityId  = selctedDict.ContainsKey(idPropertyName);
            var isAutoIncrementingId = IsIdAutoIncrementing(typeof(TEntity));

            EnsureNoIdColumnIncludedWhenCreatingAutoIncrementEntites(saveBehavior.RecordMode, isAutoIncrementingId, isImportingEntityId);

            var importResult = new ImportResult {
                RowErrorDetails = new Dictionary <string, string>()
            };
            var filePath  = matchingData.FilePath != null ? (Path.GetTempPath() + matchingData.FileName) : matchingData.FileName;
            var excelRows = await _excelIoWrapper.GetRows(filePath, matchingData.Sheet);

            var foundErrors = false;

            if (saveBehavior.RecordMode == RecordMode.CreateOrIgnore && saveBehavior.CommitMode == CommitMode.Bulk)
            {
                _dbContext.Configuration.AutoDetectChangesEnabled = false;
                _dbContext.Configuration.ValidateOnSaveEnabled    = false;
            }
            var genericListType = typeof(List <>).MakeGenericType(idType);
            var rowIds          = new List <object>();
            var existingIds     = (IList)Activator.CreateInstance(genericListType);

            if (isImportingEntityId)
            {
                var xlsxIdColName = selctedDict[idPropertyName];

                rowIds.AddRange(excelRows.Select(excelRow => excelRow[xlsxIdColName])
                                .Where(x => !string.IsNullOrEmpty(x)).Select(idStringValue => StringToTypeConverter.Convert(idStringValue, idType)));

                if (rowIds.Count > 0)
                {
                    var sqlQuery = $"Select [{idPropertyName}] from {tableName}";
                    var dbIds    = await _dbContext.Database.SqlQuery(idType, sqlQuery).ToListAsync();

                    var ids = from id in dbIds
                              join rowId in rowIds on
                              id equals rowId
                              select id;

                    existingIds = ids.ToList();
                }
            }

            Task task            = null;
            var  pendingEntities = new List <TEntity>();

            for (var index = 0; index < excelRows.Count; index++)
            {
                var     excelRow       = excelRows[index];
                var     rowNumber      = index + 2; // add 2 to reach the first data row because the first row is a header, excel row numbers start with 1 not 0
                TEntity entityToUpdate = null;
                try
                {
                    if (saveBehavior.checkForEmptyRows && ExcelRowIsBlank(excelRow))
                    {
                        continue;
                    }

                    if (isImportingEntityId)
                    {
                        var xlsxIdColName = selctedDict[idPropertyName];
                        var idStringValue = excelRow[xlsxIdColName];
                        var id            = rowIds[index];

                        if (saveBehavior.RecordMode == RecordMode.CreateOrIgnore && (string.IsNullOrEmpty(idStringValue) || existingIds.Contains(id)))
                        {
                            continue;
                        }

                        if (saveBehavior.RecordMode != RecordMode.CreateOrIgnore)
                        {
                            entityToUpdate = await GetMatchedDbObject(finder, idStringValue, idType);

                            ValidateDbResult(entityToUpdate, saveBehavior.RecordMode, xlsxIdColName, idStringValue);
                        }
                        else
                        {
                            existingIds.Add(id);
                        }
                    }

                    if (entityToUpdate == null)
                    {
                        entityToUpdate = new TEntity();
                        if (saveBehavior.CommitMode == CommitMode.Bulk)
                        {
                            pendingEntities.Add(entityToUpdate);
                        }
                        else
                        {
                            var xlsxIdColName = selctedDict[idPropertyName];
                            var idValue       = excelRow[xlsxIdColName];
                            EnsureNoEntityCreationWithIdWhenAutoIncrementIdType(idPropertyName, isAutoIncrementingId, idValue);
                            _dbContext.Set <TEntity>().Add(entityToUpdate);
                        }
                    }

                    await MapIntoEntity(selctedDict, idPropertyName, overridingMapper, entityToUpdate, excelRow, isAutoIncrementingId, saveBehavior.RecordMode);

                    if (validator != null)
                    {
                        var errors = validator.GetValidationErrors(entityToUpdate);
                        throw new RowInvalidException(errors);
                    }
                    else
                    {
                        importResult.SuccessCount++;
                    }
                }
                catch (RowParseException e)
                {
                    HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Error: " + e.Message);
                    foundErrors = true;
                }
                catch (RowInvalidException e)
                {
                    HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Error: " + e.Message);
                    foundErrors = true;
                }
                catch (Exception)
                {
                    HandleError(importResult.RowErrorDetails, rowNumber, entityToUpdate, "Cannot be updated - error importing");
                    foundErrors = true;
                }

                if (saveBehavior.CommitMode == CommitMode.AnySuccessfulOneAtATime)
                {
                    await _dbContext.SaveChangesAsync();
                }

                if (saveBehavior.CommitMode == CommitMode.Bulk && (index % 100 == 0 || index == excelRows.Count - 1))
                {
                    if (task != null)
                    {
                        await task;
                        task = null;
                    }
                    _dbContext.Set <TEntity>().AddRange(pendingEntities);
                    task = _dbContext.BulkSaveChangesAsync(b => b.BatchSize = 50);
                    pendingEntities.Clear();
                }
            }

            if (task != null)
            {
                await task;
            }

            if (pendingEntities.Count > 0)
            {
                _dbContext.Set <TEntity>().AddRange(pendingEntities);
            }

            _dbContext.Configuration.AutoDetectChangesEnabled = true;
            _dbContext.Configuration.ValidateOnSaveEnabled    = true;

            if ((saveBehavior.CommitMode == CommitMode.AnySuccessfulAtEndAsBulk) ||
                (saveBehavior.CommitMode == CommitMode.CommitAllAtEndIfAllGoodOrRejectAll && !foundErrors) ||
                (saveBehavior.CommitMode == CommitMode.Bulk))
            {
                await _dbContext.BulkSaveChangesAsync(b => b.BatchSize = 50);
            }

            return(importResult);
        }