Mary
Mon Dec 06 13:23:16 CST 2004
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
On Mon, 06 Dec 2004 13:24:33 -0500, localhost <primpilus@cohort.ces>
wrote:
>
>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.
>
>