sqlbulkcopy 批量插入数据

批量插入 Datetable数据通过sqlbulkcopy 插入1百万条数据用时 10秒钟 (有兴趣的小伙伴可以去测试)

 1   /// <summary>
 2         /// 
 3         /// </summary>
 4         /// <param name="connectionStr">连接字符串</param>
 5         /// <param name="dataTableName">数据库表名称</param>
 6         /// <param name="sourceDataTable"></param>
 7         /// <param name="batchSize"></param>
 8         public static void SqlBulkCopyByDataTable(string connectionStr, string dataTableName, DataTable sourceDataTable, int batchSize = 100000)
 9         {
10             using (SqlConnection connection = new SqlConnection(connectionStr))
11             {
12                 using (System.Data.SqlClient.SqlBulkCopy sqlBulkCopy = new System.Data.SqlClient.SqlBulkCopy(connectionStr, SqlBulkCopyOptions.UseInternalTransaction))
13                 {
14                     try
15                     {
16                         sqlBulkCopy.DestinationTableName = dataTableName;
17                         sqlBulkCopy.BatchSize = batchSize;
18                         for (int i = 0; i < sourceDataTable.Columns.Count; i++)
19                         {
20                             sqlBulkCopy.ColumnMappings.Add(sourceDataTable.Columns[i].ColumnName, sourceDataTable.Columns[i].ColumnName);
21                         }
22                         sqlBulkCopy.WriteToServer(sourceDataTable);
23                     }
24                     catch (Exception ex)
25                     {
26 
27                         throw ex;
28                     }
29                 }
30             }
31         }

调用：

 1     DataTable dt = new DataTable("测试");
 2             dt.Columns.Add("Id", typeof(int));
 3             dt.Columns.Add("Name", typeof(string));
 4             dt.Columns.Add("Age", typeof(int));
 5             for (int i = 1; i <= 10; i++)
 6             {
 7                 DataRow row = dt.NewRow();
 8                 row["Id"] = i;
 9                 row["Name"] = "名字" + i;
10                 row["Age"] = i;
11                 dt.Rows.Add(row);
12             }
13             Stopwatch stopWatch = new Stopwatch();
14             stopWatch.Start();
15 
16         
17 
18             Console.WriteLine("批量插入table时间："+stopWatch.Elapsed);

批量插入List<T> :

 1    #region List<T> 批量插入
 2         /// <summary>
 3         /// 
 4         /// </summary>
 5         /// <typeparam name="T"></typeparam>
 6         /// <param name="data"></param>
 7         /// <param name="sqlconn"></param>
 8         public static void Sqlbulkcopy_<T>(List<T> data, string sqlconn = null)
 9         {
10             #region 待处理数据初始化处理
11             List<PropertyInfo> pList = new List<PropertyInfo>();//创建属性的集合
12             DataTable dt = new DataTable();
13             //把所有的public属性加入到集合 并添加DataTable的列    
14             Array.ForEach<PropertyInfo>(typeof(T).GetProperties(), p =>
15             {
16                 pList.Add(p);
17                 dt.Columns.Add(p.Name, p.PropertyType);
18             });  //获得反射的入口（typeof（）） //要对 array 的每个元素执行的 System.Action。
19             foreach (var item in data)
20             {
21                 DataRow row = dt.NewRow(); //创建一个DataRow实例  
22                 pList.ForEach(p => row[p.Name] = p.GetValue(item, null)); //给row 赋值
23                 dt.Rows.Add(row); //加入到DataTable    
24             }
25             #endregion
26             #region 批量插入数据库 SqlBulkCopy声明及参数设置
27             SqlBulkCopy bulk = new SqlBulkCopy(sqlconn, SqlBulkCopyOptions.UseInternalTransaction)   //, SqlBulkCopyOptions.UseInternalTransaction
28             {
29                 DestinationTableName = "Tongbu" /*设置数据库目标表名称*/
30                 ,
31                 BatchSize = dt.Rows.Count /*每一批次中的行数*/
32             };
33 
34             bulk.ColumnMappings.Add("Id", "Id"); //设置数据源中的列和目标表中的列之间的映射关系
35                                                  // bulk.ColumnMappings.Add("FlightId", "FlightId");//ColumnMappings.Add("源数据表列名称", "目标表数据列名称");
36             bulk.ColumnMappings.Add("Name", "Name");
37             bulk.ColumnMappings.Add("Age", "Age");
38 
39 
40             //...
41             #endregion
42             bulk.WriteToServer(dt);
43             if (bulk != null)
44             {
45                 bulk.Close();
46             }
47         }
48         #endregion

调用：

  List<TestModel> T_list = new List<TestModel>();
            for (int i = 1; i <= 100000; i++)
            {
                T_list.Add(new TestModel() { Name = "Test" + i, Age = i });
            }

            Stopwatch stopWatch = new Stopwatch();
            stopWatch.Start();

           
            Sqlbulkcopy_Tran<TestModel>(T_list);

            Console.WriteLine(stopWatch.Elapsed);