DataAdapter.Update return value and RejectChanges when using stored procedures

[Patrik Ellrén]

The DataAdapter.Update method returns an int to indicate how many rows were
successfully updated but if I set the UpdateCommand to a stored procedure
how do I hook it up to return the correct value and so that ADO.NET can
recognize when it fails and call RejectChanges on the rows that couldn't be
updated?

For example, if I have this simple procedure (sorry for any SQL errors)

PROCEDURE UpdateRow(
anId IN NUMBER,
aValue IN VARCHAR2,
prevLastModified IN TIMESTAMP) IS
BEGIN
UPDATE MyTable SET
value = aValue,
lastModified = SYSTIMESTAMP
WHERE
id = anId AND
lastModified = prevLastModfied;
END;

How does ADO.NET know when the timestamp check fails? Do I have to return
anything special or is this logic implemented in the database specific
connection classes? I connect to an Oracle 9i database if that matters.

Thanks.

 

[W.G. Ryan eMVP]

Patrick:

If I understand your problem correctly, you'll need to call update row by
row on your own or implement similar methodology. The .Update method does
this for you when you pass it a datatable, but if 10 rows are successful and
one fails, AcceptChanges will already have been called on each of the 10
that worked.

One way to do this is create another dataset using the GetChanges method of
the first dataset. Then, call update with this dataset and as the updates
succeed, call AcceptChanges on the respective road in the Source dataset.

Check out Bill Vaughn's article on Retrieving the Gozoutas at
www.betav.com -> articles -> MSDN - that should help you with the rest of
the problem.

HTH,

Bill

 

[Patrik Ellrén]

Thanks,

I'll look through Bill's articles. Just to be clear: I don't want to call
RejectChanges on every row in the DataSet if the update of a single row
fails, just on the row that couldn't be updated. I gather this is the normal
behaviour for ADO.NET. My question was really: how to signal update failure
to ADO.NET if I have set the DataReader.UpdateCommand to use a stored
procedure.Where does this logic exist? Is there some return code/exception
convention that I must use in the stored procedure if I want ADO.NET to
detect that an update has failed and call RejectChanges for the row the
operation failed for?

 

[Alfred Gary Myers Jr.]

Hi,

Set the DataAdapter's ContinueUpdateOnError to True
After DataAdapter.Update() all DataRows successfully submitted will have
been run through DataRow.AcceptChanges() and have RowState = Unchanged.
The DataRows that were not successfully submitted will contain errors and
can be retrieved by DataTable.GetErrors() wich returns an array of DataRows
that can be rejected.

HTH

Alfred Gary Myers Jr.
www.yuma.com.br
Blog in portuguese: http://br.thespoke.net/MyBlog/alfred_myers/MyBlog.aspx