locked
DBConcurrencyException - Accounting for as opposed to Handling... RRS feed

  • Question

  • Okay,

    My system uses the Dataset as a base but greatly enhances it.  It currently is designed to handle AutoIncrement Field Updates (Identity Columns), as well as other DB relation integrity during the update process.  If you add or update or delete a row, it makes sure to maintain the relationship integrity across multiple tables (and as I'm working on it, across multiple datasets, for cross-database dependency).

    However, with some circumstances I have used TRIGGERS to maintain this integrity in the database, but in doing so it seems it affects (negatively) the dataset's ability (via DbDataAdapters) to commit it's data to the database.

    Take three tables, two parents, one child.  One of the parents is not CASCADE on delete or update, due to the limitations in Sql Server, while the other is.  So P1 cascades to C1, while P2 maintains a INSTEAD OF DELETE TRIGGER, which Sets the C1.P2Code columns to NULL on P2 Delete Row. 

    Everything works fine here.  But I load the data into a dataset on a form, and delete one of the P2 Rows and then attempt to UpdateAll() on the TableAdapterManager. 

    My system gathers child and parent rows during the update process to make sure that Parent IDENTITY rows are added, refreshed, and then update the child rows with the actually identity value as opposed to the .Net -1,-2 default IDENTITY values.  This works fine and is very helpful.  The Next Step though is to delete a row.  Well, WHen I process my tables, and the Parent Table is Running the Delete Process on its one row that i've deleted, it checks to see if there are any children associated with that row.  It proceeds to update that row if necessary.  If you use the ForeignKeyConstraint in the Dataset Designer, the Child Row is automatically affected at the moment of DataRow.Delete(), however if they are just DataRelation (with no constraint) the child row is ignored, but this can affect some databases adversely. Working in rules and other such constraint data is important, and I will be working with the current system as it is, but I noticed a problem:

    TRIGGERS

    Basically, If I update the Child Row (via the TableAdapter.Update(row[]) method) first, when I execute the DbDataAdapter.Update() on the Deleted row, I get a DBConcurrencyException that the DeleteCommand Affected 0 instead of 1 rows.  If I delete the Parent Row First, I get a DBConcurrencyException theat the UpdateCommand Affected 0 instead of 1 rows.

    First: By Updating first the C1 Table with the Updated Child Row, hows does this affect the Upate to the P2 Table with the Deleted Parent Row to cause a Concurrency Error?

    Second: By Deleting P2 Row First, the trigger automatically updates the C1 Row, which means that the UPdate to the C1 row, is not the same version as what's in the database, and thus I understand the Concurrency Error.  However, I need to have the trigger in place to affect the cascading update to the child on a delete from the parent, but with such triggers in place I can't seem to preserve the data on a delete from the database.

    Update then Delete = DB Concurrency Exception

    Delete then Update = DB Concurrency Exception

    How Else can I save this data to the Database WHILE MAINTAINING Accurate Data in the dataset.  (I could ignore the problem as a DataRelation, ie: no in memory Constraint), but then I would have to refresh the child rows after the delete command was called.

    Surely someone else has come across a situation like this where the TRIGGER is negatively affecting the Dataset update process.

    Thanks

    Jaeden "Sifo Dyas" al'Raec Ruiner

     


    "Never Trust a computer. Your brain is smarter than any micro-chip."
    PS - Don't mark answers on other people's questions. There are such things as Vacations and Holidays which may reduce timely activity, and until the person asking the question can test your answer, it is not correct just because you think it is. Marking it correct for them often stops other people from even reading the question and possibly providing the real "correct" answer.
    Monday, August 2, 2010 9:09 PM

Answers

  • Hello JaedenRuiner,

    Thanks for your post! Based on my understanding, even though you checked the data integraty and make sure the parent identity is there before inserting a child data row as well as deleting a child rows before deleting a parent data record, you still encountered a DBConcurrency exception in your grogram.

    When using triggers in this scenario, one solution is to refresh the data in your dataset manually or automatically. You could also add a delay time so that the trigger has enough time to execute the command.

    Besides, when encountering DBConcurrency exception, one recommendation is to handle the DBConcurrency exception in your exception handling part. Please take a look at this article for what I mean: http://msdn.microsoft.com/en-us/magazine/cc188748.aspx

    Best regards


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    If you have any feedback, please tell us.
    Welcome to the All-In-One Code Framework!
    Monday, August 9, 2010 3:51 AM