How to read timestamp type's data from sql server using C#?

Question

I get the result in .NET like this:

var lastRowVersion = SqlHelper.ExecuteScalar(connStr, CommandType.Text, "select
top 1 rowversion from dbo.sdb_x_orginfo order by rowversion desc");

The result is a byte array [0]= 0,[1]=0,[2]=0,[3]=0,[4]=0,[5]=0,[6]=30,[7]=138, but the result in SQL Server is 0x0000000000001E8A.

How can I get the value "0x0000000000001E8A" in .NET?

score 24 · Answer 1 · edited Nov 14 '16 at 16:28

I found that the byte[] returned from sql server had the wrong Endian-ness and hence the conversion to long (Int64) did not work correctly. I solved the issue by calling Reverse on the array before passing it into BitConverter:

byte[] byteArray = {0, 0, 0, 0, 0, 0, 0, 8};

var value = BitConverter.ToUInt64(byteArray.Reverse().ToArray(), 0);

Also, I thought it better to convert to UInt64.

score 5 · Accepted Answer · edited Jan 29 '14 at 15:19

5

If you just want to convert byte[] to a System.Int64 (aka long) then use BitConverter.ToInt64:

SqlBinary binary = /* ... */;
long value = BitConverter.ToInt64(binary.Value, 0); // 0 is the start index in the byte array

To display it as a hex string, you can use the X format specifier, e.g.:

Console.WriteLine("Value is 0x{0:X}.", value);

edited Jan 29 '14 at 15:19

mcw

3,170
28
33

answered Nov 08 '11 at 06:35

bobbymcr

22,445
3
50
65

4

See the other answer - SqlBinary.Value has the opposite endianness, so you most likely want to reverse the bytes. – mcw Jan 29 '14 at 15:36

score 2 · Answer 3 · answered Jul 12 '17 at 16:31

Here's an one-liner:

var byteArray = new byte[] { 0, 0, 0, 0, 0, 0, 30, 138 };

var rowVersion = "0x" + string.Concat(Array.ConvertAll(byteArray, x => x.ToString("X2")));

The result is:

"0x0000000000001E8A"

Just be aware that there are other options that perform better.

score 0 · Answer 4 · answered Apr 20 '14 at 18:37

0

You can use following query which will return bigint instead of returning hex.

select top 1 cast(rowversion as bigint) rowversion from dbo.sdb_x_orginfo order by rowversion desc

answered Apr 20 '14 at 18:37

Ashish Singh

1,080
1
9
8

3

Be aware that bigint is a 64bit *signed* integer, whereas rowversion is a 64bit *unsigned* integer. Given a large enough rowversion value, casting to bigint will give unexpected results. – Snixtor Jan 29 '15 at 03:59

score 0 · Answer 5 · answered Mar 03 '15 at 13:14

Same as "Ashish Singh" I returned to c# after converting to bigint on DB and used Int64 on the code side. Converting to long/Int64/UInt64 in c#/VB gave wrong results.(even after reversing the array because of the little endian issue.)

Pay attention on the DB side that RowVersion timestamp has MIN_ACTIVE_ROWVERSION() which indicates the last commited timestamp

score 0 · Answer 6 · answered Feb 29 '16 at 10:37

0

Works fine for me

  var versionString = new StringBuilder();
  versionString.Append("0x");
  for (var i = 0; i < binary.Count(); i++)
  {
      versionString.Append(string.Format("{0:X}", binary[i]));
  }
  versionString.ToString(); // result

answered Feb 29 '16 at 10:37

NaN

1,697
9
13

How to read timestamp type's data from sql server using C#?

6 Answers6