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();