Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

converting sql server rowversion to long or ulong?

What is the proper type for the rowversion (timestamp) data type?

I know it is 8 bytes but i cannot find a link in MSDN which tell if it is a signed or unsigned long.

which code should I use, does it even matter?

byte[] SqlTimeStamp;

long longConversion;
longConversion = BitConverter.ToInt64(SqlTimeStamp,0);
TimeStamp = BitConverter.GetBytes(longConversion);

ulong ulongConversion;
ulongConversion = BitConverter.ToUInt64(SqlTimeStamp,0);
TimeStamp = BitConverter.GetBytes(ulongConversion);
like image 901
Fredou Avatar asked Jan 15 '14 19:01

Fredou


2 Answers

It does matter. You want your comparison to have the same result as SQL Server's comparison. SQL Server uses unsigned comparisons on binary types:

select case when 0x0FFFFFFFFFFFFFFF < 0xFFFFFFFFFFFFFFFF then 'unsigned' else 'signed' end

If you do the same thing with long which is signed, 0xFFFFFFFFFFFFFFFF represents -1. That means your comparison will be incorrect; it won't match with the same comparison done in SQL Server.

What you definitely want is to use ulong where 0xFFFFFFFFFFFFFFFF is ulong.MaxValue.

Endianness is also important

Additionally, as Mark pointed out, BitConverter.GetUInt64 is not converting properly. Mark is not completely right- BitConverter is either big-endian or little-endian depending on the system it's running on. You can see this for yourself. Also, even if BitConverter was always little-endian, Array.Reverse is less performant with a heap allocation and byte-by-byte copying. BitConverter is just not semantically or practically the right tool for the job.

This is what you want:

static ulong BigEndianToUInt64(byte[] bigEndianBinary)
{
    return ((ulong)bigEndianBinary[0] << 56) |
           ((ulong)bigEndianBinary[1] << 48) |
           ((ulong)bigEndianBinary[2] << 40) |
           ((ulong)bigEndianBinary[3] << 32) |
           ((ulong)bigEndianBinary[4] << 24) |
           ((ulong)bigEndianBinary[5] << 16) |
           ((ulong)bigEndianBinary[6] <<  8) |
                   bigEndianBinary[7];
}

The cleanest solution

Update: If you use .NET Core 2.1 or later (or .NET Standard 2.1), you can use BinaryPrimitives.ReadUInt64BigEndian which is a perfect fit.

On .NET Framework, here is the solution I use: Timestamp.cs. Basically once you cast to Timestamp, you can't go wrong.

like image 193
jnm2 Avatar answered Nov 13 '22 15:11

jnm2


Neither will work correctly for purposes of comparing timestamp/rowversion values, if you're running on an x86 family CPU, because of endian. The first byte of a timestamp is most significant, but not so for little endian integer types.

Call Array.Reverse(ts) before calling BitConverter.ToUInt64(ts), and for the other direction, after calling BitConverter.GetBytes(tsUInt64)

like image 9
Mark McGinty Avatar answered Nov 13 '22 13:11

Mark McGinty