concurrency down to the column level - sql

Using VS05 pro SP1...

(I AM REPOSTING THIS IN SQL FROM VB LANGUAGE BECAUSE I RECEIVED NO RESPONSES...ANY HELP WOULD BE APPRECIATED)

This is for a telephony application using Last In Wins concurrency...

Basically, we want to update only the columns that have changed in a row, not the entire row with the few changed columns. We are using TableAdapters exclusively.

We need concurrency down to the column level...that is to say, if a concurrency exception is thrown, we don't just want the new row to be UPDATED and overwrite the old row in the datasource...we only want the new columns to be UPDATED to the datasource...consider the following sequence...

- Fill table A and table B identically

- change column1 value in table A

- Update table A

- change column2 value in table B

- Update table B - this will generate a concurrency error.

Most of the help centers around using the merge command, to update table B, but in the situation above, we would lose the change to column1 (which we don't want to loose). Merge preserves the changes in table B (column2), but it would have the old value for column1, thus we would loose the value in column 1 when we updated the new merged table.

So, we need a way to build a datatable made up of the values in the datasouce plus the changes in the table B, which we will then update.

I have played endlessly with for/next type of constructs to build the new table of new column values using row version data, but I cannot get it to happen.

Upon exception for UPDATING table B, how do we build a temporary table from the datasource (so we get the change to column 1) and the new changes in table B, so we can update the temporary table and not loose any data.

Thanks!

Bob

hi Bob,

BobInIndy wrote:

(I AM REPOSTING THIS IN SQL FROM VB LANGUAGE BECAUSE I RECEIVED NO RESPONSES...ANY HELP WOULD BE APPRECIATED)

This is for a telephony application using Last In Wins concurrency...

Basically, we want to update only the columns that have changed in a row, not the entire row with the few changed columns. We are using TableAdapters exclusively.

We need concurrency down to the column level...that is to say, if a concurrency exception is thrown, we don't just want the new row to be UPDATED and overwrite the old row in the datasource...we only want the new columns to be UPDATED to the datasource...consider the following sequence...

- Fill table A and table B identically

- change column1 value in table A

- Update table A

- change column2 value in table B

- Update table B - this will generate a concurrency error.

Most of the help centers around using the merge command, to update table B, but in the situation above, we would lose the change to column1 (which we don't want to loose). Merge preserves the changes in table B (column2), but it would have the old value for column1, thus we would loose the value in column 1 when we updated the new merged table.

So, we need a way to build a datatable made up of the values in the datasouce plus the changes in the table B, which we will then update.

I have played endlessly with for/next type of constructs to build the new table of new column values using row version data, but I cannot get it to happen.

Upon exception for UPDATING table B, how do we build a temporary table from the datasource (so we get the change to column 1) and the new changes in table B, so we can update the temporary table and not loose any data.

Thanks!

Bob

from a SQL Server point of view, you have to "build" a DDL script (aka stored procedure) for each single (and combination of) attribute of your entity, so that you can "execute" UPDATE obj SET singleCol = singleVal WHERE pk = xx, but this obviously will lead to a very high proliferation of stored procedures for each single base table.. another solution would be dynamic SQL, where the desired UPDATE statement can be "constructed" to reflect only the modified attributes, but I do personally not like accepting dynamic SQL against "my" tables...

or, depending on your NULL behaviour, you even could create a single proc where the NULL does not qualify for updates for the single attribute, that's to say something like

SET NOCOUNT ON; USE tempdb; GO CREATE TABLE dbo.TestTB ( Id int NOT NULL PRIMARY KEY, Data0 varchar(10) NOT NULL, Data1 int NOT NULL DEFAULT 10, Data2 datetime NOT NULL DEFAULT '20070101', rv timestamp NOT NULL ); INSERT INTO [dbo].[TestTB] ([Id], [Data0], [Data1], [Data2]) VALUES ( 1, 'Test', DEFAULT, DEFAULT ); GO CREATE PROCEDURE dbo.usp_Test ( @.Id int, @.Data0 varchar(10) = NULL, @.Data1 int = NULL, @.Data2 datetime = NULL, @.rv timestamp OUTPUT ) AS BEGIN DECLARE @.r int; BEGIN TRY UPDATE dbo.TestTB SET [Data0] = ISNULL(@.Data0, [Data0]), [Data1] = ISNULL(@.Data1, [Data1]), [Data2] = ISNULL(@.Data2, [Data2]) WHERE [Id] = @.Id AND [rv] = @.rv; SET @.r = @.@.ROWCOUNT; IF @.r = 0 BEGIN SELECT @.rv = [rv] FROM dbo.TestTB WHERE [Id] = @.Id; RAISERROR ('Concurrency violation', 16, 10); END; RETURN 0; END TRY BEGIN CATCH DECLARE @.ErrorMSG varchar(2000); SET @.ErrorMSG = ERROR_MESSAGE(); RAISERROR (@.ErrorMSG, 16, 1); RETURN -100; END CATCH; END; GO DECLARE @.Id int, @.Data0 varchar(10), @.Data1 int, @.Data2 datetime, @.rv timestamp; SELECT @.Id = 1, @.Data0 = '1st Mod', @.Data1 = NULL, @.Data2 = NULL, @.rv = [rv] FROM dbo.TestTB WHERE [Id] = 1; PRINT 'parameters to use'; SELECT @.Id, @.Data0, @.Data1, @.Data2, @.rv; EXEC dbo.usp_Test @.Id = 1, @.Data0 = @.Data0, @.Data1 = @.Data1, @.Data2 = @.Data2, @.rv = @.rv OUTPUT; PRINT 'Modified data'; SELECT * FROM dbo.TestTB; PRINT 'key parameters'; SELECT @.Id, @.rv; PRINT '--'; PRINT 'now let''s try a concurrency exception;'; PRINT 'manual modification to simulate a conflict'; UPDATE dbo.TestTB SET [Data1] = 1000 WHERE [Id] = 1; PRINT 'which results in a timestamp change'; SELECT * FROM dbo.TestTB; SELECT @.Data0 = '2st Mod', @.Data1 = NULL, @.Data2 = NULL; EXEC dbo.usp_Test @.Id = 1, @.Data0 = @.Data0, @.Data1 = @.Data1, @.Data2 = @.Data2, @.rv = @.rv OUTPUT; SELECT * FROM dbo.TestTB; PRINT 'fails, as Data1 now is 1000'; PRINT 'but you have the new timestamp value, so just resubmitit'; EXEC dbo.usp_Test @.Id = 1, @.Data0 = @.Data0, @.Data1 = @.Data1, @.Data2 = @.Data2, @.rv = @.rv OUTPUT; PRINT 'only your modified attribute is persisted'; SELECT * FROM dbo.TestTB; GO DROP PROCEDURE dbo.usp_Test; DROP TABLE dbo.TestTB; --<- parameters to use -- - -- -- 1 1st Mod NULL NULL 0x00000000000007EF Modified data Id Data0 Data1 Data2 rv -- - -- -- 1 1st Mod 10 2007-01-01 00:00:00.000 0x00000000000007F0 key parameters -- 1 0x00000000000007EF -- now let's try a concurrency exception; manual modification to simulate a conflict which results in a timestamp change Id Data0 Data1 Data2 rv -- - -- -- 1 1st Mod 1000 2007-01-01 00:00:00.000 0x00000000000007F1 Msg 50000, Level 16, State 1, Procedure usp_Test, Line 35 Concurrency violation Id Data0 Data1 Data2 rv -- - -- -- 1 1st Mod 1000 2007-01-01 00:00:00.000 0x00000000000007F1 fails, as Data1 now is 1000 but you have the new timestamp value, so just resubmitit only your modified attribute is persisted Id Data0 Data1 Data2 rv -- - -- -- 1 2st Mod 1000 2007-01-01 00:00:00.000 0x00000000000007F2

the logic is: if NULL is passed, the column will persists it's original value, else the new provided value is stored..

in case of concurrency violation (a trivial check is here performed), an exception is risen, and the new timestamp value is returned as output parameter.. you can this way do something (if you like) as informing you will overwrite someone's work (only if your modified column will collide with his/her column) and, having the current timestamp, you can re-execute the UDPATE statement..

this way a single procedure can accomplish the desired behaviour of modifying the "changed" columns, as in case of NULL the underlying attribute's value is conserved, but this obviously require your "logic" to use a NULL as a "logical" treshhold for "unchanged values"...

you should be able to access your original and current values in ado.net to populate the command parameters as "required"...

regards