c# convert DbContextTransaction to SqlTransaction

Question

I am trying to use SqlBulkCopy under DbContext. My Sql Connection string has UserId and Password and that is why to pass connection object to SqlBulkCopy I am creating SqlConnection object having a SqlCredential and passing the SqlConnection Object to the SqlBulkCopy. My SqlBulkCopy initialisation looks as below.

using (var conn = new SqlConnection("", cred))
{
   using (var bulkCopy = new SqlBulkCopy("",SqlBulkCopyOptions.CheckConstraints |
SqlBulkCopyOptions.KeepNulls,DbContext.Database.CurrentTransaction))
   { 
    // bulkCopy code 
   }
 }

But the problem here is constructor's third parameter should be a SqlTransaction. In my case I already have my DbContext which is enlist with the Transaction of my Service.

How can I convert DbContextTransaction(DbContext.Database.CurrentTransaction) to a SqlTransaction.

Answer 1

You need a instance of the dbContext or a IDbTransaction to get the UnderlyingTransaction:

DbContext:

var bulkCopy = new SqlBulkCopy("", SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.KeepNulls,
        (myDbContext.Database.CurrentTransaction.UnderlyingTransaction) as SqlTransaction)) ;

---

IDbTransaction:

using (IDbTransaction tran = conn.BeginTransaction())
{
  var bulkCopy = new SqlBulkCopy("", SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.KeepNulls, tran as SqlTransaction);
}

SqlTransaction inherited from DbTransaction!

Note:

By default, a bulk copy operation is performed as an isolated operation. The bulk copy operation occurs in a non-transacted way, with no opportunity for rolling it back. If you need to roll back all or part of the bulk copy when an error occurs, you can use a SqlBulkCopy-managed transaction, perform the bulk copy operation within an existing transaction (as in the DbContext example and passing it as parameter is optional), or be enlisted in Transaction as in the example of the IDbTransaction.

Answer 2

By casting the UnderlyingTransaction:

(SqlTransaction)transaction.UnderlyingTransaction