locked
Optimistic concurrency with custom select statements RRS feed

  • 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. 

    Tuesday, July 11, 2017 12:37 PM

All replies

  • 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?

    Tuesday, July 11, 2017 12:55 PM
  • 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. 

    Tuesday, July 11, 2017 1:07 PM
  • 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

    Tuesday, July 11, 2017 2:16 PM
  • 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.

    Wednesday, July 12, 2017 11:06 AM