Optimistic concurrency with custom select statements

Question

User1277740678 posted

I have an issue where my custom update and delete statements will only work if I don't use optimistic concurrency. 

I'll have a basic custom query like SELECT TOP 10 * from A. 

In order to auto generate the update and delete statements, I make a different datasource (SQL2) with SELECT* from  A, and 

copy and paste  those auto generate statements into my custom query. 

They work fine for SQL2 but not for the custom datasource. 

I've also tried it with the timestamp/ dbrow version, but still the same results. 

Answer 1

User475983607 posted

Kvetch

I have an issue where my custom update and delete statements will only work if I don't use optimistic concurrency. 

I'll have a basic custom query like SELECT TOP 10 * from A. 

In order to auto generate the update and delete statements, I make a different datasource (SQL2) with SELECT* from  A, and 

copy and paste  those auto generate statements into my custom query. 

They work fine for SQL2 but not for the custom datasource. 

I've also tried it with the timestamp/ dbrow version, but still the same results. 

I'm not sure you are implementing optimistic concurrency correctly.  See the following Optimistic concurrency on doc.smicrosoft. 

IMHO, you should implement this type of logic in a centralized location like a stored procedure, business, or data layer.  Otherwise, you need to repeat this logic on other pages.

I've also tried it with the timestamp/ dbrow version, but still the same results. 

You have no clearly explained the expected results and what "the same results" means. I have to assume the issue is related to committing dirty data. Is there anyway you can post the code?

Answer 2

User1277740678 posted

It won't need to be repeated so no BLL required. 

The results is that when using custom statements, when the user clicks update or delete, nothing happens. The row is still there, and no edits have been saved. 

Example code

Select * From [BULK] Where SKUID=@SKUID or @SKUID =-1

UPDATE [BULK] SET [BULK] = @BULK, [SKUID] = @SKUID WHERE [BULKID] = @original_BULKID AND [BULK] = @original_BULK AND [SKUID] = @original_SKUID

DELETE FROM [BULK] WHERE [BULKID] = @original_BULKID AND [BULK] = @original_BULK AND [SKUID] = @original_SKUID

Those update and delete statements work fine when I use the specify columns from a table option, to get 

Select * From [BULK] Where SKUID=@SKUID

The issue is still there if i modify the optimistic concurrency for null values. And there aren't any null values in this table anyway. 

Answer 3

User475983607 posted

Again, it seems your are not implementing optimistic concurrency correctly. Generally, a last update field (time stamp) is used to determine if the record is dirty.  On the initial SELECT the last update field is retrieved along with the other data.  Then on UPDATE the disconnected last update field is compared to the current value of the field in the table.  If the date times are equal the update is committed.  If the date times are not equal the update is not committed.

Please read the optimistic concurrency documentation in my previous link for details on how to implement optimistic concurrency.

https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/optimistic-concurrency

Answer 4

User753101303 posted

Hi,

And you double check your where criteria? Are you 100% sure it does match the targeted row? Are you sure original values are kept and passed correctly?

Not directled related but a know approach is to use a rowversion column (which is updated automatically whenever an update happens) which allows to use a single column to test for optimistic concurrency.