15

I want to store a C#.NET ulong into a T-SQL database. I don't see any provisions for doing this, as the SQL bigint has the same Min/Max values as a normal long.

Is there any way I can do this? Or is catching an OverflowException my only hope?

Robert Harvey
  • 168,684
  • 43
  • 314
  • 475
Onion-Knight
  • 3,257
  • 7
  • 29
  • 34
  • See [this answer](https://dba.stackexchange.com/a/34036) which tells you to use `DECIMAL(20,0) CHECK (columnName >= 0 AND columnName <= 18446744073709551615)`. – stomy Apr 05 '19 at 16:46

3 Answers3

9

This should answer your question:

http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataproviders/thread/ff08c190-a981-4896-9542-3f64b95a84a2/

You would use BigInt, you just have to be careful in how you convert the signed type back into an unsigned type in C#

// This has not been tested
unchecked
{
    myUlong = myDataReader.GetInt64(...);
}

...

The other possibility is to use VarBinary with a length of 8, then convert the bytes to a ulong in C#

Neil N
  • 23,912
  • 15
  • 82
  • 141
  • @Robert Harvey: I posted a short example – Neil N Jun 02 '10 at 14:47
  • Do you mean treating `long.MinValue` as `0` and `long.MaxValue` as `ulong.MaxValue`? – Onion-Knight Jun 02 '10 at 14:48
  • 1
    Is the behavior for the signed bit defined properly here? If you don't want to lose precision, you have to use the sign bit as the additional high-order bit in the ulong. Will this do that? – Robert Harvey Jun 02 '10 at 14:48
  • @Robert Harvey: thats why it's unchecked. High order bit doesnt matter to ulong, it just wants the 8 full bytes. – Neil N Jun 02 '10 at 14:51
  • `bigint` cannot store `ulong` as that is basically `long`. `ulong` has higher range. `varbinary` is the right answer. – Mrchief Feb 03 '13 at 03:40
  • @Mrchief, it doesnt have anything to do with the range, it's the number of bits. Notice the code above converts from unsigned to signed. – Neil N Feb 03 '13 at 04:27
  • Well, try inserting 9223372036854775808 (long.Max + 1) in a bigint. You'll be greeted with a "Arithmetic overflow error". That's what I mean by range. That number is valid `ulong` but can't be stored in `bigint`. Makes sense? – Mrchief Feb 03 '13 at 16:36
  • @Mrchief, which is why you convert it to signed first. 9223372036854775808 converted to signed is -9223372036854775808, which inserts into a bigint field just fine. You just have to remember to convert it again on the way back out of the database. Ulong.MaxValue (18446744073709551615), converted to signed, is -1, which also inserts into BigInt just fine ;) – Neil N Feb 04 '13 at 03:30
  • I used `UInt64` as a variable's type and the type of the corresponding column in **DB** is `BIGINT`. It is showing the following **error:** `The parameter data type of UInt64 is invalid.` Can this be sorted out or I need to change `UInt64` to `Int64`? – phougatv Jul 06 '15 at 07:18
  • 1
    @barnes: did you remember the "unchecked" code block? – Neil N Jul 06 '15 at 14:01
  • No! I missed it. Thanks mate. :) – phougatv Jul 06 '15 at 16:07
6

I know it's not the same, but would this do:

select convert(decimal(38, 0), 12345678901234567890123456789012345678)

The maximum for an unsigned long seems to be 18,446,744,073,709,551,615 which is significantly less than this decimal can store. There might be issues converting, but I'm sure that a couple of wrapper functions in your application would sort it pretty quickly.

Jonathan
  • 23,467
  • 12
  • 62
  • 81
  • 1
    Good approach for accommodating the range of a ulong, however be aware that sql reports that decimal(38,0) takes up 17 bytes. – BC. Jun 02 '10 at 18:03
  • Are you saying it's oversize for the amount of data we want to store? – Jonathan Jun 02 '10 at 18:17
  • @Johnathan: I think so. `decimal(38,0)` is more than double the size of `bigint` (2.125 times, actually) . If space was an issue, I'd be a little concerned. – Onion-Knight Jun 02 '10 at 18:45
  • 7
    @Onion-Knight The [maximum value](http://msdn.microsoft.com/en-us/library/t98873t4(v=VS.100).aspx) of an unsigned long is 18,446,744,073,709,551,615, meaning you only need `DECIMAL(20, 0)`. This takes 13 instead of 17 bytes. The advantage of `DECIMAL` over `BIGINT` is that it will preserve the semantic meaning of the numbers you are storing. Any other application, ETL, report, or human that looks at the numbers will understand them. From a data architect's perspective, that justifies the storage penalty. The `BIGINT`, on the other hand, will present negative numbers (i.e. lies) to the reader. – Nick Chammas Jan 09 '12 at 22:24
0

For me Neil N's solution didn't work. Visual Studio kept complaining that I can't assign a long value to a ulong variable, no matter the unchecked.

What did work in the end was simply

ulong fileSize = (ulong)(long)reader["fileSize"];

(withoutunchecked).

In the SQLite DB the value is stored as long, so very high values become negative and sorting them on SQL-side fails. But after getting the ulong values Linq can do its magic

items = items.OrderByDescending(x => x.FileSize).ToList();
ecth
  • 879
  • 1
  • 8
  • 30