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

[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