Sequence of Adding and Removing Records from child Table

[devoas]

Hi,

Kindly advise what is the steps/sequence in which Open Access Add or remove Records from Child Table.  We have a collection, in which we remove couple of records and added new one. Now while Updating to Database we expect OA to follow same sequence but we found that it first insert new record and then it Remove/delete.   Since we have a Unique key index in Database, so this creates a problem..... 

Example:  Suppose we have follow Records in Child Table.

PkId       KeyValue
1 12
2. 13

KeyValue is unique Index..

Now once we Edit this and try to Remove KeyValue = 12 and Add again to asign new Id. On Save we get database error of Unique Key Voilation. Because OA, first try to Insert New Record with KeyValue=12  rather than deleting PKID=1

How to address such scenario to have Delete/Insert in specified sequence.

Thanks,
Devoas,

[IT-Als]

Hi Devoas,

The only solution I can think of is to carry out the operation in two transactions

Transaction1.Begin()
scope.Remove(childObject);
Transaction1.Commit();

// Generates a new id (pkId) for same key value = 12
Transaction2.Begin
scope.Add(childObject);
Transaction2.Commit

Of cause you will have to do some manual recovery handling in case Transaction2 fails. However, why should it.

Regards

Henrik

[devoas]

Hello Henrik,

Thanks for your reply,. but I cannot address this in two transaction block. Because Master and child updated in single transaction block...

The reason for having this requirement can be well understood by following example.

We have a list of employees, User allocate these employees to different Shifts....  An Employee can  allocated in only one shift for a day. (we use unique index)
Master Table : DailyShift  (PKId,   Date)
Child Table   : DailyShiftEmployess (Pkid,   DailyshiftId,   EmployeeId, Shiftid)

So a transaction may look like following

1 19-Dec-2010 (Master)

1 1 Emp1 shft1
2 1 Emp2 shft2
3. 1 Emp3 shft3

User once saved and then edit this record and deleted grid row three and added again as
4 1 Emp3 shft2

Once the save is clicked this changes is transfered to Database............... I used following

Transaction1.Begin() oDailyShift.DailyShiftEmployess.Remove(childobject3);
oDailyShift.DailyShiftEmployess.Add(childobject4);
Transaction2.Commit

This throws exception as OpenAccess add the Emp3 Record first rather then deleting and so Emp3 gets duplicated in DailyShift Record.

Hope above would explain more on the issue, please advise.

Regards
devoas

[IT-Als]

Hi Devoas,

Thanks for your clarification.
So, you're saying that each action (edit/save, delete, create/save) in grid does not result a corresponding action on the scope.. add, remove..

What about if you do this:

Scope.Transaction.Begin() oDailyShift.DailyShiftEmployess.Remove(childobject3);
Scope.Transaction.Commit() Scope.Transaction.Begin()
oDailyShift.DailyShiftEmployess.Add(childobject4);
Scope.Transaction.Commit

If you are already handling the remove and add with the code you posted, you should be able to do the above... unless I am misunderstanding something totally here...

Regards

Henrik

[devoas]

Hello Henrik,

Thanks for your reply... As you suggested to use two transaction block. Please advise is it safe to use this practice. As we normally try to have one set of data saved under single transaction block to avoid any incomplete saving due to hardware or power failure. In this case if any such thing happen, data integrity will be at risk.

Please suggest...

Regards
devoas

[IT-Als]

Hello Devoas,

As I pointed out in the my first reply in the thread, the drawback of doing two transactions is loss of data integrity in case of failures.

However, consider the following:
You're deleting first. If this fails, the second transaction (the add operation) should no be carried out either.
If the add fails, you actually do not "loose" anything, since the delete is already carried out. You will probably still have the new row in the grid. So, one can hit Save again.

If you want it to be fault tolerant I think maybe you should go for enlisting the object scope transactions in a System.Transaction transaction. But still, in my opinion, it would be way too far to go, to handle this (in my opinion) potential problem.

Another solution could be to remove the index and do the checks (that thce index did for dupliates/uniqueness) in application code.

Regards

Henrik

[devoas]

Hi Henrik,

Thanks once again for helping on the issue and giving your expert opinion. Actually When I commit Master Object, means all changes done in Master also updated which also contains the total number of employees assigned for a day.... So if first transaction complete and second fail, it will be wrong data in master tables ..... Hence I cannot afford transaction to fail partially, if it fail, complete rollback is acceptable...

Presently we have no other choice to remove the unique index and this is what we have already done till get any proper solution.


Regards
devoas

[IT-Als]

Hi Devoas,

Sorry I wasn't able to help your further. Maybe someone at Telerik can elaborate on the matter.
I have no other workarounds/solutions as the ones presented in this thread.

Regards

Henrik

[Alexander]

Hello guys,

Actually there is another solution that may prove to be better than deleting the index from the database. The ITransaction interface has a method called Flush() which pushes the changes made in the current transaction to the server without persisting them. Any subsequent calls to scope.Add or scope.Remove will result in insert/delete statements executed after the ones already pushed to the server. Having this in mind, you can delete the old objects from the scope, call scope.Transaction.Flush(), add the new objects and commit the transaction. This way the delete and insert operations will be performed in the correct order and the whole transaction will be rolled-back in case of failure.
Hope that helps.

Best wishes,
Alexander
the Telerik team