Error Handling of the batch copy operation (SqlBulkCopy): transaction commit, rollback, and sqlbulkcopy transactions

Source: Internet
Author: User

[Switch] error handling of the batch copy operation (SqlBulkCopy): transaction commit, rollback, and sqlbulkcopy transactions

Address: http://blog.csdn.net/westsource/article/details/6658109

By default, the batch copy operation is executed as an independent operation. Batch replication is performed in a non-transactional manner and cannot be rolled back. If you need to roll back all batch copies or some of them in case of an error, you can use SqlBulkCopy to host the transaction and perform the batch copy operation in the existing transaction, orSystem. Transactions Transaction.

Because different batches are executed in different transactions, if an error occurs during the batch copy operation, all rows in the current batch will be rolled back, however, rows in the previous batch are retained in the database. (All the batches copied before the error point are submitted. roll back the batches of the current copy and stop the batch copy operation before processing any other batches .)

For example, you can copy 100 pieces of data to the database in batches and set batchsize to 10. if there are no 10 data copies as a transaction, the entire 100 data replication operation is divided into 10 independent transactions. An error occurred when copying 56th data records (6th transactions. Then, the first 50 pieces of data are committed to the database, and only the wrong transactions are rolled back.

If the whole batch copy operation needs to be rolled back due to an error, or the batch copy should be executed as part of a larger rollback process, you can provide the SQLTransaction object to the SqlBulkCopy constructor.

Example:

using (SqlConnection destinationConnection = new SqlConnection(connectionString))            {                destinationConnection.Open();                 using (SqlTransaction transaction = destinationConnection.BeginTransaction())                {                    using (SqlBulkCopy bulkCopy = new SqlBulkCopy( destinationConnection, SqlBulkCopyOptions.Default,                               transaction))                    {                        bulkCopy.BatchSize = 10;                        bulkCopy.DestinationTableName = "dbo.BulkCopyDemoMatchingColumns";                        try                        {                            bulkCopy.WriteToServer(reader);                            transaction.Commit();                        }                        catch (Exception ex)                        {                            Console.WriteLine(ex.Message);                            transaction.Rollback();                        }                        finally                        {                            reader.Close();                        }                    }                }            }

SqlBulkCopyOptions attribute member:

 

Default Use the default value for all options.
  KeepIdentity Reserve the source ID value. If this parameter is not specified, the target assigns an id value.
  CheckConstraints Check constraints while inserting data. By default, constraints are not checked.
  TableLock Obtain the update locks in batches during the bulk copy operation. If not specified, the row lock is used.
  KeepNulls Retain null values in the target table regardless of the default value. If not specified, the null value is replaced by the default value (if applicable ).
  FireTriggers If this parameter is specified, the server triggers the insert trigger for the row inserted into the database.
  UseInternalTransaction If this parameter is specified, each batch of batch copy operations will occur in the transaction. ArgumentException occurs if this option is specified and the SqlTransaction object is provided for the constructor.

For details, refer:

Http://msdn.microsoft.com/zh-cn/dynamics/tchktcdk.aspx

Http://msdn.microsoft.com/zh-cn/partners/system.data.sqlclient.sqlbulkcopyoptions.aspx

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.