利用SqlBulkCopy类批量导入数据到sql server

最新推荐文章于 2022-05-07 11:30:27 发布

原创最新推荐文章于 2022-05-07 11:30:27 发布 · 755 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#sql server #exception #function #integer

.Net 专栏收录该内容

29 篇文章

订阅专栏

本文提供了一个使用ADO.NET实现的将数据表导入到数据库表中的代码示例，包括连接数据库、开启事务、使用SqlBulkCopy进行数据批量复制等关键步骤。

直接贴代码:

Private Function ImportData(ByVal dt As DataTable) As Boolean
Dim result As Boolean = False
Dim sqlCon As SqlConnection = New SqlConnection("sqlConnectString")
sqlCon.Open()
Dim sqlTran As SqlTransaction = sqlCon.BeginTransaction()
Dim sqlBulkCopy As New SqlBulkCopy(sqlCon, SqlBulkCopyOptions.KeepIdentity, sqlTran)
Try
sqlBulkCopy.DestinationTableName = "TabName"
sqlBulkCopy.ColumnMappings.Clear()
For i As Integer = 0 To dt.Columns.Count - 1
sqlBulkCopy.ColumnMappings.Add(dt.Columns(i).ColumnName, dt.Columns(i).ColumnName)
Next
sqlBulkCopy.WriteToServer(dt)
sqlTran.Commit()
result = True
Catch ex As Exception
sqlTran.Rollback()
result = False
Finally
sqlBulkCopy.Close()
End Try
Return result
End Function