Managing a Network failure during a Transactional SqlBulkCopy

ado.net c# sql sqlbulkcopy

Question

I'm researching SqlClient's SqlBulkCopy in ADO.Net and have the following questions.

What will happen, if there was a network error during a SqlBulkCopy operation, running under as part of a transaction over a huge number of records?

Will the transaction be left open (neither committed, nor rolled back) in the server, until we manually kill it?

What is the best approach for sending a large number of records in two DataTables (InvoiceHeader, InvoiceDetails) in a DataSet to respective SQL Server tables(InvoiceHeader, InvoiceDetails)?

Thank you.

EDIT:

A few details I wanted to add, but forgot:

This is for .Net v3.5; I'm using Enterprise Library for all database interactions.

Accepted Answer

Assuming you are using a TransactionScope, my understanding is that no, the transaction will not be left open, because SQL Server will detect the ambient transaction and auto enlist. This means that the worst case is that the transaction times out, rolling back. You can change the transaction binding to specify what to do in the event of a timeout (you probably want explicit unbind).




Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why