Archive

Posts Tagged ‘SQL’

SQLBulkCopy

May 1, 2009 1 comment

I was looking to copy some bulk data from one of the data table in memory to my SQL DB table. I tried using DBAdapter.Update but it didn’t work as in my source datatable none of the row was in updated/new stage. Then after searching a lot, I found “SQLBulkCopy”.

The SqlBulkCopy class can be used to write bulk data only to SQL Server tables. However, the data source is not limited to SQL Server; any data source can be used, as long as the data can be loaded to a DataTable instance or read with a IDataReader instance. To copy the source data to destination table in the sql server, need to set destination table name and execute “WriteToServer” method. Following code snippet

// create sqlBulkCopy object
SqlBulkCopy bulkCopy = new SqlBulkCopy(connString);

// set destination table name for sqlBulkcopy object
bulkCopy.DestinationTableName = "[Table Name]";

// write source data to destination sql table
bulkCopy.WriteToServer();

Performancewise using SQLBulkCopy is far better than using normal Insert/Update commands.

Categories: c#, SQL Tags: , ,