How can I update only the fields that have changed?

Question

I have a dataset in which I create a new row in the datatable and fill in 1 or 2 field. They may not be the same ones everytime.

When I do dataset.update(), all the fields appear to be written back. My database backend has default values set which are not triggered.

Is there any way to get the dataset to only insert the values I have manually altered thus allowing the database to "fill in the default values for the other fields"?

I also need to have the dataset generate the insert statement.

Answer 1

Try using a typed dataset or the adapter.FillSchema method.

Update 1 While this will send the extra values, it will effectively use the database defaults when doing the insert.

Answer 2

You aren't going to be able to have the designer generate the insert statement (or have the data adapter generate it at runtime) and selectively choose which fields to set.

While the DataSet does have change monitoring, it is on a row level, not a column level. Because of this, you will have to keep track of what columns are set when you make changes.

If the columns that are going to be changed are consistent, then I would craft the query in the designer manually to insert just those values.

Otherwise, you will have to monitor which columns change on your own. If the table in the database doesn't have columns that allow null values, you can allow the columns in the DataSet to have null values, and set those to default. Then, if the column does not have a null value, you know it has been changed.

If it does allow a null value, then you will have to store a flag external to the DataSet indicating whether or not the value was set for the column (you would do this when the column is changed).

NOTE: When I say that DataSets don't have change monitoring, I mean that the call to GetChanges on the DataSet/DataTable won't tell you which column changed, just what rows. The call to GetChanges is what the DataAdapter will use to determine what to send to the server, but it won't know which columns to send, so it sends them all.

Answer 3

you can set the default value manually by colling

Column.DefaultValue = defaultValue;

to generate the insert statement you can use the CommandBuilder class

new SqlCeCommandBuilder(dAdapter);