CODEWITHSUNDEEP
c#.netsql-serverexceptiondecimal
nightcoder
nightcoder

Reputation: 13509

OverflowException when converting SqlDecimal to Decimal

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:

Debugging a unit test

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

Upvotes: 3

Views: 1934

Answers (1)

Rustam
Rustam

Reputation: 46

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);

Upvotes: 2

Related Questions