关于.net:FreeSql-导入数据的各种场景总结-CNET-ORM

💻 前言

导入数据这种脏活、累活，置信大家多多少少都有经验，常见的场景有：

• 同服务器从 A 表导数据到 B 表
• 批量导入新数据
• 批量新增或更新数据
• 跨服务器从 A 表导数据到 B 表

每种场景有本人的特点，咱们个别会依据特点定制做导入数据优化，缩小总体导入的耗时，或者防止数据库 IO/CPU 占用过高，而影响到其余失常业务。

FreeSql 有好几个实用功能，流式读取数据、查问并插入、批量比照更新、插入或批改（反对实体类或字典），用好这些性能能够很不便的实现各种导入数据场景。其实 FreeSql 对应的文档始终都有，只是内容介绍比拟零散，这篇文章将针对数据导入具体介绍它们的应用办法，既然 FreeSql bug 少那就多优化一下文档吧！

本文解说以上四种常见的数据导入实现，让使用者高效解决工作问题。如果你在应用其余更好的导入计划，欢送退出探讨！

🌳 C#.NET ORM 概念

.NET ORM Object Relational Mapping 是一种为了解决面向对象与关系数据库存在的互不匹配的景象的技术。FreeSql .NET ORM 反对 .NetFramework4.0+、.NetCore、Xamarin、MAUI、Blazor、以及还有说不出来的运行平台，因为代码 绿色无依赖，反对新平台非常简单。目前单元测试数量：8500+，Nuget 下载数量：1M+。应用最宽松的开源协定 MIT https://github.com/dotnetcore/FreeSql，能够商用，文档齐全，甚至拿去卖钱也能够。

FreeSql 次要劣势在于易用性上，根本是开箱即用，在不同数据库之间切换兼容性比拟好，整体的性能个性如下：

• 反对 CodeFirst 比照构造变动迁徙、DbFirst 从数据库生成实体类；
• 反对 丰盛的表达式函数，独特的自定义解析；
• 反对 批量增加、批量更新、BulkCopy、导航属性，贪心加载、延时加载、级联保留、级联删除；
• 反对 读写拆散、分表分库，租户设计，分布式事务；
• 反对 MySql/SqlServer/PostgreSQL/Oracle/Sqlite/Firebird/ 达梦 / 神通 / 人大金仓 / 翰高 /Clickhouse/MsAccess Ado.net 实现包，以及 Odbc 的专门实现包；

8000+ 个单元测试作为基调，反对 10 少数数据库，咱们提供了通用 Odbc 实践上反对所有数据库，目前已知有群友应用 FreeSql 操作华为高斯、mycat、tidb 等数据库。装置时只须要抉择对应的数据库实现包：

dotnet add packages FreeSql.Provider.Sqlite

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.Sqlite, @"Data Source=db1.db")
    .UseAutoSyncStructure(true) // 主动同步实体构造到数据库
    .UseNoneCommandParameter(true) //SQL 不应用参数化，以便调试
    .UseMonitorCommand(cmd => Console.WriteLine(cmd.CommandText)) // 打印 SQL
    .Build();

FreeSql 提供多种 CRUD 应用习惯，请依据理论状况抉择团队适合的一种：

• 要么 FreeSql，原始用法；
• 要么 FreeSql.Repository，仓储 + 工作单元习惯；
• 要么 FreeSql.DbContext，很像 EFCore 的应用习惯；
• 要么 FreeSql.BaseEntity，充血模式；
• 要么 间接像 dapper 那样应用 DbConnection 扩大办法；

⚡ 场景一：同服务器从 A 表导数据到 B 表

导入数据，失常须要依据源数据量的大小来评估，评估过程须要在实践中缓缓积攒，以便抉择对应的导入计划。同服务器导入数据的计划有：

1、insert into A(field1, field2) select name, code from B where …

fsql.Select<B>()
    .Where(b => b.Time > DateTime.Parse("2022-08-01"))
    .InsertInto("A", b => new { b.Name, b.Code});

如果数据源是多个表组成，也能够：

fsql.Select<B, C>()
    .InnerJoin((b, c) => b.Id == c.Id)
    .Where((b, c) => b.Time > DateTime.Parse("2022-08-01"))
    .InsertInto("A", (b, c) => new {b.Name, c.Code});

2、分段插入

FreeSql 提供流式分段返回数据，避免读取的数据源量过多时占用内存，假如数据表有 100W 万记录，咱们能够设置每次只返回 1000 条。提醒：ToChunk 只执行了一次 SQL 查问。

fsql.Select<B>()
    .Where(b => b.Time > DateTime.Parse("2022-08-01"))
    .ToChunk(b => new A { field1 = b.Name, field2 = b.Code}, 1000, cb => {fsql.Insert(cb.Object).NoneParameter().ExecuteAffrows();
        // 如果数据库反对 BulkCopy 能够调用对应的 API 办法，如 SqlServer：//fsql.Insert(cb.Object).ExecuteSqlBulkCopy();});

3、分页插入

利用分页屡次读取，分页可能造成新旧数据问题，尽量设置好分页排序并记录好偏移量，确保反复问题。（不举荐）

var pageNumber = 1;
while (true)
{var list = fsql.Select<B>()
        .Where(b => b.Time > DateTime.Parse("2022-08-01"))
        .Page(pageNumber++, 1000)
        .OrderBy(b => b.Time)
        .ToList(b => new A { field1 = b.Name, field2 = b.Code}, 1000);
    if (list.Count == 0) break;

    fsql.Insert(cb.Object).NoneParameter().ExecuteAffrows();
    // 如果数据库反对 BulkCopy 能够调用对应的 API 办法，如 SqlServer：//fsql.Insert(cb.Object).ExecuteSqlBulkCopy();

    // 进展 5 秒后再插入，这个值能够依据须要本人调
    Thread.CurrentThread.Join(TimeSpan.FromSeconds(5));
}

📯 场景二：批量导入新数据

从 Excel/CVS 文件批量导入新数据，第一步须要将文件内容转换为 DataTable/List\<T\> c# 对象，这一步网上有很多工具类，在此就不解说了。

此场景适宜导入的数据是全新的、不存在于指标数据库表，假如咱们都曾经将 Excel/CVS 内容转换成为了 List\<T\>

1、利用无参数化插入

批量插入利用无参数化，会比参数化效率更高，留神参数化与 SQL 注入没有必然联系。

fsql.Insert(list).NoneParameter().ExecuteAffrows();

2、利用 BulkCopy 插入

BulkCopy 是大批量数据插入的最优计划，只惋惜不是每种数据库都反对，FreeSql 反对了 SqlServer/Oracle/MySql/PostgreSQL/ 达梦 等数据库的 BulkCopy API，如果是其余数据库倡议应用无参数化插入。

fsql.Insert(list).ExecuteSqlBulkCopy(); //SqlServer
fsql.Insert(list).ExecuteOracleBulkCopy(); //Oracle
fsql.Insert(list).ExecuteMySqlBulkCopy(); //MySql
fsql.Insert(list).ExecutePgCopy(); //PostgreSQL
fsql.Insert(list).ExecuteDmBulkCopy(); // 达梦

为什么不对立 API？

目前每种数据库驱动的 BulkCopy API 参数不统一，这些参数能够针对性的进行调优。

🚀 场景三：批量新增或更新数据

相比场景二，场景三会更麻烦，因为不是简略的追加数据，还要解决历史数据的更新，甚至对历史数据存在则疏忽。正因为简单才衍生出了更多的计划，每种计划都有优缺点，须要使用者依据本身理论状况抉择最适宜的一种办法。

同上，咱们假如曾经将 Excel/CVS 内容转换成为了 List\<T\>

1、内存循环 list 查问判断（不举荐）

foreach (var item in list)
{if (fsql.Select<T>(item).Any() == false)
        fsql.Insert(item).ExecuteAffrows();
    else
        fsql.Update<T>().SetSource(item).ExecuteAffrows();}

这种形式切实不举荐作为批量操作，性能非常低。其实 FreeSql.Repository 提供了上述的封装：

var repo = fsql.GetRepository<T>();
foreach (var item in list)
    repo.InsertOrUpdate(item);

2、利用数据库 MERGE INTO 个性

IFreeSql 定义了 InsertOrUpdate 办法实现增加或批改的性能，利用数据库个性：

fsql.InsertOrUpdate<T>()
  .SetSource(list) // 须要操作的数据
  //.IfExistsDoNothing() // 如果数据存在，啥事也不干（相当于只有不存在数据时才插入）.ExecuteAffrows();

// 或者..
var sql = fsql.Select<T2, T3>()
  .ToSql((a, b) => new
  {
    id = a.id + 1,
    name = "xxx"
  }, FieldAliasOptions.AsProperty);

fsql.InsertOrUpdate<T>()
  .SetSource(sql)
  .ExecuteAffrows();

fsql.InsertOrUpdateDict 办法可针对非实体类操作（字典类型）

题外话，是否见过这种 SQL：

insert into T
select name, code
from dual
where not exists(select 1 from T where code = dual.code)

3、利用 BeginEdit 批量编辑

MERGE INTO 数据库个性，其实性能是很低的。800 万行记录导入 7000 行大概 7 秒，各数据库性能差不多。

BeginEdit 是 FreeSql 特色性能之一，十分实用，可它却是少有被挖掘到的性能。创意源自于医疗软件，比方操作员编辑清单，会新增，会删除，会批改，等操作完后再点保留。

其实我过往开发的我的项目也有过相似需要，每步操作都进行数据库保留，没什么问题吧？让咱们看下最初对立保留应该怎么做。

// 将 list 返回给 UI 端渲染
var list = fsql.Select<T>().Where(a => a.OrderId == 100).ToList();


// 对立保留

// 旧数据可通过查问，或者由 UI 端提供
List<T> oldlist = fsql.Select<T>().Where(a => a.OrderId == 100).ToList();
// 新数据由 UI 端提供
List<T> newlist = ...;

var repo = fsql.GetRepository<T>();
repo.BeginEdit(oldlist); // 开始进行编辑
repo.EndEdit(newlist); // 比照新旧 List

BeginEdit/EndEdit 只针对 oldlist 比照，而不是针对全表。在内存中比照计算 Insert/Update/Delete 比 MERGE INTO 性能快得多，利用该性能能够很不便的实现批量导入或更新，例如反复导入一天的数据。

该当留神当导入的数据量过大时，应该分批进行操作，而不是一次性比照全副，假如咱们每批执行 1000 条：

// 查问旧数据
var oldlist = fsql.Select<T>().WhereDynamic(list1000).ToList();
// 应用 IN 查问性能可能较慢，能够按工夫范畴查问，如下：//var minTime = list1000.Select(a => a.Time).Min();
//var maxTime = list1000.Select(a => a.Time).Max();
//var oldlist = fsql.Select<T>().Where(a=> a.Time.Between(minTime, maxTime)).ToList();
// 在内存二次过滤，还能够进一步优化将 list1000.Any 改成字典
//oldlist = oldlist.Where(a=> !list1000.Any(b => b.Id == a.Id)).ToList();

var repo = fsql.GetRepository<T>();
// 被编辑的数据
repo.BeginEdit(oldlist);
// 将 list1000 与 oldlist 进行比照，计算出 delete/insert/update 语句执行
repo.EndEdit(list1000);

EndEdit 最多执行 3 条 SQL，从而大大晋升了命令往返工夫耗费。特地适宜导入大批量数据，且大部分数据曾经存在，或者数据未产生变更的场景。

4、MySql insert ignore into

如果只插入不存的的数据，并且应用 MySql 数据库，能够应用以下形式：

fsql.Insert<T>().MySqlIgnoreInto().AppendData(list).NoneParameter().ExecuteAffrows();
///INSERT IGNORE INTO `T`(...)
//VALUES(...),(...),(...)

🌌 场景四：跨服务器从 A 表导数据到 B 表

与场景一相似，跨服务器须要定义多个 IFreeSql 对象，假如 A 表所在服务器拜访对象是 fsql1，B 表应用 fsql2

1、分段插入

fsql2.Select<B>()
    .Where(b => b.Time > DateTime.Parse("2022-08-01"))
    .ToChunk(b => new A { field1 = b.Name, field2 = b.Code}, 1000, cb => {fsql1.Insert(cb.Object).NoneParameter().ExecuteAffrows();
        // 如果数据库反对 BulkCopy 能够调用对应的 API 办法，如 SqlServer：//fsql1.Insert(cb.Object).ExecuteSqlBulkCopy();

        // 这里也能够应用 BeginEdit 进行批量编辑性能，解决数据反复问题
    });

2、分页插入

利用分页屡次读取，分页可能造成新旧数据问题，尽量设置好分页排序并记录好偏移量，确保反复问题。（不举荐）

var pageNumber = 1;
while (true)
{var list = fsql2.Select<B>()
        .Where(b => b.Time > DateTime.Parse("2022-08-01"))
        .Page(pageNumber++, 1000)
        .OrderBy(b => b.Time)
        .ToList(b => new A { field1 = b.Name, field2 = b.Code}, 1000);
    if (list.Count == 0) break;

    fsql1.Insert(cb.Object).NoneParameter().ExecuteAffrows();
    // 如果数据库反对 BulkCopy 能够调用对应的 API 办法，如 SqlServer：//fsql1.Insert(cb.Object).ExecuteSqlBulkCopy();

    // 这里也能够应用 BeginEdit 进行批量编辑性能，解决数据反复问题

    // 进展 5 秒后再插入，这个值能够依据须要本人调
    Thread.CurrentThread.Join(TimeSpan.FromSeconds(5));
}

⛳ 结束语

FreeSql 的功能强大，以及稳定性，我不想吹牛，也不喜爱吹牛，如果大家有什么好的想法能够一起探讨，毕竟咱们只能遇到无限的场景，还有很多我不晓得的场景需要不是吗？

心愿这篇文章能帮忙大家轻松了解并熟练掌握它，疾速解决工作中遇到的数据导入问题，为企业的我的项目研发贡献力量。

开源地址：https://github.com/dotnetcore/FreeSql

作者是什么人？

作者是一个入行 18 年的老批，他目前写的.net 开源我的项目有：

须要的请拿走，这些都是最近几年的开源作品，以前更早写的就不发了。