Using SQL Server Timestamp

R

Roger Twomey

I have a database that I don't want to lock. I decided that before any
updates can occur I would check a timestamp value and ensure that nobody
else updated before I did (avoiding the 'last update wins' scenario).

I have a problem, I can read the Timestamp from the db when I read the
record. I currently use the data to pre-fill a form (gee go figure ;) ) and
the user changes some values and updates.

I don't know what to DO with the timestamp value while I am holding it. I
have tried putting it into a hidden value on the form but the value does not
seem to translate back and forth.

Do I have to store it as in memory as part of the session or can I somehow
convert to and from text??

I have about 0 experience working with byte arrays so the best answer is one
with an example.

Thanks.
 
G

Greg Burns

Hope this was what you were expecting! Here is some code from my project
that using a "timestamp" (aka rowversion)

"A future release of Microsoft® SQL ServerT may modify the behavior of the
Transact-SQL timestamp data type to align it with the behavior defined in
the standard. At that time, the current timestamp data type will be replaced
with a rowversion data type.

Microsoft® SQL ServerT 2000 introduces a rowversion synonym for the
timestamp data type. Use rowversion instead of timestamp wherever possible
in DDL statements. rowversion is subject to the behaviors of data type
synonyms."

(As far as what to do with it, that depends. I shove mine into ViewState
when the page load, and then read it again after postback)


Example of typical sproc:


CREATE PROCEDURE usp_SomeSPROC(
@MyID int,
@RowVersion rowversion OUTPUT,
<blah blah>
)
AS
SET NOCOUNT ON

BEGIN TRAN

DECLARE @ErrorSave int

DECLARE @CurrRowVersion rowversion

SELECT @CurrRowVersion = [RowVersion] FROM MyTable WHERE MyID = @MyID

IF @CurrRowVersion <> @RowVersion
BEGIN
ROLLBACK
RETURN -1
END

<do some tsql statements here>

IF @@ERROR <> 0
SET @ErrorSave = @@ERROR

SELECT @RowVersion = [RowVersion] FROM MyTable WHERE MyID = @MyID

IF @ErrorSave <> 0
ROLLBACK
ELSE
COMMIT


Back in my code, typical SQL method:


This is all psuedo code...

Public RowVersion(8) As Byte

Public Sub MyMethod(<blah blah>)


Dim cn As SqlConnection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
Dim cmd As SqlCommand = New SqlCommand("usp_SomeSPROC", cn)
cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@MyID", Me.MyID)
cmd.Parameters.Add("RETURN_VALUE", SqlDbType.Int).Direction =
ParameterDirection.ReturnValue

With cmd.Parameters.Add("@RowVersion", SqlDbType.Timestamp)
.Value = Me.RowVersion
.Direction = ParameterDirection.InputOutput
End With

Try
cn.Open()
cmd.ExecuteNonQuery()

If CInt(cmd.Parameters("RETURN_VALUE").Value) = -1 Then
' this row has been modified by somebody else!!!
Throw New ModifiedByAnotherException
End If

Me.RowVersion = CType(cmd.Parameters("@RowVersion").Value,
Byte())

Catch ex As SqlException
Throw New Exception("error while doing mymethod!", ex)
Finally
cn.Close()
End Try

End Sub

Public Class ModifiedByAnotherException

Inherits System.ApplicationException

Public Sub New()
MyBase.New("Timesheet was modified by another user!")
End Sub

Public Sub New(ByVal InnerException As Exception)
MyBase.New("Timesheet was modified by another user!",
InnerException)
End Sub

End Class

HTH,
Greg
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

Forum statistics

Threads
473,733
Messages
2,569,440
Members
44,831
Latest member
HealthSmartketoReviews

Latest Threads

Top