DataTable: is deleting old DataRows before inserting new safe?

Question

i have a many-to-many relationship table in a typed DataSet. For convenience on an update i'm deleting old relations before i'm adding the new(maybe the same as before).

Now i wonder if this way is failsafe or if i should ensure only to delete which are really deleted(for example with LINQ) and only add that one which are really new.

In SQL-Server is a unique constraint defined for the relation table, the two foreign keys are a composite primary key.

Is the order the DataAdapter updates the DataRows which RowState are <> Unchanged predictable or not? In other words: is it possible that DataAdapter.Update(DataTable) will result in an exception when the key already exists?

This is the datamodel:

Datamodel

This is part of the code(LbSymptomCodes is an ASP.Net ListBox):

Dim daTrelRmaSymptomCode As New ERPModel.dsRMATableAdapters.trelRMA_SymptomCodeTableAdapter
For Each oldTrelRmaSymptomCodeRow As ERPModel.dsRMA.trelRMA_SymptomCodeRow In thisRMA.GettrelRMA_SymptomCodeRows
    oldTrelRmaSymptomCodeRow.Delete()
Next
For Each item As ListItem In LbSymptomCodes.Items
    If item.Selected Then
        Dim newTrelRmaSymptomCodeRow As ERPModel.dsRMA.trelRMA_SymptomCodeRow = Services.dsRMA.trelRMA_SymptomCode.NewtrelRMA_SymptomCodeRow
        newTrelRmaSymptomCodeRow.fiRMA = Services.IdRma
        newTrelRmaSymptomCodeRow.fiSymptomCode = CInt(item.Value)
        Services.dsRMA.trelRMA_SymptomCode.AddtrelRMA_SymptomCodeRow(newTrelRmaSymptomCodeRow)
    End If
Next
daTrelRmaSymptomCode.Update(Services.dsRMA.trelRMA_SymptomCode)

Thank you in advance.

score 2 · Answer 1 · answered Apr 20 '11 at 08:32

2

I think that the DataAdapter in ADO.NET is clever enough to perform the delete/inserts in the correct order.

However, if you really want to ensure that updates are done in the correct order you should do it manually by using the Select method to return an array of data rows for each particular row state. You could then call the Update method on the array of data rows

DataTable tbl = ds.Tables["YourTable"];

// Process any Deleted rows first
adapter.Update(tbl.Select(null, null, DataViewRowState.Deleted));

// Process any Updated/Modified rows
adapter.Update(tbl.Select(null, null, DataViewRowState.ModifiedCurrent));

// Process the Inserts last
adapter.Update(tbl.Select(null, null, DataViewRowState.Added));

answered Apr 20 '11 at 08:32

codingbadger

42,678
13
95
110

Thanks. But that would result in three DB-calls(and three transactions) instead of one. I couldn't RejectChanges when the update fails on the Added-Rows for the already accepted changes on the deleted and changed rows. – Tim Schmelter Apr 20 '11 at 09:21
@TimSchmelter: Couldn't you wrap all the updates in a single transaction within a try/catch block to rollback if any errors occur? – codingbadger Apr 20 '11 at 09:45
Possible but difficult because in a typed dataset this logic is encapsulated in autogenerated TableAdapters. I would have to extend these Adapters in a partial class in a separate file than the autogenerated one and expose the DataAdapter itself to provide the Transaction. But i'd love to avoid this extra-effort ;) – Tim Schmelter Apr 20 '11 at 11:29
Thank, I love this solution. – Athit Upakan Aug 06 '21 at 11:55

score 1 · Answer 2 · answered Apr 20 '11 at 08:37

Not sure about the DA but in theory DB transactions should be performed in the following order Deletes, Inserts, Updates.

looking at msdn the exact wording for the update method is

Blockquote Attempts to save all changes in the DataTable to the database. (This includes removing any rows deleted from the table, adding rows inserted to the table, and updating any rows in the table that have changed.) Blockquote

In regards to your solution of deleting items and possibly re-inserting the same items, typically speaking this should be avoided because it creates a load on the DB. In high volume applications you want to do everything you can to minimize calls to the DB as they are very expensive; computation time, from determining which row updates are spurious, is cheap.

Thanks. Because the user(and there are not many users at all) can only select 1-5 items in the ListBox the extra load for the DB is rather limited. — Tim Schmelter, Apr 20 '11 at 09:26

DataTable: is deleting old DataRows before inserting new safe?

2 Answers2