Related insert fails in transationscope

Question

I am using TransactionScope to add a object's data to one database.

pseudo code:

using (TransactionScope trx = new TransactionScope())
{
   SqlConnection con = DL.GetNewConn();
   int newParentRecordID = InsertParentIntoTableA(object parent, con);

   foreach(obj child in parent.childrenObjects)
   {
       child.ParentID = newParentRecordID ;
       int newChildRecordID =  InsertChildIntoTableB(object child, con);
   }
   trx.Complete();
}

I get exception at InsertChildIntoTableB() with the error being that the ParentID in TableB does not have a matching Primary Key entry in TableA.

The connection is reused.

How do I get around this? Doing a SELECT WITH (NOLOCK) on TableA does show the newly inserted parent Record, but the following child record insert cannot see it.

Edit to clarify: In the foreach loop, I already have the inserted, but uncommited new ParentID. The problem is the the insert to the the child's TableB fails as the FK in TableB for parent TableA cannot see the uncommited new TableA PK ID.

Answer 1

In case you have an identity column you can try to use OUTPUT INSERTED clause in your INSERT statement.

Here's a good article on this subject.

Answer 2

A side note - you have to complete transaction explicitly with TransactionScope.Complete instance method or otherwise it is rolled back.