Sql Bulk Copy in .Net

Sometimes, we need to insert a large amount of data in sql table. .Net Framework 2.0 and later version contain a “SqlBulkCopy” class in “System.Data.SqlClient” namespace.

SqlBulkCopy provides simplest and fastest way to copy large amounts of data to SqlServer, Instead of inserting all data row by row.

Here, is the example of SqlBulkCopy class

private void BulkCopy(DataTable dtSource, string strDestinationTableName)
{
   SqlConnection sqlCon = new SqlConnection("Your Connection String");
   SqlBulkCopy sqlBC = new SqlBulkCopy(sqlCon);
   sqlBC.DestinationTableName = strDestinationTableName;

   /*
   Here is the column mapping to the source and destination table.
   You can also use column name instead of column index for mapping.   
   */

   //sqlBC.ColumnMappings.Add(SourceTableColumnIndex, DestinationTableColumn Index

   sqlBC.ColumnMappings.Add(0, 0);     
   sqlBC.ColumnMappings.Add(1, 1);
   sqlBC.ColumnMappings.Add(2, 2);
   sqlBC.ColumnMappings.Add(3, 3);
   sqlBC.ColumnMappings.Add(4, 4);

   try
   {
    sqlCon.Open();
    sqlBC.BatchSize = 500; //BatchSize Indicates the total number of rows sent to the server at a time.
    sqlBC.WriteToServer(dtSource);
    sqlCon.Close();
   }
   catch (Exception ex)
   {
    if (sqlCon.State == ConnectionState.Open)
        sqlCon.Close();
    throw ex;
   }
}