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