bulk-operations-patterns

Bulk Operations Patterns

Master bulk data operations including Excel import/export, batch processing, and efficient database operations in ABP Framework applications.

When to Use This Skill

• Implementing bulk import from Excel/CSV files
• Processing large datasets with validation
• Bulk database inserts/updates with InsertManyAsync/UpdateManyAsync
• File upload handling with blob storage
• Progress tracking for long-running operations
• Export data to Excel/CSV formats

Excel Import Pattern

1. DTO for Excel Import

// Application.Contracts/{EntityPlural}/BulkImport{Entity}Dto.cs
public class BulkImportProductDto
{
    [ExcelColumnName("Product Code")]
    public string ProductCode { get; set; }

    [ExcelColumnName("Product Name")]
    public string Name { get; set; }

    [ExcelColumnName("Price")]
    public string PriceText { get; set; }  // String for validation

    [ExcelColumnName("Stock")]
    public string StockText { get; set; }

    [ExcelColumnName("Category")]
    public string CategoryName { get; set; }

    // Parsed values (not from Excel)
    [ExcelIgnore]
    public decimal Price { get; set; }

    [ExcelIgnore]
    public int Stock { get; set; }

    [ExcelIgnore]
    public Guid? CategoryId { get; set; }
}

2. File Upload DTO

// Application.Contracts/{EntityPlural}/BulkImportFileDto.cs
public class BulkImportFileDto
{
    [Required]
    public IFormFile File { get; set; }

    public bool ValidateOnly { get; set; } = false;
}

// Allowed extensions
public static class BulkImportFileExtensions
{
    public static readonly string[] Allowed = { ".xlsx", ".xls", ".csv" };
    public const long MaxFileSize = 10 * 1024 * 1024; // 10MB
}

3. AppService Implementation

public class ProductAppService : ApplicationService, IProductAppService
{
    private readonly IRepository<Product, Guid> _productRepository;
    private readonly IRepository<Category, Guid> _categoryRepository;
    private readonly IBlobContainer<BulkImportFileContainer> _fileContainer;
    private readonly ILogger<ProductAppService> _logger;

    [Authorize(ProductPermissions.Products.Import)]
    public async Task<BulkImportResultDto> BulkImportAsync(BulkImportFileDto input)
    {
        _logger.LogInformation(
            "[{Service}] BulkImportAsync - Started - FileName: {FileName}",
            nameof(ProductAppService), input.File.FileName);

        // Step 1: Validate file
        ValidateFile(input.File);

        // Step 2: Parse Excel
        var items = await ParseExcelAsync(input.File);

        if (!items.Any())
        {
            throw new UserFriendlyException("No data found in file");
        }

        // Step 3: Load reference data
        var categories = await _categoryRepository.GetListAsync();

        // Step 4: Validate all rows
        var validationErrors = await ValidateImportDataAsync(items, categories);

        if (validationErrors.Any())
        {
            return new BulkImportResultDto
            {
                IsSuccess = false,
                TotalRows = items.Count,
                ErrorCount = validationErrors.Count,
                Errors = validationErrors
            };
        }

        if (input.ValidateOnly)
        {
            return new BulkImportResultDto
            {
                IsSuccess = true,
                TotalRows = items.Count,
                Message = "Validation passed. Ready to import."
            };
        }

        // Step 5: Process import
        var result = await ProcessImportAsync(items, categories);

        _logger.LogInformation(
            "[{Service}] BulkImportAsync - Completed - Imported: {Count}",
            nameof(ProductAppService), result.SuccessCount);

        return result;
    }

    private void ValidateFile(IFormFile file)
    {
        var extension = Path.GetExtension(file.FileName).ToLowerInvariant();

        if (!BulkImportFileExtensions.Allowed.Contains(extension))
        {
            throw new UserFriendlyException(
                $"Invalid file type. Allowed: {string.Join(", ", BulkImportFileExtensions.Allowed)}");
        }

        if (file.Length > BulkImportFileExtensions.MaxFileSize)
        {
            throw new UserFriendlyException(
                $"File size exceeds maximum allowed ({BulkImportFileExtensions.MaxFileSize / 1024 / 1024}MB)");
        }
    }

    private async Task<List<BulkImportProductDto>> ParseExcelAsync(IFormFile file)
    {
        using var stream = new MemoryStream();
        await file.CopyToAsync(stream);
        stream.Position = 0;

        // Register encoding provider for older Excel formats
        System.Text.Encoding.RegisterProvider(
            System.Text.CodePagesEncodingProvider.Instance);

        using var importer = new ExcelImporter(stream);
        var sheet = importer.ReadSheet();

        return sheet.ReadRows<BulkImportProductDto>()
            .Where(x => !string.IsNullOrWhiteSpace(x.ProductCode))
            .ToList();
    }

    private async Task<List<BulkImportErrorDto>> ValidateImportDataAsync(
        List<BulkImportProductDto> items,
        List<Category> categories)
    {
        var errors = new List<BulkImportErrorDto>();
        var existingCodes = await GetExistingProductCodesAsync();

        for (int i = 0; i < items.Count; i++)
        {
            var rowNumber = i + 2; // Excel row (1-based + header)
            var item = items[i];

            // Trim inputs
            item.ProductCode = item.ProductCode?.Trim()?.ToUpperInvariant();
            item.Name = item.Name?.Trim();
            item.CategoryName = item.CategoryName?.Trim();

            // Validate required fields
            if (string.IsNullOrWhiteSpace(item.ProductCode))
            {
                errors.Add(new BulkImportErrorDto(rowNumber, "ProductCode", "Product Code is required"));
            }

            if (string.IsNullOrWhiteSpace(item.Name))
            {
                errors.Add(new BulkImportErrorDto(rowNumber, "Name", "Product Name is required"));
            }

            // Validate numeric fields
            if (!decimal.TryParse(item.PriceText, out var price) || price < 0)
            {
                errors.Add(new BulkImportErrorDto(rowNumber, "Price", $"Invalid price: {item.PriceText}"));
            }
            else
            {
                item.Price = Math.Round(price, 2);
            }

            if (!int.TryParse(item.StockText, out var stock) || stock < 0)
            {
                errors.Add(new BulkImportErrorDto(rowNumber, "Stock", $"Invalid stock: {item.StockText}"));
            }
            else
            {
                item.Stock = stock;
            }

            // Validate duplicates within file
            var duplicates = items
                .Select((x, idx) => (Item: x, Index: idx))
                .Where(x => x.Index != i &&
                    x.Item.ProductCode?.ToUpperInvariant() == item.ProductCode)
                .Select(x => $"Row {x.Index + 2}");

            if (duplicates.Any())
            {
                errors.Add(new BulkImportErrorDto(
                    rowNumber,
                    "ProductCode",
                    $"Duplicate in file: {string.Join(", ", duplicates)}"));
            }

            // Validate against existing data
            if (existingCodes.Contains(item.ProductCode))
            {
                errors.Add(new BulkImportErrorDto(
                    rowNumber,
                    "ProductCode",
                    $"Product code already exists: {item.ProductCode}"));
            }

            // Validate category
            if (!string.IsNullOrWhiteSpace(item.CategoryName))
            {
                var category = categories.FirstOrDefault(
                    c => c.Name.Equals(item.CategoryName, StringComparison.OrdinalIgnoreCase));

                if (category == null)
                {
                    errors.Add(new BulkImportErrorDto(
                        rowNumber,
                        "Category",
                        $"Category not found: {item.CategoryName}"));
                }
                else
                {
                    item.CategoryId = category.Id;
                }
            }
        }

        return errors;
    }

    private async Task<HashSet<string>> GetExistingProductCodesAsync()
    {
        var codes = await _productRepository
            .GetQueryableAsync()
            .ContinueWith(q => q.Result
                .Select(p => p.ProductCode.ToUpperInvariant())
                .ToHashSet());

        return codes;
    }

    private async Task<BulkImportResultDto> ProcessImportAsync(
        List<BulkImportProductDto> items,
        List<Category> categories)
    {
        var products = items.Select(item => new Product(
            GuidGenerator.Create(),
            item.ProductCode,
            item.Name,
            item.Price,
            item.Stock)
        {
            CategoryId = item.CategoryId
        }).ToList();

        await _productRepository.InsertManyAsync(products);

        return new BulkImportResultDto
        {
            IsSuccess = true,
            TotalRows = items.Count,
            SuccessCount = products.Count,
            Message = $"Successfully imported {products.Count} products"
        };
    }
}