I have a set of parent-child-grandchild-greatgrandchild-greatgreatgrandchild  etc tables.  Each of these levels have a cost, and the cost is determined from the bottom level child table.  I have in place some code on the BusinessObject AfterSave event, to grab the Child record that got saved, parent record, then grab all of its children to determine its new cost (such as changing a quantity etc). When this parent saves, it's new total gets rolled up to its parent in the same manner, an AfterSave event on the BusinessObject, and so on and so on untill we get to the top child, in which we calculate the new totals for the Top level, and call a method to refresh its data without reloading its DataTable (that was interesting ).

The Problem I'm running into is when Im Importing all of the Data in this Parent-Child-grandchild etc into the database with a transaction.  I would like to be able to save all of these BusinessObject's with a transaction, and then Commit or Rollback when Im done.  The problem is on the AFterSave of the first child record.  It's AfterSave event, and when it tries to read its Parent record, it just hangs because that record has not been committed in the transaction.  If I turn off transactional saving, it works fine, I just lack the transactional support.

Is there an easy way around this, Or is my methodology of what I'm doing just wrong?

Thanks.

Have a good Memorial Day Weekend Guys.

Robin Giltner

Nope, your methodology is good, you just need to change the isolation level.  Something like Chaos, or some other isolation level that will allow you to make dirty reads will be what you want.  Check the documentation on the different isolation levels.

I've been running through the Isolation levels, using Chaos results in

I thought using ReadUnCommitted would work for me, or at least it would let me fill by PrimaryKey of a record not Committed yet, but it still hangs on ParentBO.FillbyPrimaryKey method.

I guess since the parent child relations are enforced with a cascade delete, dropping the Transaction, and just deleting the Parent most record would also remove the child records untill I can get this figured out.

Thanks Ben.

Robin Giltner

Hi Robin,

Just a thought - it sounds as though you could fire an update trigger in SQL when the bottom child changes and have the database do the calculation for you.

Cheers, Peter

Thats a good thought, but unfortunatly the application is going to use a MS SQL 2005 Express Edition as a backend, which I don't think supports triggers.

Thanks

Robin Giltner

Old post but I wanted to add that SQL Express does support triggers, if anyone ran across this.

Keith Chisarik

Yeah, thanks for the clarification, Keith.  Good to know