We have a web application that calls a business/data layer to do
database updates. There is a lot of coordination in the web page to
handle id's updated from one class to be used as child id's in other
classes. Here's the scenario.
Using EnterpriseLibrary 2.0.
Surround all class updates with TransactionScope.
Call Update on ClassA - works correctly
Set ChildID (ForeignKey) on ClassB and Call Update - works correctly
Commit Transaction Scope - works correctly.
If there are never any problems with the inner update, it works as
planned, the problem occurs when there is a problem with the update on
ClassB.
Call Update on ClassA - works correctly
Set ChildID (ForeignKey) on ClassB and Call Update - problem with
other data (invalid cast, etc)
Dispose Transaction Scope - works correctly.
In this case, it works as planned, neither record is saved to db, but
when we bring back the page to the user, and they correct the data, on
this second attempt is when the problem occurs.
Call Update on ClassA - works correctly
Set ChildID (ForeignKey) on ClassB and Call Update - problem with
other data (invalid cast, etc)
Dispose Transaction Scope - works correctly.
Display error to user, and they correct bad data
Call Update on ClassA - works correctly
Set ChildID (ForeignKey) on ClassB and Call Update - Get a FOREIGN KEY
constraint violation 'The INSERT statement conflicted with the FOREIGN
KEY constraint ...'
I'm not sure why the inner update fails the second time because the id
generated from the first update is not recognized, like they are in
different scopes.
fyi, I am using a SCOPE_IDENTITY() to grab the newly inserted id's