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