OverflowException when converting SqlDecimal to Decimal

Question

In our real application we get an OverflowException when trying to convert SqlDecimal value of 99 to System.Decimal. The exception is thrown because SqlDecimal's precision is higher than System.Decimal's precision.

Here is a test that reproduces the issue:

[Test]
public void SqlDecimalToDecimalOverflowTest()
{
    // in our real application d1 & d2 are loaded from a database; both are declared as DECIMAL(28, 5)
    // for test purposes I recreate identical SqlDecimal objects here:
    var d1 = new SqlDecimal(28, 5, true, 9900000, 0, 0, 0); // Debugger shows {99.00000}
    var d2 = new SqlDecimal(28, 5, true, 100000, 0, 0, 0); // Debugger shows {1.00000}

    var d3 = d1.Value / d2; // Debugger shows d1.Value as {99}, d1.Value is of type decimal (not SqlDecimal)
    var exception = d3.Value; // Debugger shows d3 as {99.0000000000000000000000000000000}
}

A screenshot:

The question is:
What is the fastest way to convert such SqlDecimal objects to Decimal?

Some time ago I wrote this helper method:

public static SqlDecimal RecreateWithMinPrecScale(this SqlDecimal value)
{
    string s = value.Scale > 0 ? value.ToString().TrimEnd('0') : value.ToString();
    int delimiterIndex = s.IndexOf(".");
    int precision = s.Length - (delimiterIndex >= 0 ? 1 : 0) - (s.StartsWith("-") ? 1 : 0);
    int scale = delimiterIndex >= 0 ? s.Length - 1 - delimiterIndex : 0;
    return SqlDecimal.ConvertToPrecScale(value, precision, scale);
}

so I can write

var ok = d3.RecreateWithMinPrecScale().Value; // no exception

But obviously this is a slow and inefficient method and we need to do billions of such calculations.

Please, let us not discuss why we use SqlDecimal class and not just System.Decimal (it's a financial application and I believe previously we had requirements to support very long numbers (either large or precise) and it was said that 28-29 digits of System.Decimal can be not enough).

Tone · Accepted Answer · 2016-05-26 02:07:25Z

2

You should restore values of the precision and scale attributes in the d3 after the divide operation:

var d3 = d1.Value / d2;
d3 = SqlDecimal.ConvertToPrecScale(d3, 28, 5);

edited May 26, 2016 at 2:07

Tone

1,7552 gold badges17 silver badges21 bronze badges

answered May 26, 2016 at 1:31

Rustam

464 bronze badges

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

1 Comment

nightcoder Over a year ago

Thank you, Rustam. In our case, yes, we can use ConvertToPrecScale() and specify correct precision and scale, because we have this information in the attribute in our generated data model. But what if I want a general solution and don't know the precision and scale? I mean, what if I just want to remove extra/unnecessary scale from the number (like what my RecreateWithMinPrecScale() method does)?

Collectives™ on Stack Overflow

OverflowException when converting SqlDecimal to Decimal

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related