Cannot execute sqlbulkcopy inside TransactionScope

Topics: Data Access Application Block
Oct 17, 2007 at 7:00 PM
without causing the Transaction to be promoted to a distributed trans (MS DTC). Problem seems to be that it is necessary to give sqlbulkcopy a conn str (creating a new connection I assume), since the existing DAAB connection is not accessible (EntLib 3.1).

Is it possible to access the DAAB connection and pass it to sqlbulkcopy?

Can I reasonably replace the sqlbulkcopy with UpdateDataSet and get comparable performance?

Also, will this be fixed in the future releases of DAAB?

Kirk Spearman
Jul 30, 2010 at 9:50 AM

+1. I've been searching for an answer to the question for a long time. Still no progress...

Jul 30, 2010 at 11:01 AM

Are you strictly after using SqlBulkCopy inside TransactionScope?  I can't seem to make it work but it's possible to include it in a SqlTransaction.  Sample code:

Database db = EnterpriseLibraryContainer.Current.GetInstance<Database>();
DbConnection connection = db.CreateConnection();
    using(DbTransaction transaction = connection.BeginTransaction())
           DbCommand command = //create command;
           db.ExecuteNonQuery(command, transaction);
           SqlBulkCopy bulkCopy = new SqlBulkCopy(connection as SqlConnection, SqlBulkCopyOptions.KeepIdentity, transaction as SqlTransaction);
           //configure bulkCopy properties and perform operations against it

Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.

Jul 30, 2010 at 2:57 PM
It' desirable to use TransactionScope, as I use it in integration test. So I create transaction scope in SetUp and dispose it in TearDown. I need this so that no changes are made to the database during tests.
Aug 2, 2010 at 8:49 AM

Unfortunately, I don't see a way how to do it using the TransactionScope.  Executing various commands inside a TransactionScope using pure ADO.NET or purely the EntLib way would use the same connection but with your requirement, there's just no exposed method in DAAB to get the connection that it is currently using.


Sarah Urmeneta
Global Technology and Solutions
Avanade, Inc.

Aug 2, 2010 at 9:53 AM
OK, thanks. Seems like I need to create another test fixture class specially for testing SqlBulkCopy and use standard SqlTransaction there.