使用sqlBulkCopy去批量插入数据

 

 1         /// <summary>
 2         /// SqlBulkCopy批量插入数据
 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 (SqlBulkCopy sqlBulkCopy=new 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         }

 

      使用方式如下，这里我将插入100万条数据，

　　在SqlBulkCopyByDataTable方法中每次插入10万条，

　　其插入方式SqlBulkCopyOptions.UseInternalTransaction （即事务插入，可用其他方式。）

 

 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 <= 1000000; 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 
14             string connStr = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
15 
16             Stopwatch stopWatch = new Stopwatch();
17             stopWatch.Start();
18 
19 
20            SqlBulkCopyByDataTable(connStr, "T_Demo", dt); 
21 
22             Console.WriteLine(stopWatch.Elapsed);  
23             Console.ReadKey();