Datasets and Adapter Updates

[optictygre]

Hi folks.

I have a couple questions about updating data from a dataset to a
database using a data adapter. If I declare an adapter, set the
batchupdatesize property to 0, and then call the
adapter.Update(dataset.GetChanges) function, will the entire batch
update fail if the database returns an error on one row of the data?

For example, if I have a dataset with 100 rows of data that needs
inserted into the database, but the database throws back a unique
constraint error on one of the rows, my program throws an error on the
adapter,update function. In that case, do any of the 100 rows get
inserted? Is there any way just to catch the error on a particular
row, yet continue inserting the rest of the rows?

Thanks for any help!
-Jason

 

[Cor Ligthert [MVP]]

Jason,

All records will be processed although the error is accepted, if you not do

http://msdn.microsoft.com/library/d...ataadapterclasscontinueupdateonerrortopic.asp

Be aware that the dataset GetChanges is not such a clever instrucktion as it
first looks.
It updates a copy of your dataset, while a dataadapter will normally already
only process datarows wich rowstate is changed.

I hope this gives an idea,

Cor

 

[optictygre]

Hi Cor,

First of all, the only reason I'm using the Dataset.GetChanges was
because I was going to be performing the updates in a batch, and the
code was taken from MSDN. If that's not the way to go, then I won't
use it. After inserting records into the database, the dataset will be
destroyed - as I won't need the information in it anymore.

I found out something interesting while playing around with the
options, though.

The DataAdapter has a ContinueUpdateOnError property which you can set
to 'True' which will continue processing the DataAdapter.Update
command, even if an error is encountered. This is ideal, so I can
catch errors at each row of data which fails to get inserted. However,
this ONLY works properly if the batch processing is turned OFF
(DataAdapter.UpdateBatchSize = 1).

If the DataAdapter.UpdateBatchSize is set to 0 or another number which
would turn batch processing on, and an error occurs within a record of
the batch, then the whole batch fails the update. Obviously, this is
not what I want to happen.

Ideally, I'd like to get a mix of the two scenarios. I'd like to be
able to use batch processing, thereby having less round-trips to the
database on insert of the rows, but at the same time I'd like to be
able catch each individual row error as it occurs (which for some
reason doesn't work when batch is turned on, and ContinueUpdateOnError
is set to 'True'). To me, it looks like it has to be one way or
another. I either Insert each row individually with a trip to the
database for each insert, with the ability to catch each row error, or
I try to send batches to the server, and if a row fails in the batch,
then the whole batch fails.

Any thoughts?

-Jason

 

[Ahmed Jewahar]

Hi,

Could you please help me with a sample code on this:

In windows form I have a Datagrid which is populated from DataAdaptor,
Datset etc..

How can I transfer all the records from into database table from
datagrid.

I have to transfer all data from datagrid. Also only the the records
which has been modified.

Kindly help..

Rgds.