Bulk insert by SqlBulkCopy

SqlBulkCopy is great option provided by microsoft to insert bulk records with flexibilty to define BatchSize and BulkCopyTimeout. I have used it ,it is really very fast as it does not write logs in database.

following is example for SqlBulkCopy.


private void BulkInsertData(DataSet objDS)
{
if (objDS != null)
{
if (objDS.Tables[0] != null)
{
DataTable sourceData = objDS.Tables[0];

using (SqlConnection destinationConnection = new SqlConnection(connectionString))
{
// open the connection
destinationConnection.Open();
using (SqlBulkCopy bulkCopy = new SqlBulkCopy(destinationConnection.ConnectionString))
{
bulkCopy.BulkCopyTimeout = 0; //sets no time limits
bulkCopy.BatchSize = 5000;
// column mappings
bulkCopy.ColumnMappings.Add("A", "A");
bulkCopy.ColumnMappings.Add("B", "B");
bulkCopy.ColumnMappings.Add("C", "C");
bulkCopy.ColumnMappings.Add("D", "E");

bulkCopy.DestinationTableName = "DestinationTableName";
bulkCopy.WriteToServer(sourceData);
}
}

sourceData.Clear();
sourceData.Dispose();
}
}

}

Map the columns required for bulk insert and provide data in datatable to function WriteToServer which will do work of bulk insert to MSSql table.