SqlBulkCopy and transactions

[Oscar Thornell]

Hi,

I am trying to enlist an instance of SqlBulkCopy into a transaction. When I
executes and try to write to the database I´ll get an exception indicating a
transactional deadlock.

"Transaction (Process ID 58) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction."

1) Is it possible to do a cast of the transaction has I do below?
2) If the following approach is a dead end. How would you guys design a
solution? I am bound to use the transactional mechanism provided by
Enterprise Library and the already implemented way that local transactions
are created!?

The transaction used is started in the business layer of the application:

IDbConnection conn =
DatabaseFactory.CreateDatabase().CreateConnection();
conn.Open();
IDbTransaction transaction = conn.BeginTransaction();

A controller class then manages the business case and performs operations...

Call 1 --> Delete(obj, transaction);

private void Delete(Domain obj, DbTransaction transaction)
{
DbCommand command = db.GetStoredProcCommand("DeleteDomain");
db.AddInParameter(command, "@Id", DbType.Int32, obj.Id);
db.ExecuteNonQuery(command, transaction);
}

Call 2 --> SaveBatch(obj.Data, (SqlTransaction)transaction);
//Observe the cast to SqlTransaction, the underlaying object has
however always been an SqlTransaction

private void SaveBatch(List<SomeClass> list, SqlTransaction
transaction)
{
SqlBulkCopy bulk = new SqlBulkCopy(transaction.Connection,
SqlBulkCopyOptions.Default, transaction);
CreateMappings(bulk);
bulk.WriteToServer(table);
}

Regards
/Oscar