Create Update statement with a timestamp in the where clause

[2Fuzzy4U]

Hi there,

I've got an object/class that hold some attibutes which are inititaly filled with data using a select statement. (Odbc connection) This includes a database timestamp (stored as Byte[]). Each database row get's its own object/class instantiation. (I'm not planning to use any dataset(s) in my project.)

Now I want to construct a Update statement which uses the timestamp as check. My idea is to construct a string and use ExecuteNonQuery () because that fits best in my choosen solution.

But I'm stuck on how to get the timestamp in the where clause. Can someone enlighten me? Please? Thanks in advance

Coretta (NL)
Fresh to the game of C# and working with Visual Studio 2005 (Beta)

 

[obislavu]

I think you need to implement an optimistic concurency update. The timestamp must be supplied by the database and not by the client programs.
Here are teks from the doc.
One technique involves including a timestamp column in the table. Databases commonly provide timestamp functionality that can be used to identify the date and time when the record was last updated. Using this technique, a timestamp column is included in the table definition. Whenever the record is updated, the timestamp is updated to reflect the current date and time. In a test for optimistic concurrency violations, the timestamp column is returned with any query of the contents of the table. When an update is attempted, the timestamp value in the database is compared to the original timestamp value contained in the modified row. If they match, the update is performed and the timestamp column is updated with the current time to reflect the update. If they do not match, an optimistic concurrency violation has occurred.

Another technique for testing for an optimistic concurrency violation is to verify that all the original column values in a row still match those found in the database. For example, consider the following query:

SELECT Col1, Col2, Col3 FROM Table1

To test for an optimistic concurrency violation when updating a row in Table1, you would issue the following UPDATE statement:

UPDATE Table1 Set Col1 = @NewCol1Value,
Set Col2 = @NewCol2Value,
Set Col3 = @NewCol3Value
WHERE Col1 = @OldCol1Value AND
Col2 = @OldCol2Value AND
Col3 = @OldCol3Value

As long as the original values match the values in the database, the update is performed. If a value has been modified, the update will not modify the row because the WHERE clause will not find a match.

Note that it is recommended to always return a unique primary key value in your query. Otherwise, the preceding UPDATE statement may update more than one row, which might not be your intent.
obislavu

 

[stevexff]

'2000-09-27T00:00:00.000'

ought to work. But you might want to consider using something other than timestamp as an optimistic lock token. Perhaps an int column, or even better use checksum().

 

[2Fuzzy4U]

Thanks for the sugestions,
Yes, I want to implement optimistic locking, that's why we have the timestamp fields in our database. But I realy don't want to add all columns of a table in a WHERE clause and just leave the timestamp.

==========
I've also just found a solution (that works for me).. Create a function that converts the timstamps Byte[] representation to a string and use that in the where clause. I had been trying to get something like that working, but just missed something.

public string TimestampToHex(object obj)
{
string timestamp = "0x";
byte[] tsBytes = (byte[])obj;
foreach(byte tsByte in tsBytes)
{
timestamp += Convert.ToString(tsByte, 16).PadLeft(2, '0');
}
return timestamp;
}

_________________
Greetzzz
Coretta (NL)