使用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 DataRow14             Using scope As ConnectionScope = New ConnectionScope15                 For Each xmlNode In xmlNodeList16                     strCostCode = xmlNode.Attributes("CostCode").Value17                     strCostShortName = xmlNode.Attributes("CostShortName").Value18                     strParetnCode = xmlNode.Attributes("ParentCode").Value19                     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 - 123                         row = dt.NewRow()24                         row("DtCostProductRuleGUID") = Guid.NewGuid()25                         row("CostCode") = strCostCode26                         row("CostLevel") = iCostLevel27                         row("CostShortName") = strCostShortName28                         row("DtCost") = decDtCost29                         row("IfEndCost") = iIfEndCost30                         row("IsAttend") = xmlNode.Attributes(dtProduct.Rows(i).Item("Product")).Value.ToString()31                         row("ParentCode") = strParetnCode32                         row("ProductGUID") = dtProduct.Rows(i).Item("ProductGUID").ToString()33                         row("ProjCode") = sProjCode34                         row("RecollectGUID") = sRecollectGUID35                         dt.Rows.Add(row)36                     Next37                 Next38                 ‘儲存金蝶業態39                 xmlNode = xmlDOM.SelectSingleNode("/data/jdRow")40                 For i = 0 To iProductCount - 141                     strSQL = "update cb_JdProductRule set  JdProductName=‘" & xmlNode.Attributes(dtProduct.Rows(i).Item("Product")).Value.ToString() & _42                             "‘ where [email protected] and ProductGUID=‘" & dtProduct.Rows(i).Item("ProductGUID").ToString() & "‘;"43 44 45 46 47 48 49 50 51                     sbStr.Append(strSQL)52                 Next53                 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

 

使用SqlBulkCopy進行批量資料插入