Sequence of Adding and Removing Records from child Table

9 posts, 0 answers
  1. devoas
    devoas avatar
    63 posts
    Member since:
    Dec 2009

    Posted 18 Dec 2010 Link to this post

    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,

  2. TSE
    TSE avatar
    380 posts
    Member since:
    Sep 2008

    Posted 18 Dec 2010 Link to this post

    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

  3. devoas
    devoas avatar
    63 posts
    Member since:
    Dec 2009

    Posted 19 Dec 2010 Link to this post

    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








  4. TSE
    TSE avatar
    380 posts
    Member since:
    Sep 2008

    Posted 20 Dec 2010 Link to this post

    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

  5. devoas
    devoas avatar
    63 posts
    Member since:
    Dec 2009

    Posted 20 Dec 2010 Link to this post

    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

  6. TSE
    TSE avatar
    380 posts
    Member since:
    Sep 2008

    Posted 20 Dec 2010 Link to this post

    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

  7. devoas
    devoas avatar
    63 posts
    Member since:
    Dec 2009

    Posted 20 Dec 2010 Link to this post

    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


  8. TSE
    TSE avatar
    380 posts
    Member since:
    Sep 2008

    Posted 20 Dec 2010 Link to this post

    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

  9. Alexander
    Admin
    Alexander avatar
    720 posts
    Member since:
    Sep 2012

    Posted 22 Dec 2010 Link to this post

    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

    Accelerate your learning with industry's first Telerik OpenAccess ORM SDK. Download today.

Back to Top