Error handling for "Go" bulk copy operation (SqlBulkCopy): Transaction Commit, Rollback

Original address: http://blog.csdn.net/westsource/article/details/6658109

By default, the bulk copy operation is performed as a standalone operation. The bulk copy operation occurs in a non-transactional manner and cannot be rolled back. If you need to roll back all bulk copies or part of it on an error, you can either use SqlBulkCopy managed transactions, perform a bulk copy operation in an existing transaction, or enlist it in system.transactions Transaction .

Because different batches are executed in different transactions, if an error occurs during a bulk copy operation, all rows in the current batch are rolled back, but rows in the previous batch remain in the database. (All batches copied before the error point are committed, roll back the currently copied batch, and abort the bulk copy operation before any other batches are processed.) ）

For example: bulk copy 100 data to the database summary, BatchSize set to 10. No 10 data is copied as a transaction, the entire 100 data copy operation is divided into 10 separate transactions. If you copy to the 56th data (6th transaction), an error occurs. The first 50 data is committed to the database, and only the transaction that is faulted is rolled back.

If the entire bulk copy operation needs to be rolled back because of an error, or if the bulk 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 =NewSqlConnection (connectionString))                 {Destinationconnection.open (); using(SqlTransaction transaction =destinationconnection.begintransaction ()) {                    using(SqlBulkCopy bulkcopy =NewSqlBulkCopy (destinationconnection, Sqlbulkcopyoptions.default, transaction)) {Bulkcopy.batchsize=Ten; Bulkcopy.destinationtablename="dbo. BulkCopyDemoMatchingColumns"; Try{bulkcopy.writetoserver (reader);                        Transaction.commit (); }                        Catch(Exception ex) {Console.WriteLine (ex).                            Message); Transaction.                        Rollback (); }                        finally{reader.                        Close (); }                    }                }            }

Sqlbulkcopyoptions Attribute Members:

Default	Use default values for all options.
	KeepIdentity	Retains the source identity value. If not specified, the target is assigned an identity value.
	Checkconstraints	Please check the constraints while inserting the data. By default, constraints are not checked.
	Tablelock	Gets a bulk update lock during a bulk copy operation. If not specified, row locks are used.
	Keepnulls	Leave null values in the target table, regardless of the default value setting. If not specified, the null value is replaced by the default value (if applicable).
	Firetriggers	When specified, causes the server to fire the Insert trigger for rows inserted into the database.
	Useinternaltransaction	If specified, each batch of bulk copy operations occurs in the transaction. If this option is indicated and a SqlTransaction object is provided for the constructor, ArgumentException occurs.

For details, please refer to:

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

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

Error handling for "Go" bulk copy operation (SqlBulkCopy): Transaction Commit, Rollback