2

I have a SQL Bulk copy function which has following code to create a bulk copy instance. 

 SqlBulkCopy bulkCopy = new SqlBulkCopy((SqlConnection) WorkConnection,
            SqlBulkCopyOptions.FireTriggers, (SqlTransaction) WorkTransaction);

"WorkTransaction" is an external transaction. What will happen to it

  1. If we use "using" block around the SqlBulkCopy. Will it dispose this external transaction ?
  2. If we call close function ( "bulkCopy.Close();"), will it commit/dispose external transaction ?

Any help would be appreciated!

Thanks

Relativity
  • 6,690
  • 22
  • 78
  • 128
  • possible duplicate of [Rollback for bulk copy](http://stackoverflow.com/questions/2216254/rollback-for-bulk-copy) – saarrrr Sep 11 '15 at 21:09
  • I highly doubt it as that would defeat the point, but it sounds easy to test for yourself. Fire up SQL Profiler and run your code. If you see a `COMMIT`, the answer is yes, if you don't, then the answer is no. – dmeglio Sep 11 '15 at 21:11
  • Calling `Dispose()` (Putting it in a "using" block) calls the `Close()` function. See the Remarks section of the [Close method](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.close(v=vs.110).aspx). – Scott Chamberlain Sep 11 '15 at 21:49

1 Answers1

3

No. The point of supplying an external transaction to SqlBulkCopy is to allow other operations to be included in the transaction, so unless an error occurs, SqlBulkCopy will not terminate the transaction.

From the MS Doc:

You can specify an existing SqlTransaction object as a parameter in a SqlBulkCopy constructor. In this situation, the bulk copy operation is performed in an existing transaction, and no change is made to the transaction state (that is, it is neither committed nor aborted). This allows an application to include the bulk copy operation in a transaction with other database operations.

RBarryYoung
  • 55,398
  • 14
  • 96
  • 137
  • From what you just pasted, why are you saying "unless an error occurs"? It sounds like even if an error occurs it doesn't affect the transaction state? – dmeglio Sep 11 '15 at 21:24
  • thanks...I ahve read this. But it does not talk about closing Bulkcopy. – Relativity Sep 11 '15 at 21:38
  • Yes it does, *"no change is made to the transaction state"* that means it will not affect the state, even if you dispose/close the class. – Scott Chamberlain Sep 11 '15 at 21:47