Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How compare/cast SQL rowversion field data in C#

Tags:

c#

sql

I have two records in my student table with a row-version field. I am creating the object of that record as student class in my C# code.

My question are:

  1. In which C# compatible datatype is rowversion data cast?
  2. How do I compare rowversion of two records and find which one is the latest in C#?
like image 755
Jones Avatar asked Oct 10 '11 13:10

Jones


2 Answers

One way to find the latest rowversion is to use the StructuralComparer

var r1 = new byte[] {0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x10, 0x00};
var r2 = new byte[] {0x00, 0x00, 0x00, 0x00, 0x00, 0x00, 0x09, 0xFF};

bool r1_is_newer = StructuralComparisons.StructuralComparer.Compare(r1, r2) > 0;

A benefit of the structural comparisons is that they automatically deal with NULLs

Regarding use of the BitConverter. If the above byte arrays were converted to UInt64 using the BitConverter , you'd end up with very high numbers and the comparison is meaningless (as the rowversion changes by one, the uint64 converted number changes by around 2^56)

If you'd like to sprinkle some LINQ over it to make it extra slow, you can use

bool r1_is_newer = 
  Enumerable.Zip(r1, r2, (b1, b2) => new { b1, b2 })
  .SkipWhile(item => item.b1 == item.b2)
  .Take(1).Any(item => item.b1 > item.b2);
like image 112
Chris Bednarski Avatar answered Sep 20 '22 21:09

Chris Bednarski


rowversion is binary(8), and will be returned as a byte[] if you use SqlCommand/SqlDataReader. If you use EF 4.1, the corresponding property will also be byte[].

You could use BitConverter to convert to UInt64 and then compare. You could also return the rowversion cast as bigint from SQL Server, which would appear as a long in C#. In either case, the larger value would have been created later.

In my own tests, I've seen that rowversion starts at a small, positive value (not 0). If you do cast to bigint, you'll have to consider what will happen when it reaches 2^64. In my case, it's not something I have to worry about. If you think that will happen in your database, then you'll have to add logic to handle that in your comparison, or just stick with BitConverter.ToUInt64.

Also, be careful if your rowversion column accepts NULL; You'll have to account for that if it does.

like image 33
Jeff Ogata Avatar answered Sep 20 '22 21:09

Jeff Ogata