Affected Records for UPDATE, INSERT, DELETE for Oracle stored procs

  • Thread starter Thread starter Mike Lastort
  • Start date Start date
M

Mike Lastort

I'm trying to figure out a way to determine the number of
records that were affected by calling Oracle stored procs
that UPDATE, INSERT or DELETE.

The problem is that I am working within an existing
framework that dynamically creates UPDATE, INSERT and
DELETE commands for Oracle data adapters based on all the
tables in the database. However, I can't call
OracleCommand.ExecuteNonQuery (which returns the number of
records affected) to execute the stored procs because of
the way the code is structured.

Is it possible to get the number of affected records for
UPDATEs, INSERTs and DELETEs without calling
ExecuteNonQuery? If so, what should I call? If not, we're
going to have a fair bit of rewriting to do.

Thanks in advance,

Mike Lastort
(e-mail address removed)
 
Thanks. I think that's leading us in the right direction.
We're going to try to use %ROWCOUNT in conjunction with an
update cursor which is created based on a "SELECT FOR
UPDATE."

Hopefully that will do what we want.

We're going to use the rowcount as the output parameter
from the stored procs, returning 1 if one record is
updated, deleted or inserted, and 0 for anything else, and
hope that the DataAdapter.Update() method will give us
what we want.
 
Thanks Folks. Mike and I are working together. The
problem is not really how to find out if a row is changed
in Oracle. More specifically, it is:

How to let the ADO.Net DataAdapter.Update() method know
that the wrong number of rows changed?

We are using the DataAdapter's Update() method to write
changes to the database. The documentation on
DataAdapter's InsertCommand (and UpdateCommand and
DeleteCommand) claims that one can use a stored procedure
call or a SQL statement to run during Update(). We would
like to do so. We have plugged in the stored proc and
it's working fine.

However, in the case where there is a concurrent change
(i.e., someone else modified the row since you read it),
we want to signal the DataAdapter that it should raise a
DbConcurrencyException. How can we?

In the case of embedded SQL, presumably the DataAdapter
uses the RowsAffected property to decide (expecting one
row to change). Is something similar possible with stored
procs?

Can someone with access to the ADO.Net code look this up
for us? Is it documented somewhere I haven't looked?

Thanks Muchly,
Bill
 
Back
Top