. Net batch insert data to MSSQL

I have been using datatable to update data in batches to the database. Today I found that the import will time out when adapter. Update data exceeds 0.6 million pieces of data.

' Merge DT into database
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 underwriting information where id = 0 " , Conn)
Myadapter. selectcommand = Mycommand
Dim Mycommandbuilder As   New Sqlcommandbuilder (myadapter)
Myadapter. Update (DT)
End sub

Find the information and use the followingCodeMore than 1 million data records can be inserted at a time. The MSSQL event probe finds that adapter. Update is an insert data table, while sqlbulkcopy is a 50 thousand insert.

Sub Adddata ( Byval DT As Datatable)
Dim   Str   As   String   = _ Conn. SSTR
' Declare database connection
Dim Conn As   New Sqlconnection ( Str )
Conn. open ()
' Declare sqlbulkcopy and using to release unmanaged Resources
Using Sqlbc As   New Sqlbulkcopy (conn)
' Defines the volume of data inserted in batches at a time.
Sqlbc. batchsize =   50000

' Set tables to be written in batches
Sqlbc. destinationtablename =   " Underwriting Information "

' The custom able corresponds to the database field.
Sqlbc. columnmappings. Add ( " License plate number " , " License plate number " )
Sqlbc. columnmappings. Add ( " Vehicle ID " , " Vehicle ID " )
Sqlbc. columnmappings. Add ( " Engine No. " , " Engine No. " )
Sqlbc. columnmappings. Add ( " Policy No. " , " Policy No. " )
Sqlbc. columnmappings. Add ( " Contract date " , " Contract date " )
Sqlbc. columnmappings. Add ( " Warranty start period " , " Warranty start period " )
Sqlbc. columnmappings. Add ( " Warranty end period " , " Warranty end period " )
Sqlbc. columnmappings. Add ( " Guid " , " Guid " )

'Batch write
Sqlbc. writetoserver (DT)
End Using
Conn. Dispose ()
End sub

SQL statement generated by Adapter. Update

Exec Sp_executesql n ' Insert into [warranty information] ([license plate number], [Vehicle rack number], [engine number], [warranty number], [contract date], [warranty start period], [warranty period], [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 ' Min d17867 ' , @ P2   = N ' 04353437 ' , @ P3   = N ' 4106392 ' , @ P4   = N ' Pdaa200835020117022132 ' , @ P5   =   ' 12 8 2008 12: 00: 00: 000 AM ' , @ P6   =   ' 01 1 2009 12: 00: 00: 000 AM ' , @ P7   =   ' 12 31 2009 12: 00: 00: 000 AM ' , @ P8   =   ' 0d603eb4-4040-4d84-833d-1566c5dad4f0 '

SQL statement generated by sqlbulkcopy

Insert   Bulk Underwriting information ( [ License plate number ]   Nvarchar ( 50 ) Collate chinese_prc_ci_as, [ Vehicle ID ]   Nvarchar ( 50 ) Collate chinese_prc_ci_as, [ Engine No. ]   Nvarchar ( 50 ) Collate chinese_prc_ci_as, [ Policy No. ]   Nvarchar ( 50 ) Collate chinese_prc_ci_as, [ Contract date ]   Datetime , [ Warranty start period ]   Datetime , [ Warranty end period ]   Datetime , [ Guid ]   Char ( 100 ) Collate chinese_prc_ci_as)