ADO.NET: SqlBulkCopy
There are times in a programmers life when you need to find a way to insert a lot of data into a SQL Server database table as fast as possible, and you’re not concerned about constraints being enforced or triggers being fired. If that’s your forte, then you’ve come to the right place.
SQL Server’s Bulk Copy Protocol has been around for a long time. It has a significant performance advantage over other methods, because SQL Server basically just inserts the data into a table, bypassing many SQL Server operations.
The SqlBulkCopy class, in the System.Data.SqlClient namespace, was introduced with ADO.NET 2.0, and was designed to make working with SQL Server’s Bulk Copy feature easy. So having said that, let’s take a look at how it works.
To begin with, create a new SQL Server Database named “Test”. Add a new table to the database named “Items”. Add a “ItemCode” column, of “int” data type; add a “ItemDesc” column, of “varchar(20)” data type.
Once you have the SQL Server Database created, with a “Test” table in place, you’re ready to work with the code below.
And just for fun, let’s throw in a StopWatch so we can see how long it takes to perform the insert!
'Create a table, add columns and name
' them the same as they are in the
' database table they are updating
Dim table As New DataTable()
table.Columns.Add("ItemCode")
table.Columns.Add("ItemDesc")
'Load the table with 100,000 rows!
For i As Integer = 1 To 100000
table.Rows.Add(i, "Item " & i) : Next
'Create a new StopWatch to measure the
' amount of time it takes to perform the
' insert
Dim swatch As New Stopwatch()
swatch.Start()
'Configure the connection string
Dim cnString As String = _
"Data Source=localhost;" & _
"Initial Catalog=Test;" & _
"Integrated Security=True;"
'Create a new SqlBulkCopy class
Dim bcp As New SqlClient.SqlBulkCopy(cnString, _
SqlClient.SqlBulkCopyOptions.KeepNulls)
'You MUST specifiy the table you are going to fill
bcp.DestinationTableName = "Items"
'Increase the timeout to a more appropriate time
' for the amount of data your are inserting.
' The default is 30 seconds
bcp.BulkCopyTimeout = 60
'Write the data to the SQL Server Database table
bcp.WriteToServer(table)
'Close the instance
bcp.Close()
'Stop the Stopwatch!
swatch.Stop()
MsgBox("Done! Elapsed Milliseconds: " & _
swatch.ElapsedMilliseconds)
Some things to note:
The SqlBulkCopy class constructor is overloaded, so it can take just a connection string; a connection string and SqlBulkCopyOptions; just a SqlConnection; or a SqlConnection, SqlBulkCopyOptions, and a SqlTransaction (which enables a roll back if necessary).
The SqlBulkCopy class’ WriteToServer method is also overloaded, so it can take rows from a DataRow() array, a DataTable or a DataReader.
The fasted SqlBulkCopyOptions is “KeepNulls”, because the default column values in the table are ignored. The “Default” option inserts rows, filling in default column values where necessary.
For more information, see SqlBulkCopy on MSDN.
Summary
The System.Data.SqlClient.SqlBulkCopy class can be used to quickly and efficiently insert rows from a DataRow() array, DataTable or DataReader directly into a SQL Table, bypassing many SQL Server operations.
Because the SqlBulkCopy class has a significant performance advantage over other methods, it makes it the insert method of choice when you need fast, direct inserting of data into a SQL Table.
0 comments: