sqlsugar批量插入百万数据

表模型

/// 

/// 当和数据库名称不一样可以设置表别名 指定表明
/// 
[SugarTable("dbstudent")]
public class Student
{
    /// 

    /// 数据库是自增才配自增 
    /// 
    [SugarColumn(IsPrimaryKey = true, IsIdentity = true)]
    public int Id { get; set; }

    [SugarColumn(ColumnName = "SchoolId", IsNullable = true)]
    public int? SchoolId { get; set; }

    [SugarColumn(ColumnName = "StudentName", IsNullable = true)]//数据库与实体不一样设置列名 
    public string? Name { get; set; }

    /// 

    /// 设置后插入会取数据库默认值
    /// 
    //[SugarColumn(IsOnlyIgnoreInsert = true)]
    [SugarColumn(ColumnName = "CreateTime", IsNullable = true)]
    public DateTime? CreateTime { get; set; }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

测试

using SqlSugar;
using System.Diagnostics;

namespace SQLSugarStu01
{
    internal class Program
    {
        static void Main(string[] args)
        {
            ConnectionConfig connectionConfig = new ConnectionConfig()
            {
                ConnectionString = "server=127.0.0.1;User ID=sa;Password=xxxxxxxxxxxxxxxxxx;database=sqlsugarstu01;Encrypt=True;TrustServerCertificate=True;connection timeout=600;",
                IsAutoCloseConnection = true,
                DbType = DbType.SqlServer
            };
            using (SqlSugarClient db = new SqlSugarClient(connectionConfig))
            {
                //初始化表格
                db.CodeFirst.InitTables(typeof(Student));
                {
                    List<Student> students = new List<Student>();
                    db.Deleteable<Student>().ExecuteCommand();
                    for (int i = 0; i < 1000000; i++)
                    {
                        students.Add(new Student()
                        {
                            Name = $"Name_{i}",
                            SchoolId = i,
                            CreateTime = DateTime.Now
                        });
                    }
                    db.Deleteable<Student>().ExecuteCommand();
                    Stopwatch stopwatch1 = Stopwatch.StartNew();
                    stopwatch1.Start();
                    //插入 100万 数秒时间
                    db.Fastest<Student>().BulkCopy(students);//性能 比现有任何Bulkcopy都要快30%
                    stopwatch1.Stop();
                    Console.WriteLine($"BulkCopy大数据操作：1000000条数据大概用时：{stopwatch1.ElapsedMilliseconds} 毫秒");//看机器和数据库性能，测试是5、6秒完成
                }
            }
            Console.WriteLine("完成");
        }
    }
}
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

提示

BulkUpdate 批量更新
BulkMerge 批量插入或者更新
BulkDelete 批量删除
BulkQuery 批量查询