SqlDataReader Convert SQL Signed to .NET Unsigned Integers

Question

Often use SQL Identity as the ID of .NET objects.
Start the ID at 0 or 1 as don't want the objects to have negative IDs.
The ID is presented to users so it needs to be logical to humans.

Also often use something like ((Int32)key1 << 32) + key2; for hash
If unsigned can so with faster (UInt32)key1 << 32 | key2;

Since SQL does not have unsigned data type half the range is lost.

What is a good practice for converting from SQL signed to unsigned in .NET?

Where the convert goes from 0 to max.
A straight cast does not not work as (UInt16)Int16.Min = Int16.Max +1.
Need to convert Int16.Min to 0 and Int16.Max to UInt16.Max.

In SQL will set the Identity seed to minimum (or minimum +1) for the data type.

Currently have a situation where going to blow through Int32 max but doubt it will ever get past UInt32 max. If it does get past UInt32 max the app and database will need a full review.

Tried extensions to SqlDataReader and it seems to work.
Will post as an answer.
Not yet gone into production so checking SO for something better or warnings about the approach.

Don't need DataTable compatibility as this app uses zero and never will.

We don't use Entity Framework nor SQL LINQ. Grunge TSQL and SP.

public static class MyExtensions
{
    public static UInt16 GetUInt16(this SqlDataReader rdr, int i)
    {
        //return (UInt16)rdr.GetInt16(i);  // wrong answer
        Int16 int16 = rdr.GetInt16(i);
        UInt16 uint16 = (UInt16)(int16 + 32768);
        return uint16;
    }
    public static UInt32 GetUInt32(this SqlDataReader rdr, int i)
    {
        Int32 int32 = rdr.GetInt32(i);
        UInt32 uint32 = (UInt32)(int32 + 2147483648);
        return uint32;
    }
}

Eric J. · Answer 1 · 2012-10-02T18:27:05.577

1

You can store your UInt32 as an Int32 in SQL Server. Cast your unsigned integer to a signed one when saving to SQL.

Your UInt32 values that are larger than Int32.MaxValue will be represented in SQL as negative numbers, but you can cast them back to UInt32 after reading from the database.

If using ADO.Net

uint hash;

When reading data

while (reader.Read())
{
    uint hash = (uint)reader.GetInt32(myIndex);
}

When writing data

cmd.Parameters.AddWithValue("MyParamName", (int)hash);

If using EF Code First

Expose a property of type Int32 that just wraps your hash value, and mark the actual hash property as NotMapped.

private uint hash;

[NotMapped]
public uint Hash 
{ 
    get { return hash; }
    set { hash = value; }
}

public int HashAsLong 
{ 
    get { return hash; }
    set { hash = value; }
}

edited Oct 02 '12 at 18:27

answered Oct 02 '12 at 18:21

Eric J.

147,927
63
340
553

Where and how to do the casting then is my question? And not saving this would be Identity only. But would query to the column. – paparazzo Oct 02 '12 at 18:23
Regarding the extensions... why are you working with a wider integer as your temporary variable? You only need to use an unsigned integer of the same width, e.g. `Int32 int32 = rdr.GetInt16(i);` could be `Uint16 uint16 = rdr.GetInt16(i);`. Adding 32768 will not cause a uint16 to overflow. It will get no larger than 0xffff. – Eric J. Oct 02 '12 at 18:40
That is not doing what I want with conversion of Identity. Need to go from 0 to UInt32.Max. -32768 need to convert to 0. (UInt32)-32768=32768. – paparazzo Oct 02 '12 at 18:41
If you need that mapping, you can certainly go with your code. I thought you just need a bijection (every uint value maps to exactly one int value and vice versa) so that you can store the uint values in SQL. – Eric J. Oct 02 '12 at 19:14

score 0 · Accepted Answer · answered Oct 06 '12 at 19:38

In the end what I did way make the object responsible for the translation.

A read only signed property of sqlID.
For updates back to the database decided that should not mess with that value.

The objects also expose an unsigned ID that is used by other objects, hash, and UI.

SqlDataReader Convert SQL Signed to .NET Unsigned Integers

2 Answers2