StrataFrame Forum » StrataFrame Application Framework - V1 » Business Objects and Data Access (How do I?) » Views & BO Updates with Concurrency

Views & BO Updates with Concurrency

Crones — Mon, 19 Nov 2007 13:15:55 GMT

I have a BO mapped to a view that joins fields from 2 tables in a 1-1 relationship. I've created stored procs to handle the inserts and updates and these work ok except for when it comes to concurrency. My BO always returns "success" when saving when there is a concurrency issue on the main table and I'm not sure how to catch this.

Here's how my procedure logic is laid out.

[codesnippet]UPDATE Table1 SET RowVersion = (RowVersion + 1) ... WHERE PK=@Table1PK AND ((RowVersion = @RowVersion) OR (@RowVersion IS NULL))
SELECT @RowVersion = RowVersion FROM Table1 WHERE PK=@Table1PK;
UPDATE Table2 SET ... WHERE Table1FK=@PK[/codesnippet]

When there are no concurrency issues, everything works great.
However, if there is a problem during the table updates I do not receive an error.

I think this is more of a SQL Stored Procedure issue than anything having to deal with the framework but either way I'm not sure how to handle this. Any ideas?

TIA

RE: Views & BO Updates with Concurrency

StrataFrame Team — Mon, 19 Nov 2007 13:15:55 GMT

Good to hear. Let me know if you get stuck on anything else with those sprocs.

RE: Views & BO Updates with Concurrency

Crones — Mon, 19 Nov 2007 11:17:31 GMT

Thanks a bunch for the help. That got me started in the right direction.

RE: Views & BO Updates with Concurrency

StrataFrame Team — Mon, 19 Nov 2007 10:01:42 GMT

Ah, the issue is that the BO checks on the number of records modified (the ROWCOUNT in the sproc), so since you're always updating table 2 (not checking on the concurrency in an IF) then the ROWCOUNT will always be greater than 0.

Your best bet is to start a transaction and do something like this:

-- Start a transaction so you can modify both tables without issues
BEGIN TRANSACTION
-- Declare a temp variable to grab the row version from table1
DECLARE @TEMPVERSION
-- Retrieve the row version
SELECT @TEMPVERSION = RowVersion FROM Table1
-- Check the row version to make sure that there isn't a concurrency issue
IF @TEMPVERSION = @RowVersion THEN
-- There isn't a concurrency issue, so update the tables
BEGIN
-- Update Table1
-- Update Table2
-- Retrieve the @RowVersion to return it
-- Committ the transaction because everything worked as expected
COMMITT
END
ELSE
BEGIN
-- Rollback the transaction because there was a concurrency error
-- The ROWCOUNT will automatically return 0 because no rows were
-- updated and it will be detected by the business object as a
-- concurrency error
ROLLBACK
END

I'm not positive on all of the syntax, but it might get you started in the right direction.