Benchmarking SqlBulkCopy

Bulk uploading into SQL Server programmatically has always been slow and tedious exercise.
Fortunately Microsoft has included SqlBulkCopy in the SqlClient for ADO.NET 2.0

In my testing. I loaded Australia's PostCode list from csv file, consisting of 16,789 rows.
I compared SqlAdapter.Fill(), SqlAdapterFill() + Setting the UpdateBatchSize property, and finally I used SqlBulkCopy.

First I read the csv file into in memory DataTable. For this purpose I read all rows in one go. In real live application, we would need to page the operation.

Then I push the DataTable into SqlServer 2005 table using 3 different methods.

1. SqlAdapter.Fill
2. SqlAdapter.Fill + Setting the UpdateBatchSize property = 500
   
3. SqlBulkCopy

Option 1 took 11,365 ms
Option 2 took 9,636 ms
and .....
Option 3 took 568 ms

Wow,.....
I suspect that option 1 and option 2 difference would be more significant if the SQL Server is located accross network. In this test I use local SQL Server.

To do SqlBulkCopy is surprisingly simple:
You need only to specify the connection string, the datatable and the name of the destination table.
string connectionString = "....";
DataTable csvTable = PopulateTable();
SqlBulkCopy bcp = new SqlBulkCopy(connectionString );
bcp.DestinationTableName = "PostCodes";
bcp.WriteToServer(csvTable);

Here are snippet of the code from my Benchmark Harness, which is a winforms application

---