0

Is it safe to use SqlBulkCopy in a TransactionScope? I am aware of this question but it does not answer the question and its pretty old. If its not possible what is a good alternative?

Community
  • 1
  • 1
Luke101
  • 63,072
  • 85
  • 231
  • 359

1 Answers1

2

It seems that if the TransactionScope is created before the sql-connection used for the SqlBulkCopy is created and opened the transaction-handling mechanics of the TransactionScope are used. Which means that you manually need to create your own SqlConnection and open it which you later use for the SqlBulkCopy instance.

using (var ts = new TransactionScope())
using (var sqlCon = new SqlConnection(conStr))
{
  sqlCon.Open(); // ensure to open it before SqlBulkCopy can open it in another transactionscope.
  using (var bulk = new SqlBulkCopy(sqlCon))
  {
    // Do you stuff
    bulk.WriteToServer...
  }      

  ts.Complete(); // finish the transaction, ie commit
}

Note 1: Changed answer heavily after reading up a bit on TransactionScope vs SqlTransaction

Note 2: This answer is purely from reading, I have no empirical evidence of the above as of now.

Note 3: There seems to be a new answer in the question you were referring to since my first reply, he gives the same answer as I do here, so I hope he has empirical evidence :) (ie https://stackoverflow.com/a/33311494/691294)

Community
  • 1
  • 1
flindeberg
  • 4,887
  • 1
  • 24
  • 37
  • How do I get a transaction object from a TransactionScope? – Luke101 Oct 23 '15 at 13:51
  • @Luke101 You cannot easily do that, if at all, but it seems like opening your `SqlConnection` inside a `TransactionScope` does the same thing. See my updated answer for details. – flindeberg Oct 23 '15 at 22:27