Testing differences between SqlDecimal and C# Decimal

Question

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!

You can use the SqlDecimal constructor with the decimal type directly. Like this SqlDecimal sqlDecimal = new SqlDecimal(d);. — Donnie
– Donnie, Commented Nov 30, 2016 at 20:31
I can, but then it doesn't validate the trailing zero problem I mentioned, below in a comment. This throws an error and will work: SqlDecimal.ConvertToPrecScale(new SqlDecimal(value), precision, scale); — Noah
– Noah, Commented Nov 30, 2016 at 20:31

D Stanley · Accepted Answer · 2017-09-14 19:00:44Z

1

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 store 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).

edited Sep 14, 2017 at 19:00

answered Nov 30, 2016 at 20:07

D Stanley

153k12 gold badges189 silver badges257 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Noah Over a year ago

SqlDecimal's constructor takes in an int array, not a byte array, but this likely is the reason. The problem with just passing a decmial in is that the trailing zeros on the decimal c# decimal are flexible, where as in sql server they are always padded, leading to overflow issues on the output.

Noah Over a year ago

Ah, ok, so not messing with the bits myself does work, I just have to set the precision and scale after. Thank you! SqlDecimal.ConvertToPrecScale(new SqlDecimal(value), precision, scale);

D Stanley Over a year ago

Yes that would work, however you should not need to create a SqlDecimal directly. The SQL provider should implicitly convert your decimal value for you. If you have a case where you need to create a SqlDecimal then that should work.

Andy McLaughlin · Accepted Answer · 2016-11-30 20:06:35Z

0

My guess is that Decimal.GetBits(d) is overflowing the Int32 return type. Try doing just Int32 bits = Decimal.GetBits(d) and see if it throws the same error.

answered Nov 30, 2016 at 20:06

Andy McLaughlin

111 silver badge2 bronze badges

2 Comments

D Stanley Over a year ago

Decimal.GetBits returns an int[], not an Int32

Noah Over a year ago

Worth a shot, breaking that out into its own line still resulted in an exception being thrown from the SqlDecimal constructor.

Collectives™ on Stack Overflow

Testing differences between SqlDecimal and C# Decimal

2 Answers 2

3 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related