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)?
A few details I wanted to add, but forgot:
This is for .Net v3.5; I'm using Enterprise Library for all database interactions.
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).