How to use SqlBulkCopy with transaction on ReliableSqlConnection in sql azure

azure sqlbulkcopy sql-server transactions

Question

Our application on azure cloud service, We need to perform some operations on sql azure using the same transaction, (insert into and SqlBulkCopy) using ReliableSqlConnection to allow Transaction.Rollback If any steps fail

but run this code: using (SqlBulkCopy ContactsDistBulkCopy = new SqlBulkCopy(oConnection, SqlBulkCopyOptions.KeepIdentity, oTransaction))

Ask for system.data.connection and not ReliableSqlConnection ?

Popular Answer

How much data are you passing in Bulk? You might be able to use a Table-Valued-Parameter approach instead of Bulk Copy, and that is simple to include in a transaction. The table you assemble on the client is sent to SQL as a parameter very quickly, and inserts are performed with BULK optimizations on the server. This is appropriate for ~O(10000) rows. This would fit into the ReliableSQLConnection framework, as long as you use the rsc.ExecuteCommand style of invoking the command.

You can find more details at http://msdn.microsoft.com/en-us/library/bb510489.aspx



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