We will Know about how we use “bulk insert in .net”. For bulk insert we mostly use the object SqlBulkCopy because it seemed the best and easiest option for performing a bulk insert in a SQL Server database. i have given the below example and code to create SqlBulkCopy – BulkCopy.cs. This class works the same way as SqlBulkCopy but has some extra features.
/// Bulk Insert in .net
///
///Connection String
///Bulk Data in Datatable
///Table Name
/// —-Optional—-
///No of Records will be inserted in one batch
/// true/false
public static bool BulkInsertToDatabase(DataTable dt, string tableName, int batchSize = 10000)
{
var connectionString = GetConnectionString();
using (var connection = new SqlConnection(connectionString))
{
var bulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.FireTriggers | SqlBulkCopyOptions.UseInternalTransaction, null);
bulkCopy.DestinationTableName = tableName;
connection.Open();
bulkCopy.BatchSize = batchSize;
bulkCopy.BulkCopyTimeout = 0;
bulkCopy.WriteToServer(dt);
connection.Close();
}
return true;
}
}
