Bulk Inert In Sql Server 2005 Using Dot Net 2
Data bulk insertion is a requirement often faced by developers. Using dot net 2 and sql server 2005, Microsoft has provided a nice feature for bulk insertion using SqlBulkCopy class. It is really fast and indeed no comparison of performing instead in transaction or row by row. Coupling SqlBulkCopy with Transaction is very nice. Here how it works:
using (SqlConnection lConnection = new SqlConnection(“connection_string goes here”))
{
connection.Open();
SqlTransaction lTransaction =lConnection.BeginTransaction();
{
// BulkUpload actually starts here
using (SqlBulkCopy lCopy = new SqlBulkCopy(lConnection,
SqlBulkCopyOptions.Default, lTransaction))
{
lCopy.DestinationTableName = targetTable; // the table to which data is to be written
lCopy.ColumnMappings.Add(New SqlBulkCopyColumnMapping(SourceColumnName, DestinationColumnName));
lCopy.BatchSize= BATCH_SIZE; // records to be written in one batch
lCopy.NotifyAfter = 200; // in number of records
lCopy.WriteToServer(SourceTable);
lTransaction.commit();
catch
{
lTransaction.Rollback();
throw;
}
The above code writes a source data table to destination database table on the basis of connection string and table name. We can set the batch sieze, we may also write an event so that after each batch we are notified of the success. Its easy and certainly very fast.
Cool Keep it up
Thanks buddy, being quite lazy in posting and had a lot of items that I need to post yet