DataAdapter.Fill() behavior for row deleted at the data source

Question

I'm using the DataSet/DataTable/DataAdapter architecture to mediate between the database and my model objects, which have their own backing (they aren't backed by a DataRow). I've got a DataAdapter with AcceptChangesDuringFill = False, AcceptChangesDuringUpdate = False, and FillLoadOption = OverwriteChanges. Here's my understanding of the DataAdapter model under these conditions:

DataAdapter.Update()

• DataRowState.Added will result in the InsertCommand firing
• DataRowState.Modified will result in the UpdateCommand firing
• DataRowState.Deleted will result in the DeleteCommand firing

DataAdapter.Fill()

• Any row in the returned result set whose primary key corresponds to an existing row in the DataTable will be used to update that row, and that row's state will always become DataRowState.Modified, even if the returned row is identical to the current row
• Any row in the returned result set whose primary key doesn't correspond to any existing row will be used to create a new row, and that row's state will become DataRowState.Added
• Any row in the DataTable that doesn't correspond to a row in the returned result set will stay at DataRowState.Unchanged

Given that I'm correct with this mental model, suppose I want to use Fill() to notice deleted rows in the data source. Also, suppose that the parameters of the SelectCommand don't return the entire table. I'm guessing that I have two options:

• Find all the rows that should've been updated by the Fill() but are still DataRowState.Unchanged (relies on my untested italicized assumption above). These rows have been deleted at the data source.
• Clear all relevant rows from the DataTable before the Fill(); any row that doesn't show up again has been deleted at the data source. This loses the distinction between DataRowState.Added and DataRowState.Modified that is preserved with the first method.

So, my questions:

• Is my above model of the DataAdapter correct, subject to the property values I noted at the top?
• Which option should I go with to find deleted rows? I'd prefer the first one, but that relies on my assumption that all returned rows will be set to DataRowState.Modified even if the row is identical; is that a safe assumption?
• Am I going about this all wrong?

Answer 1

I see a similar problem but I cannot use .Clear since the DataTable is bound to a user interface list and .Clear followed by .Fill causes the list to lose the user's current selection. Thus, I implemented a (ugly) workaround which basically consists of

1. changing a field in the DataTable to a value that I know this field will never have
2. running .Fill
3. removing all rows containing this value

In other words:

    For Each drow As DataRow In dset.Tables(0).Rows
        drow.Item("myField") = -1
    Next

    myDataAdapter.Fill(dset)

    Dim drowsRemove = (From drow In dset.Tables(0).AsEnumerable() _
                       Where drow.Field(Of Integer)("myField") = -1).ToList()
    For Each drow In drowsRemove
        dset.Tables(0).Rows.Remove(drow)
    Next

Any suggestions for more elegant solutions are appreciated.

Answer 2

Turns out that my assumption is erroneous—if a row returned by the SelectCommand is exactly the same as a row already in the DataTable, that row remains marked as DataRowState.Unchanged. So the proper procedure is removing rows from the DataTable before calling Fill(), and determining the fate of a row by comparing the new set of DataRowState.Added rows to the former list of rows.