When you want to insert a large amount of data into the database, using insert is not only inefficient, but also causes a series of database performance problems

When inserting data using insert statements. I used two methods:

1. Each time you insert data, only one database will be inserted, which will lead to the continuous opening and closing of database links each time you insert data, resulting in low efficiency (worst)
2. Use string splicing to insert data in batches, but use StringBuilder will take up a lot of memory

The above method is not a good choice for bulk data insertion. In this case, I use SqlBulkCopy

sqlbulkCopy: load other data sources into the SQL Server table in batches, that is, insert other data sources into the database

Example code:

To create a test User table:

1 CREATE TABLE [dbo].[Users](
2     [Id] [uniqueidentifier] NOT NULL,
3     [Name] [nvarchar](100) NULL,
4     [Gender] [int] NULL,
5     [Age] [int] NULL,
6     [CityId] [int] NULL,
7     [OpTime] [datetime] NULL,
8     CONSTRAINT [PK_Users] PRIMARY KEY CLUSTERED([Id] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
9  ) ON [PRIMARY]

Then define a model mapped to the table. Because of the SqlBulkCopy feature, the defined model must have the properties corresponding to all the fields in the table: that is, the defined model needs to be in the same order as the fields in the data table, because it will be inserted in the order when it is converted to the datatable

 1 public enum Gender
 2 {
 3     Man = 1,
 4     Woman
 5 }
 6 
 7 public class User
 8 {
 9     public Guid Id { get; set; }
10     public string Name { get; set; }
11     public Gender? Gender { get; set; }
12     public int? Age { get; set; }
13     public int? CityId { get; set; }
14     public DateTime? OpTime { get; set; }
15 }

Make some data to DataTable:

List to DataTable address: https://www.cnblogs.com/zhangShanGui/p/12038563.html

 1 List<User> usersToInsert = new List<User>();
 2 usersToInsert.Add(new User() { Id = Guid.NewGuid(), Name = "so1", Gender = Gender.Man, Age = 18, CityId = 1, OpTime = DateTime.Now });
 3 usersToInsert.Add(new User() { Id = Guid.NewGuid(), Name = "so2", Gender = Gender.Man, Age = 19, CityId = 2, OpTime = DateTime.Now });
 4 usersToInsert.Add(new User() { Id = Guid.NewGuid(), Name = "so3", Gender = Gender.Man, Age = 20, CityId = 3, OpTime = DateTime.Now });
 5 usersToInsert.Add(new User() { Id = Guid.NewGuid(), Name = "so4", Gender = Gender.Man, Age = 21, CityId = 4, OpTime = DateTime.Now });
 6 
 7 var data = DataTableExtensions.ToDataTable(usersToInsert);
 8  using (SqlBulkCopy bulkCopy = new SqlBulkCopy(ConnectionString))
 9                 {
10                     bulkCopy.DestinationTableName =
11                         "Users";
12                     try
13                     {
14                         bulkCopy.WriteToServer(data, DataRowState.Added);
15                     }
16                     catch (Exception ex)
17                     {
18                         Console.WriteLine(ex.Message);
19                     }
20                 }