Concurrency Checking - SQL Server Rowversion - Stored Procedures ASP.NET Command

[Bari Allen]

I'm trying to test for concurrency, using a SQL Stored Procedure on a
RowVersion (timestamp) Field. The vb code I'm using is as follows

Dim cmd As New SqlCommand("ConcurrencyCheck", cn)
Dim intID as integer = MyDataset.Tables("MyTable").Rows(0).Item("ID")
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@ReturnValue", SqlDbType.Int)
cmd.Parameters(0).Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add("@ID", intID)

Dim concurParam As New SqlParameter
concurParam.ParameterName = "@Concurrency"
concurParam.Value =
MyDataset.Tables("MyTable").Rows(0).Item("ConcurrencyValue")
concurParam.SqlDbType = SqlDbType.Binary
cmd.Parameters.Add(concurParam)

cn.Open()

If cmd.ExecuteScalar() > 0 Then
Record Found ...
Else
Record not found ...
End If

cmd.Dispose()
cn.Close()
cn.Dispose()

Prior to submitting the form, I run a different stored procedure which
populates the "MyDataset" from the same row using:
SELECT * FROM MyTable WHERE ID = @ID

The code in the stored procedure that submits the form is:
SELECT Count(*) FROM MyTable WHERE ID = @ID AND ConcurrencyValue =
@Concurrency

How do I convert the originally retrieved data (RowVersion) back to a true
binary in ASP.NET to send it back to SQL? It currently is storing the field
as an array, for some reason. Thus, when I test this procedure and now
(without a doubt) no one has modified the record since, it ALWAYS returns
"record not found"

Thanks in advance for any help on this.

Bari

 

[Bruce Barker]

a sql timestamp should come across as an 8 byte binary array. be sure to
specify size of 8. sql wants an 8 byte literal.

SELECT Count(*) FROM MyTable
WHERE ID = @ID
AND ConcurrencyValue = 0x000000000000906A

-- bruce (sqlwork.com)

 

[Greg Burns]

How do I convert the originally retrieved data (RowVersion) back to a true
binary in ASP.NET to send it back to SQL? It currently is storing the
field
as an array, for some reason. Thus, when I test this procedure and now
(without a doubt) no one has modified the record since, it ALWAYS returns
"record not found"

Here is some code I grabbed that is using RowVersion and converting back and
forth...

Dim rowVersion(8) As Byte
....
With cmd.Parameters.Add("@RowVersion", SqlDbType.Timestamp)
.Value = rowVersion
.Direction = ParameterDirection.InputOutput
End With
....
rowVersion = CType(cmd.Parameters("@RowVersion").Value, Byte())

Greg

 

[Daniel Walzenbach]

Bari,

what I do is that I convert the TimeStamp value in a BigInt value which I
can easily pass back and forth to my stored proc. An example would look like
the following:

CREATE PROCEDURE dbo.spTest
(
@OIDTest uniqueidentifier
, @SomeValue as int

, @VersionOld bigint = 0 output
)

AS

declare @Error int
declare @rowcount int

update tblTest set

SomeValue = @SomeValue

where OIDTest = @OIDTest and TimeStp = @VersionOld
select @error = @@error, @rowcount = @@rowcount

if @error > 0
begin
-- an error occurred
goto FunctionErrorWrite
end

if (@rowcount = 1)
begin
-- everything is fine

-- get a new timeStamp
select @VersionOld = convert(bigint, TimeStp)
from tblTest
where OIDTest = @OIDTest
-- Transaktion durchführen
goto FunctionExit
end
else
begin
goto FunctionErrorWrite
end


/* SET NOCOUNT ON */
FunctionExit:
RETURN 0

FunctionErrorUnknown:
RETURN -1

FunctionErrorWrite:
RETURN -2

FunctionErrorWrongID:
RETURN -3

FunctionErrorInterimChanged:
RETURN -4

Does this help you?

Regards

Daniel Walzenbach

 

[msnews.microsoft.com]

Thank you to everyone that replied.

When I tried to convert a value from a field in the dataset's table to byte,
I got an error, because the implicit conversion wasn't allowed from an array
(the default conversion sent back from the timestamp column).

Thus, I went with Daniel's suggestion to convert the value to a BigInt
within the retrieval stored procedure's select statement. That way, I can
store the entire recordset in a dataset, when it is returned. Then, I send
back the BigInt value to the stored procedure that tests for concurrency
(for updates). This worked great.

Thank you, Daniel!

 

[Daniel Walzenbach]

Good to hear You are welcome!

Cheers,

Daniel Walzenbach