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"
};
}
}4. Result DTOs
public class BulkImportResultDto
{
public bool IsSuccess { get; set; }
public int TotalRows { get; set; }
public int SuccessCount { get; set; }
public int ErrorCount { get; set; }
public string Message { get; set; }
public List<BulkImportErrorDto> Errors { get; set; } = new();
}
public class BulkImportErrorDto
{
public int RowNumber { get; set; }
public string Field { get; set; }
public string Message { get; set; }
public BulkImportErrorDto() { }
public BulkImportErrorDto(int rowNumber, string field, string message)
{
RowNumber = rowNumber;
Field = field;
Message = message;
}
}Bulk Update Pattern
1. Bulk Update from Excel
public async Task<BulkImportResultDto> BulkUpdateAsync(BulkImportFileDto input)
{
_logger.LogInformation("[{Service}] BulkUpdateAsync - Started", nameof(ProductAppService));
ValidateFile(input.File);
var items = await ParseExcelAsync(input.File);
// Load existing products
var productCodes = items
.Select(x => x.ProductCode?.Trim()?.ToUpperInvariant())
.Where(x => !string.IsNullOrEmpty(x))
.ToList();
var existingProducts = await _productRepository
.GetListAsync(p => productCodes.Contains(p.ProductCode.ToUpper()));
var errors = new List<BulkImportErrorDto>();
var productsToUpdate = new List<Product>();
foreach (var (item, index) in items.Select((x, i) => (x, i)))
{
var rowNumber = index + 2;
item.ProductCode = item.ProductCode?.Trim()?.ToUpperInvariant();
var product = existingProducts.FirstOrDefault(
p => p.ProductCode.ToUpperInvariant() == item.ProductCode);
if (product == null)
{
errors.Add(new BulkImportErrorDto(
rowNumber, "ProductCode", $"Product not found: {item.ProductCode}"));
continue;
}
// Update fields if provided
if (!string.IsNullOrWhiteSpace(item.Name))
{
product.SetName(item.Name.Trim());
}
if (decimal.TryParse(item.PriceText, out var price))
{
product.SetPrice(Math.Round(price, 2));
}
if (int.TryParse(item.StockText, out var stock))
{
product.SetStock(stock);
}
productsToUpdate.Add(product);
}
if (errors.Any())
{
return new BulkImportResultDto
{
IsSuccess = false,
TotalRows = items.Count,
ErrorCount = errors.Count,
Errors = errors
};
}
if (productsToUpdate.Any())
{
await _productRepository.UpdateManyAsync(productsToUpdate);
}
_logger.LogInformation(
"[{Service}] BulkUpdateAsync - Completed - Updated: {Count}",
nameof(ProductAppService), productsToUpdate.Count);
return new BulkImportResultDto
{
IsSuccess = true,
TotalRows = items.Count,
SuccessCount = productsToUpdate.Count,
Message = $"Successfully updated {productsToUpdate.Count} products"
};
}2. Bulk Update from List
public async Task<BulkImportResultDto> BulkUpdateFromListAsync(
List<UpdateProductDto> items)
{
var ids = items.Select(x => x.Id).ToList();
var products = await _productRepository
.GetListAsync(p => ids.Contains(p.Id));
var productsToUpdate = (
from product in products
join item in items on product.Id equals item.Id
select UpdateProduct(product, item)
).ToList();
await _productRepository.UpdateManyAsync(productsToUpdate);
return new BulkImportResultDto
{
IsSuccess = true,
SuccessCount = productsToUpdate.Count
};
}
private Product UpdateProduct(Product product, UpdateProductDto input)
{
product.SetName(input.Name?.Trim() ?? product.Name);
product.SetPrice(input.Price ?? product.Price);
product.SetStock(input.Stock ?? product.Stock);
return product;
}Excel Export Pattern
public async Task<FileDto> ExportToExcelAsync(ProductFilter filter)
{
_logger.LogInformation("[{Service}] ExportToExcelAsync - Started", nameof(ProductAppService));
var products = await GetFilteredProductsAsync(filter);
var exportData = products.Select(p => new ProductExportDto
{
ProductCode = p.ProductCode,
Name = p.Name,
Price = p.Price,
Stock = p.Stock,
CategoryName = p.Category?.Name,
CreatedAt = p.CreationTime
}).ToList();
using var stream = new MemoryStream();
// Using ExcelMapper for export
var excel = new ExcelMapper();
excel.Save(stream, exportData, "Products");
var fileName = $"Products_{DateTime.Now:yyyyMMdd_HHmmss}.xlsx";
// Save to blob storage
await _fileContainer.SaveAsync(fileName, stream.ToArray());
return new FileDto
{
FileName = fileName,
Content = stream.ToArray(),
ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
};
}Blob Storage for Templates
// File container definition
[BlobContainerName("bulk-import-templates")]
public class BulkImportTemplateContainer { }
// Get template
public async Task<FileDto> GetImportTemplateAsync()
{
const string templateFileName = "ProductImportTemplate.xlsx";
var content = await _fileContainer.GetAllBytesAsync(templateFileName);
if (content == null)
{
throw new UserFriendlyException("Import template not found");
}
return new FileDto
{
FileName = templateFileName,
Content = content,
ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
};
}Batch Processing with Progress
public async Task<BulkImportResultDto> ProcessLargeBatchAsync(
List<CreateProductDto> items,
IProgress<int> progress = null)
{
const int batchSize = 100;
var batches = items.Chunk(batchSize).ToList();
var totalProcessed = 0;
var errors = new List<BulkImportErrorDto>();
foreach (var (batch, batchIndex) in batches.Select((b, i) => (b, i)))
{
try
{
var products = batch.Select(item => new Product(
GuidGenerator.Create(),
item.ProductCode,
item.Name,
item.Price,
item.Stock
)).ToList();
await _productRepository.InsertManyAsync(products);
totalProcessed += batch.Length;
progress?.Report((int)((double)totalProcessed / items.Count * 100));
_logger.LogInformation(
"Processed batch {BatchIndex}/{TotalBatches}, Total: {Total}",
batchIndex + 1, batches.Count, totalProcessed);
}
catch (Exception ex)
{
_logger.LogError(ex, "Failed to process batch {BatchIndex}", batchIndex);
errors.Add(new BulkImportErrorDto(
batchIndex * batchSize,
"Batch",
$"Batch {batchIndex + 1} failed: {ex.Message}"));
}
}
return new BulkImportResultDto
{
IsSuccess = !errors.Any(),
TotalRows = items.Count,
SuccessCount = totalProcessed,
ErrorCount = errors.Count,
Errors = errors
};
}Controller Implementation
[Route("api/products")]
public class ProductController : AbpController
{
private readonly IProductAppService _productAppService;
[HttpPost("bulk-import")]
[Consumes("multipart/form-data")]
public async Task<BulkImportResultDto> BulkImportAsync(
[FromForm] BulkImportFileDto input)
{
return await _productAppService.BulkImportAsync(input);
}
[HttpPost("bulk-update")]
[Consumes("multipart/form-data")]
public async Task<BulkImportResultDto> BulkUpdateAsync(
[FromForm] BulkImportFileDto input)
{
return await _productAppService.BulkUpdateAsync(input);
}
[HttpGet("export")]
public async Task<IActionResult> ExportAsync([FromQuery] ProductFilter filter)
{
var file = await _productAppService.ExportToExcelAsync(filter);
return File(file.Content, file.ContentType, file.FileName);
}
[HttpGet("import-template")]
public async Task<IActionResult> GetTemplateAsync()
{
var file = await _productAppService.GetImportTemplateAsync();
return File(file.Content, file.ContentType, file.FileName);
}
}Best Practices
- Always validate first - Parse and validate all rows before any database operations
- Use transactions - ABP's UoW handles this automatically for
InsertManyAsync/UpdateManyAsync - Batch large operations - Process in chunks of 100-500 records
- Report progress - Use
IProgress<T>for long-running operations - Log comprehensively - Log start, end, batch progress, and errors
- Provide templates - Store import templates in blob storage
- Validate duplicates - Check both within file and against existing data
- Trim inputs - Always trim and normalize string inputs
- Handle encoding - Register
CodePagesEncodingProviderfor older Excel formats - Return detailed errors - Include row number and field for each validation error