sqlbulkcopy in asp.net
SqlBulkCopy provides simplest and fastest way to copy large amounts of data to SqlServer. Instead of inserting all data row by row, BulkCopy process all data at
once, thus making the insertions fast.
public void Bulk_Table_CopyTO_Database(DataTable
TableToCopy, string SqlTableName)
{
SqlConnection con = new
SqlConnection(obj_Utils.getConnectionString(MyConnectionStrings.CONN));
if (con.State
== ConnectionState.Open)
{
con.Close();
}
con.Open();
SqlBulkCopy bc = new SqlBulkCopy(con);
bc.ColumnMappings.Add(0, 0);
bc.ColumnMappings.Add(1, 1);
bc.ColumnMappings.Add(2, 2);
bc.ColumnMappings.Add(3, 3);
bc.ColumnMappings.Add(4, 4);
bc.ColumnMappings.Add(5, 5);
if
(SqlTableName != "DBTable")
{
bc.ColumnMappings.Add(6, 6);
bc.ColumnMappings.Add(7, 7);
bc.ColumnMappings.Add(8, 8);
}
bc.DestinationTableName = SqlTableName;
bc.WriteToServer(TableToCopy);
con.Close();
}