Optimistic Concurrency and TimeStamp

[Guest]

Hi,
I would like to implement optimistic concurrency where I retrieve the
timestamp of a record in a table with a stored procedure and save the
timestamp value into a vb.net variable. This represents the original
timestamp for the record. When a user updates the record, I would like to
pass the original timestamp to a stored procedure that performs the update to
ensure the record has not been changed by another user. The problem is, how
do I store the original timestamp value from SQL Server 2000 in vb.net? I
have tried:

Dim OrigTstamp() as Byte

I can assign an incoming timestamp to a byte array but I'm not certain if
anything is there. I then have great difficulty passing the OrigTstamp() to
the update stored procedure.

 

[Miha Markic [MVP C#]]

Hi Scott,

Hi,
I would like to implement optimistic concurrency where I retrieve the
timestamp of a record in a table with a stored procedure and save the
timestamp value into a vb.net variable. This represents the original
timestamp for the record. When a user updates the record, I would like to
pass the original timestamp to a stored procedure that performs the update
to
ensure the record has not been changed by another user. The problem is,
how
do I store the original timestamp value from SQL Server 2000 in vb.net? I
have tried:

Dim OrigTstamp() as Byte

I can assign an incoming timestamp to a byte array but I'm not certain if
anything is there.

you could iterate through array, ie:
for each b as byte in origtstamp
Console.WriteLine(b)
next

or just stop into the debugger and check what's inside OrigTStamp.

I then have great difficulty passing the OrigTstamp() to

the update stored procedure.

Why?

 

[woutervu]

Hi Scott,

You can store it as a GUID if you wish (System.Guid). Use the timestamp
type in SQL Server, this translates to a byte[8] array in dotNET.
Either keep the 8 bytes and use that, or use the following code to
translate them into a GUID. Only problem is that a GUID is made up of
sixteen bytes. Hence the code copies the timestamp bytes into both the
hi-order and low-order. You can then use the second piece of code to
load a GUID into a SqlCommand object.

FIRST PIECE OF CODE - TRANSLATE BYTE[] TO GUID AND BACK

public static Guid CreateGuidFromTimestamp(byte[] timestamp)
{
byte[] guidBytes = new byte[16];
Array.Copy(timestamp, guidBytes, 8);
Array.Copy(timestamp, 0, guidBytes, 8, 8);
return new Guid(guidBytes);
}

public static byte[] CreateTimestampFromGuid(Guid guid)
{
byte[] bytes = new byte[8];
Array.Copy(guid.ToByteArray(), bytes, 8);
return bytes;
}

SECOND PIECE OF CODE - CREATE A PROCEDURE WHICH TAKES AN INPUT
TIMESTAMP

static void SomeProcedure(Guid oldTimestamp, out Guid newTimestamp)
{
SqlCommand command = new SqlCommand();
command.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@p_oldTimestamp",
System.Data.SqlDbType.VarBinary, 8));
command.Parameters["@p_oldTimestamp"].Value =
CreateTimestampFromGuid(oldTimestamp);
csp_Portals_Update.Parameters.Add(new
System.Data.SqlClient.SqlParameter("@p_newTimestamp",
System.Data.SqlDbType.VarBinary, 8,
System.Data.ParameterDirection.Output, false,
((System.Byte)(0)), ((System.Byte)(0)), "",
System.Data.DataRowVersion.Current, null));
command.ExecuteNonQuery();
newTimestamp =
CreateGuidFromTimestamp((byte[])command.Parameters["@p_newTimestamp"].Value);
}

 

[Sahil Malik [MVP]]

In addition to Miha's answer -

I can assign an incoming timestamp to a byte array but I'm not certain if
anything is there. I then have great difficulty passing the OrigTstamp()
to
the update stored procedure.

All you really need is a simple SqlParameter with the appropriate data
type - no biggie.

 

[Guest]

Scott,

I don't use stored procedures to do that, but I do use a timestamp.

When I load data from a row into my business object, I store the timestamp
like this:

Private UpdateStamp As Byte()

When I save the business object's data to the database I use a parameterized
update statement, like this:

cmdStudents.CommandText = "Update Students Set StudentName = ?, Test1 = ?,
Test2 = ? Where ID = ? And UpdateStamp = ?"

I am using OleDb, so my @UpdateStamp parameter looks like this:

cmdStudents.Parameters.Add("@UpdateStamp", OleDb.OleDbType.Binary).Value =
Me.UpdateStamp

Some variation of this should work for you.

Kerry Moorman