C#上传excel，解析主从表，1W数据快速插入数据库，5s完成

参考文章
net core天马行空系列-各大数据库快速批量插入数据方法汇总
ExcelMapper

Controller核心代码

[HttpPost]
public async Task<IActionResult> ImportToDoItems(IFormFile file)
{
    if (file == null || file.Length == 0)
    {
        return BadRequest("File is empty");
    }

    using var stream = new MemoryStream();
    using (MiniProfiler.Current.Step("ToStram"))
    {
        await file.CopyToAsync(stream);
        stream.Position = 0;
    }

    IEnumerable<ImportToDoItemModel> importModels;
    using (MiniProfiler.Current.Step("Convert"))
    {
        //解析文件为强类型集合
        importModels = ExcelMapperConvertor.Convert<ImportToDoItemModel, ImportToDoItemModelValidator>(stream);
    }

    //插入数据库
    using (MiniProfiler.Current.Step("DataBase"))
    {
        //导入的主表名称
        var importMasterNames = importModels.Select(x => x.Name).Distinct();
        //数据库中存在的主表
        var existMasters = await _dbContext.Set<ToDoMaster>().Where(x => importMasterNames.Contains(x.Name)).ToListAsync();
        //数据库中存在的主表名称
        var existMasterNames = existMasters.Select(x => x.Name);
        //需要插入的主表名称（数据库中不存在）
        var insertMasterNames = importMasterNames.Where(x => !existMasterNames.Contains(x));

        //插入主表,直接用dbContext插入
        var insertMasters = insertMasterNames
            .Select(name => new ToDoMaster()
            {
                Id = YitIdInitHelper.NextId(),
                Name = name
            });
        await _dbContext.AddRangeAsync(insertMasters);

        //插入从表，从表用SqlBulkCopy
        var creationTime = DateTime.Now;
        var insertToDoItems = importModels
            .Select(x => new ToDoItem()
            {
                Id = YitIdInitHelper.NextId(),
                ToDoMasterId = allMasterNames[x.Name].Id,
                Text = x.Text,
                Count = x.Count,
                IsDeleted = false,
                CreationTime = creationTime,
            });

        var connectionString = "Server=localhost; Database=MyABP7NET6Db; Trusted_Connection=True;TrustServerCertificate=True;Integrated Security=True;";
        using (var dbConnection = new SqlConnection(connectionString))
        {
            dbConnection.Open();

            using var sqlBulkCopy = new SqlBulkCopy(dbConnection, SqlBulkCopyOptions.KeepIdentity, null);
            sqlBulkCopy.BatchSize = 20000;
            //表名
            sqlBulkCopy.DestinationTableName = "ToDoItems_202408";
            //针对列名做一下映射
            sqlBulkCopy.ColumnMappings.Add("Id", "Id");
            sqlBulkCopy.ColumnMappings.Add("ToDoMasterId", "ToDoMasterId");
            sqlBulkCopy.ColumnMappings.Add("Text", "Text");
            sqlBulkCopy.ColumnMappings.Add("Count", "Count");
            sqlBulkCopy.ColumnMappings.Add("IsDeleted", "IsDeleted");
            sqlBulkCopy.ColumnMappings.Add("CreationTime", "CreationTime");
            //将实体类列表转换成dataTable
            var table = insertToDoItems.ToDataTable();
            sqlBulkCopy.WriteToServer(table);
        }
        //await _dbContext.AddRangeAsync(insertToDoItems);

        await _dbContext.SaveChangesAsync();
    }

    return Ok(new object[] { importModels.Count() });
}

MiniProfile监控数据

浏览器监控数据

Model及其校验类

public class ImportToDoItemModel
{
    // 主表字段
    public string Name { get; set; }

    // 从表字段
    public string Text { get; set; }

    // 从表字段
    public int Count { get; set; }
}

public class ImportToDoItemModelValidator : AbstractValidator<ImportToDoItemModel>
{
    public ImportToDoItemModelValidator()
    {
        RuleFor(x => x.Name).NotEmpty();
        RuleFor(x => x.Count).ExclusiveBetween(0, 10001).WithMessage("Count 不符合要求");
    }
}

ExcelMapperConvertor封装

public class ExcelMapperConvertor
{
    /// <summary>
    /// ExcelMapper 将文件流（内存流）转为强类型集合
    /// FluentValidation校验转换后的数据是否符合业务要求
    /// 如果校验失败直接报错
    /// </summary>
    public static IEnumerable<T> Convert<T, TValidator>(Stream stream) where TValidator : AbstractValidator<T>, new()
    {
        var importer = new ExcelMapper(stream);
        var validator = new TValidator();

        try
        {
            //此处如果转换出错，会继续执行，直到遍历到错误一行时，才会报错
            var results = importer.Fetch<T>();
            // 遍历到错误一行时，才会报错
            foreach (var result in results)
            {
                var validationResult = validator.Validate(result);
                if (!validationResult.IsValid)
                {
                    foreach (var error in validationResult.Errors)
                    {
                        throw new Exception($"{error.PropertyName}：{error.AttemptedValue} {error.ErrorMessage}");
                    }
                }
            }

            return results;
        }
        catch (Exception ex)
        {
            throw new Exception(ex.Message);
        }
    }
}

DataTableHelper封装

public static class DataTableHelper
{
    public static ConcurrentDictionary<string, object> CacheDictionary = new ConcurrentDictionary<string, object>();

    /// <summary>
    /// 构建一个object数据转换成一维数组数据的委托
    /// </summary>
    /// <param name="objType"></param>
    /// <param name="propertyInfos"></param>
    /// <returns></returns>
    public static Func<T, object[]> BuildObjectGetValuesDelegate<T>(List<PropertyInfo> propertyInfos) where T : class
    {
        var objParameter = Expression.Parameter(typeof(T), "model");
        var selectExpressions = propertyInfos.Select(it => BuildObjectGetValueExpression(objParameter, it));
        var arrayExpression = Expression.NewArrayInit(typeof(object), selectExpressions);
        var result = Expression.Lambda<Func<T, object[]>>(arrayExpression, objParameter).Compile();
        return result;
    }

    /// <summary>
    /// 构建对象获取单个值得
    /// </summary>
    /// <param name="modelExpression"></param>
    /// <param name="propertyInfo"></param>
    /// <returns></returns>
    public static Expression BuildObjectGetValueExpression(ParameterExpression modelExpression, PropertyInfo propertyInfo)
    {
        var propertyExpression = Expression.Property(modelExpression, propertyInfo);
        var convertExpression = Expression.Convert(propertyExpression, typeof(object));
        return convertExpression;
    }

    public static DataTable ToDataTable<T>(this IEnumerable<T> source, List<PropertyInfo> propertyInfos = null, bool useColumnAttribute = false) where T : class
    {
        var table = new DataTable("template");
        if (propertyInfos == null || propertyInfos.Count == 0)
        {
            propertyInfos = typeof(T).GetProperties().Where(it => it.CanRead).ToList();
        }
        foreach (var propertyInfo in propertyInfos)
        {
            var columnName = useColumnAttribute ? (propertyInfo.GetCustomAttribute<ColumnAttribute>()?.Name ?? propertyInfo.Name) : propertyInfo.Name;
            table.Columns.Add(columnName, ChangeType(propertyInfo.PropertyType));
        }

        Func<T, object[]> func;
        var key = typeof(T).FullName + string.Join("", propertyInfos.Select(it => it.Name).ToList());//propertyInfos.Select(it => it.Name).ToList().StringJoin(); 
        if (CacheDictionary.TryGetValue(key, out var cacheFunc))
        {
            func = (Func<T, object[]>)cacheFunc;
        }
        else
        {
            func = BuildObjectGetValuesDelegate<T>(propertyInfos);
            CacheDictionary.TryAdd(key, func);
        }

        foreach (var model in source)
        {
            var rowData = func(model);
            table.Rows.Add(rowData);
        }

        return table;
    }

    private static Type ChangeType(Type type)
    {
        if (type.IsNullable())
        {
            type = Nullable.GetUnderlyingType(type);
        }

        return type;
    }

    public static bool IsNullable(this Type type)
    {
        // 检查类型是否是System.Nullable<T>的实例
        return type.IsGenericType && type.GetGenericTypeDefinition() == typeof(Nullable<>);
    }
}