Bulk Inert In Sql Server 2005 Using Dot Net 2

0.00 avg. rating (0% score) - 0 votes

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();           

 try
            {
              
// 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.

 
 
0.00 avg. rating (0% score) - 0 votes

W@rfi

Owner of this blog site. Have expertise on Microsoft technologies.

You may also like...

2 Responses

  1. Muhammad Abubabakar Dar says:

    Cool Keep it up 🙂

  2. admin says:

    Thanks buddy, being quite lazy in posting and had a lot of items that I need to post yet 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *