How to to handle a set that had a row added and then deleted before Adapter Update?

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I have a scenario where I am processing a batch of changes to a table. The
changes can result in an Insert or a Delete. So I Fill a DataSet with the
table's current contents, then I process the changes, then call Update on my
SqlAdapter. Psuedo Code:

Fill(DataSet, MyTable)
Foreach( Statement in UpdateCollection)
{
Foreach(Row in DataSet.Tables[MyTable].Rows)
{
if(Statement matches Row & !found)
Row.Delete()
found=true
}
if(!found)
DataSet.Tables[0].Rows.Add(NewRowFromStatement)
}
Update(DataSet, MyTable)

Unfortunately, there is a small chance that two records in the current batch
of changes are related, basically one record will be the delete for one of
the inserts in the update collection and this seems to cause a host of
problems I cannot get arround. If I call Update, then since the Deleted row
never had an original version the DeleteCommand can't find an original row
to delete from the source table and throws an exception (Deleted 0 rows,
expected 1). I had thought maybe if I found a row that was state Added and I
needed to Delete it I could just call Remove, or
AcceptChanges->Delete->Acceptchanges to cause the row to essentially
disappear from the scope of the update. But this blows up the enumeration
since it alters the Rows collection. I think my only option is to add a new
column to my table MarkedForDelete and change the code to only Add and
Update then make a seperate process to cull rows marked for delete
asynchronously.

Any tips?

Thanks,
Jason
 
Jason,

You can select the rows using the DataRow.RowState and then do the update of
them seperatly in the way you want, which is a standard way for related
datatables.

An update of the dataadapter is very much overloaded, you can do as well a
collection of datarows.

(In this case you need the dataset.acceptchanges at the end)

Cor
 
Back
Top