SQLCE - Update or Add Row

  • Thread starter Thread starter Nathan Mellor
  • Start date Start date
N

Nathan Mellor

This should be simple but it doesn't seem to be.

How can I update or add a row as a concise, atomic operation.

You'd think that filling up a dataset with rows, complete with primary keys,
would do it. That the DataAdapter would see that this row is new and add it,
and this row isn't , so update it. But it doesn't.

Looking at SQL, UPDATE will do the job if the row is there, but nothing if
it is not. INSERT will work if its not there, but will complain about
primary / unique key constraints if it is.

Any ideas for a concise, one step UDATE_OR_ADD?

Nathan
 
Once you've filled your DataSet with the DataAdapter, you can then edit and
add data to this DataSet, then call Update() on the DataAdapter to commit
these changes to the database, a couple of points:-
1. Don't call AcceptChanges, this will mark all your changes as current and
when you call Update there will be no updates to commit to the database
2. Make sure your DataAdapter has valid commands for InsertCommand and
UpdateCommand properties. See the SqlCeCommandBuilder

Peter
 
Peter Foot said:
Once you've filled your DataSet with the DataAdapter, you can then edit
and add data to this DataSet, then call Update() on the DataAdapter to
commit these changes to the database, a couple of points:-
1. Don't call AcceptChanges, this will mark all your changes as current
and when you call Update there will be no updates to commit to the
database
2. Make sure your DataAdapter has valid commands for InsertCommand and
UpdateCommand properties. See the SqlCeCommandBuilder
Click to expand...

Ok. I've used the commandbuilder. Do I have to fill the DataSet or only
FillSchema? FillSchema doesn't seem to work. But loading the whole table
into a DataSet seems rather wasteful. Can't I configure the DataAdapter to
"OverWrite on Duplicates"?

Otherwise I've got to do something like this for every Row:

DataRow r=table.rows.find(key);
if(r!=null)
{
modify
}
else
{
create new row
modify it
insert into table.
}

Not concise, especially with pulling the entire table into a dataset. It may
be more concise to do DELETE + INSERT for each row

Nathan
 
Back
Top