What does a timestamp in T-Sql mean in C#?

Question

I'm trying to develop a model object to hold a Sql Server row, and I understand perfectly how to do this except for the T-Sql/SqlServer timestamp. The table is defined as:

CREATE TABLE activity (
activity_id int
, ip_address varchar(39)
, user_id varchar(255)
, message_text
, dt timestamp
)

When I resolve a table row to my object, for an int or a string I would expect to do something like:

ActivityID = (int)dataReader["activity_id"];
IPAddress = (string)dataReader["ip_address"];

But what do I do about the timestamp column? There's no "timestamp" datatype that I can find anywhere. I know that Sql Server stores timestamp as an 8 byte binary, but what is this normally the equivalent of in .NET?

Edited to add: A little extra information... this is a row being returned from a DB2 table on our mainframe coming through a Sql Server view. "Rowversion" isn't an option, and DB2 is handing the column off as a timestamp. If timestamp and rowversion are identical, maybe I can treat it as one, but otherwise I'm stuck with timestamp.

Edited again to add: This project is going to drive me nuts. It will, at least, be a short trip. Anyway, yes @JoelC this is a Sql Server view into a DB2 database on the mainframe. I was finally able to track down one of our DBAs, who explained disdainfully that "of course" a DB2 TIMESTAMP comes across as to a Sql Server view as a datetime. From his tone of voice I guess only noobs don't know this. That's why he named it "datetime" in the actual view, Duh! (I gave it a different name in my example so as to not trigger commentary on naming conventions -- the actual data model diagram says it's a TIMESTAMP and names it timestamp). So, in this event, apparently one must cast it to a DateTime. I think I may begin considering becoming a DBA so that I, too, can drive programmers crazy. Sorry if I misled any of the responders to this question -- it was unintentional, as I was actually expecting a timestamp to be, well, a timestamp. Silly me. Thanks are especially due to Microsoft for naming a byte-array datatype a "timestamp" when it has nothing to do with dates and times. I haven't the vaguest idea which response to mark as the Answer. Sigh.

Solution 1

The name of the Sql Server timestamp data type is confusing. It's really better to think of it more like a version number — there's no date/time information in there anywhere. In fact, as of Sql Server 2008 the type was renamed to "rowversion".

Most of the time you want the datetime type in Sql Server instead, which maps easily to DateTime in C#, or perhaps datetime2 if you have a version so Sql Server that supports it. Documentation on the timestamp type is here:
http://msdn.microsoft.com/en-us/library/ms182776(v=sql.90).aspx

If you really must map that timestamp column as-is, the closest match is probably plain old long (or maybe ulong), but I have no idea how sql server handles byte ordering or big/little endien-ness vs C#.

Solution 2

According to this post

you'll have to cast it to a byte array.

byte[] dt = dataReader["dt"] as byte[];

Solution 3

It's a byte[], with a Length of 8.

Solution 4

when retrieving from database

string dt = Convert.ToBase64String(dataReader["dt"] as byte[]);

when passing into database

new SqlParameter("@dt", Convert.FromBase64String(dt))

View more solutions

Comments

[Adriano Carneiro]

@Michael that's not what OP asked. I would use DateTime, BTW

[Rahul]

What about DateTime object, it can hold your timestamp.

[George Stocker]

see also: stackoverflow.com/questions/425389/…

[Steve Townsend]

You should be using rowversion instead of timestamp - see msdn.microsoft.com/en-us/library/ms182776.aspx

[NewXcoder]

That's great advice if I have control over the database, which I don't. This is actually a DB2 table coming through a Sql Server view.

[Joel C]

@Cyber How are you bringing the DB2 database into SQL? Can you change the data type on the column in the view? I don't think a DB2 timestamp should map to a SQL Server timestamp, they don't mean the same thing.

[NewXcoder]

I'm going to mark this as the accepted answer, because it is the correct answer in response to what I thought was the question. The question having become different because my underlying assumption was wrong, the answers saying to cast to DateTime are correct in that instance.