當(dāng)想在數(shù)據(jù)庫中插入大量數(shù)據(jù)時，使用insert 不僅效率低，而且會導(dǎo)致一系列的數(shù)據(jù)庫性能問題
當(dāng)使用insert語句進(jìn)行插入數(shù)據(jù)時。我使用了兩種方式：
以上方式對于批量插入數(shù)據(jù)都不是一個好的選擇， 針對這種情況我使用了SqlBulkCopy
sqlbulkCopy：將其它數(shù)據(jù)源批量加載sqlserver表，就是可以將其它數(shù)據(jù)源插入到數(shù)據(jù)庫中
示例代碼：
創(chuàng)建測試User表：
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]
然后定義一個與表映射的Model,因SqlBulkCopy 的特性，定義的 Model 必須擁有與表所有的字段對應(yīng)的屬性：也就是定義的model，需要跟數(shù)據(jù)表的字段順序一樣，因為轉(zhuǎn)為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 }
制造些數(shù)據(jù)轉(zhuǎn)為DataTable：
List轉(zhuǎn)為DataTable地址：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 try13 {14 bulkCopy.WriteToServer(data, DataRowState.Added);15 }16 catch (Exception ex)17 {18 Console.WriteLine(ex.Message);19 }20 }
