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?

Answer 1

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

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

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

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