TransactionScope by several methods is not an ACID?

Question

I have a code like this:

using (TransactionScope transactionScope = new TransactionScope()) { SetDefaults(products); // Map the SizeCollectionIds of the products _dataAccess.MapProductSizes(products); // Mass update and insert missing parent records to the database _dataAccess.UpdateParents(products); // Get ids of parent products that were newly inserted _dataAccess.PopulateParentProductByParentSku(products); // Insert children into database _dataAccess.InsertProducts(products); // Insert the UPCs into the database _dataAccess.InsertUPCs(products); // Get Product Ids of newly inserted records _dataAccess.PopulateProductIds(products); // Get just the parent products to insert the brands List<ParentProduct> parents = (from prod in products select prod.ParentProduct).Distinct().ToList(); // Insert ParentProductBrand record _dataAccess.InsertParentProductBrands(parents); // Insert the custom attribute records _dataAccess.InsertProductCustomAttributes(products); transactionScope.Complete(); } 

What I intend is that if an error occurs in the methods called in the transaction area, the transaction is rolled back, but after some testing it seems that this is not the case, and my data ends up half-baked. Is there something I'm missing? Do I need to exchange data access calls in the methods themselves in my own TransactionScopes in order to make this work?

Answer 1

It looks like several instances of database connections are being created at your DataAccess level. Try creating a database connection in the constructor of the DataAccess class and use it in your DataAccess methods. You can read this blog post.