C#中的SqlBulkCopy批量插入数据
时间：2021-07-01 10:21:17
帮助过：3人阅读
/// <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();
耗时为如下。
C#中的SqlBulkCopy批量插入数据
标签：
