Throw DbConcurrencyException?

[localhost]

I am using SQL OleDb provider (not SqlManaged provider). I have an
ADO.NET class that uses DataAdapter.Update to pass changes to the
database. I have a "concurrency" column in the database and in my
DataTable. The MSSQL (2000) stored procedure only does the update of
the "concurrency" column matches, if not it returns a result set.

What is a good strategy for detecting, at the SP or DataAdapter level,
if the update failed so I can throw a DBConcurrencyException? What is
a good strategy for force-throwing a DBConcurrencyException from the
DB?

Thanks.

 

[Mary Chipman]

The best strategy would be not to throw any kind of exception at all
-- exceptions are expensive. Instead, code your stored procedure with
both input and output parameters (and don't forget SET NOCOUNT ON).
Check the value in the concurrency column against the value in the
input parameter before attempting the update. If they match, do the
update using the concurrency value in the WHERE clause, and then check
@@Rowcount and @@error. If the update succeeded, return the result set
(or output parameter). If it fails, return an output parameter value
indicating failure and the new concurrencyID column value. If you code
it this way in the stored procedure, you avoid throwing exceptions
both on the server and the client, which saves on round trips. The
idea is you send all the information to the server from the client,
and then code your stored procedure in such a way as to handle the
transaction totally on the server. net cost: one round trip, no
exceptions thrown.

--Mary