Noah Noah - 4 days ago 6
C# Question

Testing differences between SqlDecimal and C# Decimal

I'm trying to validate that a decimal in c# will fit into a db column decimal. The SqlDecimal object allows you to pass in a precision and scale as well as the decimal bits to the constructor. I know the size of the column and so before we write the data I check each input so I can produce business required output.

In this case we are storing a percent, so the precision is 13 and the scale is 10. I have a testing harness that I've condensed below into a unti test for SO. This sample is throwing an Arithmetic Overflow error on the SqlDecimal constructor line:

[TestMethod]
public void TestDecimalFits()
{
decimal d = 10.3m;
SqlDecimal sqlDecimal = new SqlDecimal(13, 10, d >= 0, Decimal.GetBits(d));
Assert.AreEqual(d, sqlDecimal.Value);
}


Does anyone know why this blows up?

Thanks!

Answer

The return of Decimal.GetBits is not compatible with the SqlDecimal's constructor parameter.

Decimal.GetBits returns an array that represents the exact structure of the decimal, which includes the 96-bit scaled integer value and 8 bits for the exponent (plus 1 sign bit and 27 unused bits).

The SqlDecimal constructor that you are using takes an int array that represents "The 128-bit unsigned integer that provides the value of the new SqlDecimal." - NOT the represenatation of that decimal value. The scale parameter determines what the actual value of the decimal will be.

So you are effectively passing in a different value than the constructor expects. The .NET decimal equivalent of 10.3m is

0000000001100111-0000000000000000-0000000000000000-10000000000000000

Where 1100111 is the binary equivalent of 103, and 1 is the scale.

The integer equivalent of that binary value is more than 13 digits long, which is why you get an overflow when passing it to the SqlDecimal constructor.

I would not play around with bit-fiddling and instead just use the raw decimal value, letting SQL convert it to the right precision and scale automatically.

I'm trying to validate that a decimal in c# will fit into a db column decimal.

Well, the largest value that will fit into a DECIMAL(13,10) is 999.9999999999, which is well below the maximum value of a decimal. So no, you can't stire any C# decimal value in a DECIMAL(13,10) SQL column.

(technically I think you can store 9999999999999 by dropping the precision, but even that is well below the maximum value of a decimal).

Comments