What is an efficient way to check the precision and scale of a numeric value?

Question

I'm writing a routine that validates data before inserting it into a database, and one of the steps is to see if numeric values fit the precision and scale of a Numeric(x,y) SQL-Server type.

I have the precision and scale from SQL-Server already, but what's the most efficient way in C# to get the precision and scale of a CLR value, or at least to test if it fits a given constraint?

At the moment, I'm converting the CLR value to a string, then looking for the location of the decimal point with .IndexOf(). Is there a faster way?

Padus

Answer 1

System.Data.SqlTypes.SqlDecimal.ConvertToPrecScale( new SqlDecimal (1234.56789), 8, 2)

gives 1234.57. it will truncate extra digits after the decimal place, and will throw an error rather than try to truncate digits before the decimal place (i.e. ConvertToPrecScale(12344234, 5,2)

Igenia

1234.57, but I voted you up anyway, because it's a great answer. – Glair

Answer 2

Without triggering an exception, you could use the following method to determine if the value fits the precision and scale constraints.

private static bool IsValid(decimal value, byte precision, byte scale)
{
    var sqlDecimal = new SqlDecimal(value);

    var actualDigitsToLeftOfDecimal = sqlDecimal.Precision - sqlDecimal.Scale;

    var allowedDigitsToLeftOfDecimal = precision - scale;

    return 
        actualDigitsToLeftOfDecimal <= allowedDigitsToLeftOfDecimal && 
        sqlDecimal.Scale <= scale;
}

Lythraceous

Answer 3

Here's a maths based approach.

private static bool IsValidSqlDecimal(decimal value, int precision, int scale)
{
    var minOverflowValue = (decimal)Math.Pow(10, precision - scale) - (decimal)Math.Pow(10, -scale) / 2;
    return Math.Abs(value) < minOverflowValue;
}

This takes into account how sql server will do rounding and prevent overflow errors, even if we exceed the precision. For example:

DECLARE @value decimal(10,2)
SET @value = 99999999.99499 -- Works
SET @value = 99999999.995   -- Error

Antinomy

Answer 4

You can use decimal.Truncate(val) to get the integral part of the value and decimal.Remainder(val, 1) to get the part after the decimal point and then check that each part meets your constraints (I'm guessing this can be a simple > or < check)

Sheedy