Geek Answers Handbook

DataAdapter.Fill () behavior for a row deleted in a data source

I use the architecture of the DataSet / DataTable / DataAdapter to mediate between the database and model objects that have their own support (they are not supported by DataRow). I have a DataAdapter with AcceptChangesDuringFill = False , AcceptChangesDuringUpdate = False and FillLoadOption = OverwriteChanges . Here is my understanding of the DataAdapter model under these conditions:

DataAdapter.Update ()

  • DataRowState.Added will trigger InsertCommand
  • DataRowState.Modified will cause DataRowState.Modified to fire
  • DataRowState.Deleted will cause DataRowState.Deleted to fire

DataAdapter.Fill ()

  • Any row in the returned result set whose primary key matches an existing row in the DataTable will be used to update this row, and this row state will always be DataRowState.Modified , even if the returned row is identical to the current row
  • Any row in the returned result set whose primary key does not match any existing row will be used to create a new row, and this row state will become DataRowState.Added
  • Any row in a DataTable that does not match a row in the returned result set will remain on DataRowState.Unchanged

Given that I am right with this mental model, suppose I want to use Fill() to notify about deleted rows in the data source. Also, suppose the SelectCommand parameters SelectCommand not return the entire table. I assume that I have two options:

  • Find all the rows that should have been updated with Fill() but still DataRowState.Unchanged (relies on my unchecked italic assumption above). These rows have been deleted in the data source.
  • Remove all matching rows from DataTable to Fill() ; any row that does not appear again has been deleted in the data source. This loses the distinction between DataRowState.Added and DataRowState.Modified , which is saved using the first method.

So my questions are:

  • Is my DataAdapter model above correct according to the property values ​​marked at the top?
  • What option should I look for to find deleted rows? I would prefer the former, but it relies on my assumption that all returned rows will be set to DataRowState.Modified , even if the row is identical; is this a safe guess?
  • Am I really doing all this wrong?
+4
source share
2 answers

It turns out that my assumption is wrong - if the row returned by SelectCommand is exactly the same as the row already in the DataTable , that row remains marked as DataRowState.Unchanged . Therefore, the correct procedure removes the rows from the DataTable before calling Fill() and determines the fate of the row by comparing the new rowset DataRowState.Added with the first list of rows.

+1
source

I see a similar problem, but I can’t use .Clear because the DataTable bound to a user interface list, and .Clear followed by .Fill causes the list to lose the current user selection. So I implemented a (ugly) workaround that basically consists of

  • changing a field in a DataTable to a value that I know this field will never be
  • running .Fill
  • delete 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 welcome.

+1
source

More articles:


All Articles