.NET Questions (CLOSED)

Questions and Answers on any aspect of .NET. Now closed.

This discussion group is now closed.

Have a question about .NET development? Try stackoverflow.com, a worldwide community of great developers asking and answering questions 24 hours a day.

The archives of .NET Questions contain years of Q&A. Even older .NET Questions are still online, too.

C# and MS SQL Timestamp fields

Well it seems so basic but I haven't been able to Google it successfully, so I thought I'd Spolsky it instead!

It's a such a common requirement but unbelievably I can't find any code to do it.  I want to use a SQL Timestamp field to implement concurrency/DB updating/checking in C# - e.g. I have a "User" object - Firstname, Lastname and Timestamp field and want to load those into the object from the database and then of course want to check that the Timestamp hasn't changed when I attempt an update back to the DB.

As I understand it, the Timestamp field in SQL server is stored as a byte array, but it seems a lot of messing about doing that sort of comparison and was hoping to just retrieve it as a string and then compare it back similarly somehow.

Perhaps it's been a long day and my brain power (or search powers!) are a bit below par, but I thought there may be a simple solution for this.

Anyone got any pointers.  Cheers guys and gals!
Dale Harrison Send private email
Sunday, October 28, 2007
Set the were clause of your update sql to Id = @ID and LastUpdated = @TIMESTAMP.

If the record has been updated between you retrieving the record and saving it, no records will be affected.

The database does the concurrency check not you. Otherwise you would have to retrieve the timestamp, check it and then write the record but in between all that, the record could have changed.

If you're looking for a good book on this and other ado related issues, I heartily recommend David Sceppa's fine book on ado.net.
Sunday, October 28, 2007
Thanks, but I'm still not sure about the comparison part - i.e. When I get the record out of the database, I must have to get/save the timestamp value locally - at least in memory - so I can later add it to the SQL where clause.

So I realise it's the database doing the comparison but I'm not sure how to save the value as a C# variable/array or whatever it is, and then pass it back as a parameter to a stored procedure to do the comparison.

(Oh and I'll check out that book - I've heard of it before actually.  Thanks again)
Dale Harrison Send private email
Sunday, October 28, 2007
Here's an article that might help:

Sunday, October 28, 2007
Here's how retrieve the timestamp value from the standard IDataReader interface. If you are using the SqlDataReader interface, you may want to look into the GetSqlBinary function, it might do the hex conversion for you.

// Assume you have IDataReader rdr as the result of your select statement.

// Assume const int FLD_Ts = 1; - The index of the timestamp column in your select statement.

// Passing a null buffer causes rdr.GetBytes to return the number of bytes in that field...
int iLen = rdr.GetBytes(FLD_Ts, 0, null, 0, int.MaxValue);
// Create the buffer
byte[] buffer = new byte[iLen];
// Read the bytes into the buffer.
rdr.GetBytes(FLD_Ts, 0, buffer, 0, iLen);
// Save the buffer.
_MyByteArrayProperty = buffer;

Now, when you want to use this value in an SQL statement you just have to convert the byte array into a hex literal that can be inserted into any statement. A hex literal in SQL Server is prefixed with 0x, other than that you're just looping over each byte in the buffer and converting it to hex. Surprisingly, theres no obvious built-in method to do this for you in the .Net.

So, here is a method to do that for you. ( see http://pastebin.com/fc4accb6 ) You would call the 2nd derivative which allows you to specify a string prefix, like this:

string sHex = Hex.ToHexString("0x", _MyByteArrayProperty);

Now you can go ahead and say:

string sql = String.Concat("DELETE FROM Table WHERE [Id] = ", iId, " AND TimestampCol = ", Hex.ToHexString("0x", _MyByteArrayProperty));

The above statement will fail if the TimestampCol has changed since you pulled back the record...
Wayne Bloss Send private email
Monday, October 29, 2007
You should really be using parameterized sql for all of this instead of concatenating hex values into a string.
Monday, October 29, 2007
Right - If you used parameterized SQL, you treat the timestamp as an opaque datatype (byte array of size 8, in reality), and use your data object to store it, so that when you have to do an update you'll already have it.

So no need to convert to hex, and no need to concatenate up a string that contains your statement (which is a security risk, as well).
xampl Send private email
Monday, October 29, 2007
I didn't use a parameterized query in my example there because I thought it might confuse him. Of course I use them everywhere, it's the only way to get SQL Server to cache the execution plan...

However, how do you create a parameterized query without having to convert your byte[] into hex? At some point you need to end up with a C# string variable that contains the SQL that you give to your DataReader don't you?

For instance: string sql = "exec sp_executesql N'My Query WHERE Ts = @P1', N'@P1 timestamp', @P1 = {0}"

What tool are you using that generates this string?
Wayne Bloss Send private email
Monday, October 29, 2007
I think you guys are talking about using SqlCommand objects to run your sql.

Internally the SqlCommand object has to do what I've demonstrated...at some point it needs to generate a string with the SQL in it...or am I completely wrong?
Wayne Bloss Send private email
Monday, October 29, 2007
You are completely wrong.
Monday, October 29, 2007
Google for SQL injection.
Monday, October 29, 2007
Yes, I know what sql injection is. What do you do when you want to send 50 commands in a batch though? You can't create 50 SqlCommand objects and execute them in one trip to the database can you?

Can you elaborate on your proposed solution, maybe with a few lines of code?
Wayne Bloss Send private email
Monday, October 29, 2007
After a cursory look at the code of SqlCommand, it seems to use RPC to talk to SQL Server. That's all fine and good if you want to use the Dataset, DataTable and DataAdapter to run batches for you.

However if you only want to use the SqlDataReader and you want the separate statements within your batch to be cached, you have to write your own calls to sp_executesql. Theres no other way around it.
Wayne Bloss Send private email
Monday, October 29, 2007
Using the MS Enterprise Library (v3.1?? Whatever the latest one is..)

To write a timestamp:

myDb.AddInParameter(myDbCommand, "@MyTimestampColumn", DbType.Binary, 8);

To read a timestamp:

myTimestampArrayVariable[] = reader["@MyTimestampColumn"] as byte[];

Note that you can't do a sizeof() on a value type like DateTime, so you have to either hardcode the size (8 bytes) or create a constant for it.
xampl Send private email
Wednesday, October 31, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz