参考文章
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<>);
}
}
