之前一直用DATATABLE批量更新到数据库，今天发现Adapter.Update数据超过60万条数据时导入会超时
Public Sub UpdateData(ByVal dt As DataTable)
Dim conn As New SqlConnection(_Conn.sStr)
Dim myAdapter As New SqlDataAdapter()
Dim myCommand As New SqlCommand("select * from 承保信息 where id=0", conn)
myAdapter.SelectCommand = myCommand
Dim myCommandBuilder As New SqlCommandBuilder(myAdapter)
myAdapter.Update(dt)
End Sub
找了一下资料，测试用下面的代码一次可以插入一百多万条数据，通过MSSQL事件探查器发现Adapter.Update是一条一条插入数据表，而SqlBulkCopy是5万5万插入。
Dim str As String = _Conn.sStr
'声明数据库连接
Dim conn As New SqlConnection(str)
conn.Open()
'声明SqlBulkCopy ,using释放非托管资源
Using sqlBC As New SqlBulkCopy(conn)
'定义一次批量的插入的数据量
sqlBC.BatchSize = 50000
'设置要批量写入的表
sqlBC.DestinationTableName = "承保信息"
'自定义的datatable和数据库的字段进行对应
sqlBC.ColumnMappings.Add("车牌号", "车牌号")
sqlBC.ColumnMappings.Add("车架号", "车架号")
sqlBC.ColumnMappings.Add("发动机号", "发动机号")
sqlBC.ColumnMappings.Add("保单号", "保单号")
sqlBC.ColumnMappings.Add("签单日期", "签单日期")
sqlBC.ColumnMappings.Add("保险起期", "保险起期")
sqlBC.ColumnMappings.Add("保险止期", "保险止期")
sqlBC.ColumnMappings.Add("GUID", "GUID")
'批量写入
sqlBC.WriteToServer(dt)
End Using
conn.Dispose()
End Sub
Adapter.Update生成的SQL语句
exec sp_executesql N'INSERT INTO [承保信息] ([车牌号], [车架号], [发动机号], [保单号], [签单日期], [保险起期], [保险止期], [GUID]) VALUES (@p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)', N'@p1 nvarchar(7),@p2 nvarchar(8),@p3 nvarchar(7),@p4 nvarchar(22),@p5 datetime,@p6 datetime,@p7 datetime,@p8 char(36)', @p1 = N'闽D17867', @p2 = N'04353437', @p3 = N'4106392', @p4 = N'PDAA200835020117022132', @p5 = '12 8 2008 12:00:00:000AM', @p6 = '01 1 2009 12:00:00:000AM', @p7 = '12 31 2009 12:00:00:000AM', @p8 = '0d603eb4-4040-4d84-833d-1566c5dad4f0'
SqlBulkCopy生成的SQL语句
insert bulk 承保信息 ([车牌号] NVarChar(50) COLLATE Chinese_PRC_CI_AS, [车架号] NVarChar(50) COLLATE Chinese_PRC_CI_AS, [发动机号] NVarChar(50) COLLATE Chinese_PRC_CI_AS, [保单号] NVarChar(50) COLLATE Chinese_PRC_CI_AS, [签单日期] DateTime, [保险起期] DateTime, [保险止期] DateTime, [GUID] Char(100) COLLATE Chinese_PRC_CI_AS)