DBConcurrencyException With ON DELETE CASCADE

22 Dec 2006 9:39 AM

Andrea Caldarone

Hi all,

in my SQL Server 2005 database I've two tables linked by the classic
relationship PrimaryKey->ForeignKey, I've added the clause "ON DELETE
CASCADE" to the ForeignKey constraint, so when I delete a record in the
parent table, SQL deletes for me the records in the child table.
This two tables are part of a dataset in my Visual Studio .Net project, they
populate two DataTables via two distinct sqlDataAdapter with the .FillSchema
and .Fill method of the adapters, in this way the ForeignKey contraint is
build in my dataset, infact if I delete a row in the parent DataTable,
Visual Studio deletes the rows in the child DataTable.
But when I try to call the .Update method of the two Adapters I receive an
exception: DBConcurrencyException I think that this exception is trown
because Visual Studio tries to delete some rows that SQL Server has just
deleted in response to the deletion of the the parent row operated by the
DeleteCommand of the .Update Method of the parent sqlDataAdapter.

24 Dec 2006 10:49 AM

Jesús López

You can subscribe to RowUpdated event of parent table SqlDataAdapter. In
the event handler you can navigate through parent-child relation to find all
children rows, then you can call AcceptChanges on these rows. In that way
the child table SqlDataAdapter will not execute the DeleteCommand and you
will not get DbConcurrencyException.

Pseudo code:

event handler ParentTableAdapter.RowUpdated
    if action is delete then
        for each child row in row.GetChildRows
            row.AcceptChanges
        next row
    end if
end event handler

Regards:

Jesús López

Show quote

"Andrea Caldarone" <software-livqu***@3techsrl.com> escribió en el mensaje
news:Of5qG0aJHHA.4376@TK2MSFTNGP03.phx.gbl...
> Hi all,
>
> in my SQL Server 2005 database I've two tables linked by the classic
> relationship PrimaryKey->ForeignKey, I've added the clause "ON DELETE
> CASCADE" to the ForeignKey constraint, so when I delete a record in the
> parent table, SQL deletes for me the records in the child table.
> This two tables are part of a dataset in my Visual Studio .Net project,
> they populate two DataTables via two distinct sqlDataAdapter with the
> .FillSchema and .Fill method of the adapters, in this way the ForeignKey
> contraint is build in my dataset, infact if I delete a row in the parent
> DataTable, Visual Studio deletes the rows in the child DataTable.
> But when I try to call the .Update method of the two Adapters I receive an
> exception: DBConcurrencyException I think that this exception is trown
> because Visual Studio tries to delete some rows that SQL Server has just
> deleted in response to the deletion of the the parent row operated by the
> DeleteCommand of the .Update Method of the parent sqlDataAdapter.
>