新增

FreeSql 提供单条和批量插入数据的方法,在特定的数据库执行还可以返回插入后的记录。

var connectionString = "Data Source=127.0.0.1;Port=3306;User ID=root;Password=root;" + 
    "Initial Catalog=cccddd;Charset=utf8;SslMode=none;Max pool size=10";

static IFreeSql fsql = new FreeSql.FreeSqlBuilder()
    .UseConnectionString(FreeSql.DataType.MySql, connectionString)
    .UseAutoSyncStructure(true) //自动同步实体结构到数据库
    .Build(); //请务必定义成 Singleton 单例模式

class Topic {
    [Column(IsIdentity = true, IsPrimary = true)]
    public int Id { get; set; }
    public int Clicks { get; set; }
    public string Title { get; set; }
    public DateTime CreateTime { get; set; }
}

var items = new List<Topic>();
for (var a = 0; a < 10; a++) items.Add(new Topic { Id = a + 1, Title = quot;newtitle{a}", Clicks = a * 100 });
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

1、单条插入

var t1 = fsql.Insert(items.First()).ExecuteAffrows();
//INSERT INTO `Topic`(`Clicks`, `Title`, `CreateTime`) 
//VALUES(?Clicks0, ?Title0, ?CreateTime0)
1
2
3

如果表有自增列,插入数据后应该要返回 id。

方法1:(原始)

long id = fsql.Insert(blog).ExecuteIdentity();
blog.Id = id;
1
2

方法2:(依赖 FreeSql.Repository)

var repo = fsql.GetRepository<Blog>();
repo.Insert(blog);
1
2

内部会将插入后的自增值填充给 blog.Id

2、批量插入

var t2 = fsql.Insert(items).ExecuteAffrows();
//INSERT INTO `Topic`(`Clicks`, `Title`, `CreateTime`) 
//VALUES(?Clicks0, ?Title0, ?CreateTime0), (?Clicks1, ?Title1, ?CreateTime1), 
//(?Clicks2, ?Title2, ?CreateTime2), (?Clicks3, ?Title3, ?CreateTime3), 
//(?Clicks4, ?Title4, ?CreateTime4), (?Clicks5, ?Title5, ?CreateTime5), 
//(?Clicks6, ?Title6, ?CreateTime6), (?Clicks7, ?Title7, ?CreateTime7), 
//(?Clicks8, ?Title8, ?CreateTime8), (?Clicks9, ?Title9, ?CreateTime9)
1
2
3
4
5
6
7

解决了 SqlServer 批量添加容易导致的错误:传入的请求具有过多的参数。该服务器支持最多 2100 个参数。请减少参数的数目,然后重新发送该请求。

原理为拆成多个包用事务执行;

当插入大批量数据时,内部采用分割分批执行的逻辑进行。分割规则如下:

数量参数量
MySql50003000
PostgreSQL50003000
SqlServer10002100
Oracle500999
Sqlite5000999

数量:为每批分割的大小,如批量插入 10000 条数据,在 mysql 执行时会分割为两批。
参数量:为每批分割的参数量大小,如批量插入 10000 条数据,每行需要使用 5 个参数化,在 mysql 执行时会分割为每批 3000 / 5。

分割执行后,当外部未提供事务时,内部自开事务,实现插入完整性。也可以通过 BatchOptions 设置合适的值。

FreeSql 适配了每一种数据类型参数化,和不参数化的使用。批量插入建议关闭参数化功能,使用 .NonoParameter() 进行执行。

3、ExecuteSqlBulkCopy、ExecutePgCopy、ExecuteMySqlBulkCopy

Bulk Copy 操作以扩展方法的形式实现,针对 SqlServer/PostgreSQL/MySql 数据库,可用的包:FreeSql.Provider.SqlServer/FreeSql.Provider.PostgreSQL/FreeSql.Provider.MySqlConnector。

批量插入测试参考(52个字段)

18W1W5K2K1K50010050
MySql 5.5 ExecuteAffrows38,4812,2341,1362842391676630
MySql 5.5 ExecuteMySqlBulkCopy28,4051,1426574514355924722
SqlServer Express ExecuteAffrows402,35524,84711,4654,9712,43791513888
SqlServer Express ExecuteSqlBulkCopy21,065578326139105796048
PostgreSQL 10 ExecuteAffrows46,7563,2942,2691,0193742095137
PostgreSQL 10 ExecutePgCopy10,09058333713688613025

18W 解释:插入18万行记录,表格中的数字是执行时间(单位ms)

批量插入测试参考(10个字段)

18W1W5K2K1K50010050
MySql 5.5 ExecuteAffrows11,1718663668083502434
MySql 5.5 ExecuteMySqlBulkCopy6,504399257116871001616
SqlServer Express ExecuteAffrows47,2042,2751,1084882791233516
SqlServer Express ExecuteSqlBulkCopy4,248127713048141110
PostgreSQL 10 ExecuteAffrows9,7865683361571023496
PostgreSQL 10 ExecutePgCopy4,0811679339211242

测试结果,是在相同操作系统下进行的,并且都有预热

4、插入指定的列

var t3 = fsql.Insert(items).InsertColumns(a => a.Title).ExecuteAffrows();
//INSERT INTO `Topic`(`Title`) 
//VALUES(?Title0), (?Title1), (?Title2), (?Title3), (?Title4), 
//(?Title5), (?Title6), (?Title7), (?Title8), (?Title9)

var t4 = fsql.Insert(items).InsertColumns(a =>new { a.Title, a.Clicks }).ExecuteAffrows();
//INSERT INTO `Topic`(`Clicks`, `Title`) 
//VALUES(?Clicks0, ?Title0), (?Clicks1, ?Title1), (?Clicks2, ?Title2), 
//(?Clicks3, ?Title3), (?Clicks4, ?Title4), (?Clicks5, ?Title5), 
//(?Clicks6, ?Title6), (?Clicks7, ?Title7), (?Clicks8, ?Title8), 
//(?Clicks9, ?Title9)
1
2
3
4
5
6
7
8
9
10
11

5、忽略列

var t5 = fsql.Insert(items).IgnoreColumns(a => a.CreateTime).ExecuteAffrows();
//INSERT INTO `Topic`(`Clicks`, `Title`) 
//VALUES(?Clicks0, ?Title0), (?Clicks1, ?Title1), (?Clicks2, ?Title2), 
//(?Clicks3, ?Title3), (?Clicks4, ?Title4), (?Clicks5, ?Title5), 
//(?Clicks6, ?Title6), (?Clicks7, ?Title7), (?Clicks8, ?Title8), 
//(?Clicks9, ?Title9)

var t6 = fsql.Insert(items).IgnoreColumns(a => new { a.Title, a.CreateTime }).ExecuteAffrows();
///INSERT INTO `Topic`(`Clicks`) 
//VALUES(?Clicks0), (?Clicks1), (?Clicks2), (?Clicks3), (?Clicks4), 
//(?Clicks5), (?Clicks6), (?Clicks7), (?Clicks8), (?Clicks9)
1
2
3
4
5
6
7
8
9
10
11

6、列插入优先级

全部列 < 指定列(InsertColumns) < 忽略列(IgnoreColumns)
1

在没有使用 InsertColumns/IgnoreColumns 的情况下,实体所有列将被插入数据库;

在使用 InsertColumns,没有使用 IgnoreColumns 的情况下,只有指定的列插入数据库;

在使用 IgnoreColumns 的情况下,只有未被指定的列插入数据库;

7、导入表数据

int affrows = fsql.Select<Topic>()
  .Limit(10)
  .InsertInto(null, a => new Topic2
  {
    Title = a.Title
  });
1
2
3
4
5
6
INSERT INTO `Topic2`(`Title`, `Clicks`, `CreateTime`)
SELECT a.`Title`, 0, '0001-01-01 00:00:00' 
FROM `Topic` a 
limit 10
1
2
3
4

注意:因为 ClicksCreateTime 没有被选择,所以使用目标实体属性 [Column(InsertValueSql = xx)] 设置的值,或者使用目标实体属性的 c# 默认值。

7、MySql 特有功能 Insert Ignore Into

fsql.Insert<Topic>().MySqlIgnoreInto().AppendData(items).ExecuteAffrows();
///INSERT IGNORE INTO `Topic`(`Clicks`) 
//VALUES(?Clicks0), (?Clicks1), (?Clicks2), (?Clicks3), (?Clicks4), 
//(?Clicks5), (?Clicks6), (?Clicks7), (?Clicks8), (?Clicks9)
1
2
3
4

8、MySql 特有功能 Insert Ignore Into

fsql.Insert<Topic>().MySqlIgnoreInto().AppendData(items).ExecuteAffrows();
///INSERT IGNORE INTO `Topic`(`Clicks`) 
//VALUES(@Clicks0), (@Clicks1), (@Clicks2), (@Clicks3), (@Clicks4), 
//(@Clicks5), (@Clicks6), (@Clicks7), (@Clicks8), (@Clicks9)
1
2
3
4

9、MySql 特有功能 On Duplicate Key Update

10、PostgreSQL 特有功能 On Conflict Do Update

API

方法返回值参数描述
AppendData<this>T1 | IEnumerable<T1>追加准备插入的实体
InsertIdentity<this>指明插入自增列
InsertColumns<this>Lambda只插入的列
IgnoreColumns<this>Lambda忽略的列
CommandTimeout<this>int命令超时设置(秒)
WithTransaction<this>DbTransaction设置事务对象
WithConnection<this>DbConnection设置连接对象
ToSqlstring返回即将执行的SQL语句
OnDuplicateKeyUpdateOnDuplicateKeyUpdate<T1>MySql 特有的功能,On Duplicate Key Update
OnConflictDoUpdateOnConflictDoUpdate<T1>PostgreSQL 特有的功能,On Conflict Do Update
ExecuteAffrowslong执行SQL语句,返回影响的行数
ExecuteIdentitylong执行SQL语句,返回自增值
ExecuteInsertedList<T1>执行SQL语句,返回插入后的记录
ExecuteSqlBulkCopyvoidSqlServer 特有的功能,执行 SqlBulkCopy 批量插入的封装
ExecutePgCopyvoidPostgreSQL 特有的功能,执行 Copy 批量导入数据