使用SqlBulkCopy进行批量数据插入

 1 Dim dt As DataTable = New DataTable()
 2             dt.Columns.Add("DtCostProductRuleGUID", GetType(Guid))
 3             dt.Columns.Add("RecollectGUID", GetType(Guid))
 4             dt.Columns.Add("ProjCode", GetType(String))
 5             dt.Columns.Add("ProductGUID", GetType(Guid))
 6             dt.Columns.Add("CostCode", GetType(String))
 7             dt.Columns.Add("CostShortName", GetType(String))
 8             dt.Columns.Add("ParentCode", GetType(String))
 9             dt.Columns.Add("CostLevel", GetType(Integer))
10             dt.Columns.Add("IfEndCost", GetType(Integer))
11             dt.Columns.Add("DtCost", GetType(Decimal))
12             dt.Columns.Add("IsAttend", GetType(Integer))
13             Dim row As DataRow
14             Using scope As ConnectionScope = New ConnectionScope
15                 For Each xmlNode In xmlNodeList
16                     strCostCode = xmlNode.Attributes("CostCode").Value
17                     strCostShortName = xmlNode.Attributes("CostShortName").Value
18                     strParetnCode = xmlNode.Attributes("ParentCode").Value
19                     iCostLevel = CInt(xmlNode.Attributes("CostLevel").Value)
20                     iIfEndCost = CInt(xmlNode.Attributes("IfEndCost").Value)
21                     decDtCost = CDec(xmlNode.Attributes("DtCost").Value)
22                     For i = 0 To iProductCount - 1
23                         row = dt.NewRow()
24                         row("DtCostProductRuleGUID") = Guid.NewGuid()
25                         row("CostCode") = strCostCode
26                         row("CostLevel") = iCostLevel
27                         row("CostShortName") = strCostShortName
28                         row("DtCost") = decDtCost
29                         row("IfEndCost") = iIfEndCost
30                         row("IsAttend") = xmlNode.Attributes(dtProduct.Rows(i).Item("Product")).Value.ToString()
31                         row("ParentCode") = strParetnCode
32                         row("ProductGUID") = dtProduct.Rows(i).Item("ProductGUID").ToString()
33                         row("ProjCode") = sProjCode
34                         row("RecollectGUID") = sRecollectGUID
35                         dt.Rows.Add(row)
36                     Next
37                 Next
38                 '保存金蝶业态
39                 xmlNode = xmlDOM.SelectSingleNode("/data/jdRow")
40                 For i = 0 To iProductCount - 1
41                     strSQL = "update cb_JdProductRule set  JdProductName='" & xmlNode.Attributes(dtProduct.Rows(i).Item("Product")).Value.ToString() & _
42                             "' where RecollectGUID=@RecollectGUID and ProductGUID='" & dtProduct.Rows(i).Item("ProductGUID").ToString() & "';"
43 
44 
45 
46 
47 
48 
49 
50 
51                     sbStr.Append(strSQL)
52                 Next
53                 CPQuery.From(sbStr.ToString(), param).ExecuteNonQuery()
54 
55                 Dim bulkCopy As SqlBulkCopy = scope.CreateSqlBulkCopy(SqlBulkCopyOptions.FireTriggers)
56                 bulkCopy.DestinationTableName = "cb_DtCostProductRule"
57                 bulkCopy.WriteToServer(dt)
58             End Using