C# / TSQL decimal boundary check - Is there a cleaner way?

Question

I wrote up a quick c# extension method, but wasn't sure if there was a cleaner way to accomplish what I want to do. It does work, but feels slightly hacky using the string repeater, and inserting a decimal.

Goal is that at the application level, we can clean / fix any data problems before sending off to the database to prevent overflows.

Note: PCL Library, cant reference outside DLL's in this case.

public static bool TsqlDecimalBoundariesCheck(this decimal valueToCheck, int precision, int scale)
    {
        if(scale > precision) throw new ArgumentException($"BOUNDARY CHECK: Scale [{scale}] must not be higher than Percision [{precision}]");

        // create X precision values of the value 9
        var precisionValue = new string('9', precision);

        // Insert the decimal place x positions from the right 
        if (scale > 0)
        {
            precisionValue = precisionValue.Insert((precision - scale), ".");
        }

        // Get the upper and lower values
        var upperBoundry = decimal.Parse(precisionValue);
        var lowerBoundry = upperBoundry * -1;

        return (valueToCheck <= upperBoundry) && (valueToCheck >= lowerBoundry);
    }

And some quick unit tests to accompany it:

    [TestMethod]
    public void TestBoundryConstraints()
    {
        var precision = 4;
        var scale = 1;

        var testValue = 1000m;
        var result = testValue.TsqlDecimalBoundariesCheck(precision , scale);
        Assert.IsFalse(result, $"Value {testValue} is expected to be outside Decimal({precision }, {scale})");

        testValue = -1000m;
        result = testValue.TsqlDecimalBoundariesCheck(precision , scale);
        Assert.IsFalse(result, $"Value {testValue} is expected to be outside Decimal({precision }, {scale})");

        testValue = 100m;
        result = testValue.TsqlDecimalBoundariesCheck(precision , scale);
        Assert.IsTrue(result, $"Value {testValue} is expected to be within Decimal({precision }, {scale})");

        testValue = 999.9m;
        result = testValue.TsqlDecimalBoundariesCheck(precision , scale);
        Assert.IsTrue(result, $"Value {testValue} is expected to be within Decimal({precision }, {scale})");

        testValue = -999.9m;
        result = testValue.TsqlDecimalBoundariesCheck(precision , scale);
        Assert.IsTrue(result, $"Value {testValue} is expected to be within Decimal({precision }, {scale})");
    }

The `1000m` test is duplicated and you typo'd `precision` consistently. :) — xxbbcc, Apr 24 '17 at 22:16
Thanks.. yea, horrible speller.. as most devs are as I understand it. Thank for the catch. Why has VS not added Spell Check in yet ;) — TravisWhidden, Apr 24 '17 at 22:33
@MrZander -- thanks for that, I should have mentioned this is in a PCL lib without any external references, that doesn't have access to that namespace. But that is a good solution if someone needed it. — TravisWhidden, Apr 24 '17 at 22:35
Looks like a job for [Code Review](https://codereview.stackexchange.com/). — Jeroen Mostert, Apr 25 '17 at 09:32
@TravisWhidden Do you care about truncation of the scale? Your current check doesn't seem to. For example, 999.09m is precision 5, scale 2, but passes your unit test. of precision 4, scale 1. — MrZander, Apr 25 '17 at 15:49
That is a very good point. Doing some tests in SQL it appears it will round to the correct scale when inserting. I hadnt considered the rounding part of this, but in my specific use case, I believe that is ok. Insert into TestData (SomeData) Values(999.09); results in 999.1 — TravisWhidden, Apr 25 '17 at 21:05

score 0 · Accepted Answer · answered Apr 25 '17 at 16:44

So you can definitely get rid of the hacky string repeating by doing (10^p - 1) * (10^-s) to get your upper and lower bounds.

If you want to check to make sure scale doesn't get truncated, you can actually truncate it and then compare the values. If the truncated value and the original value are the same, the scale is valid.

Putting it all together, you get something like this:

public static bool TsqlDecimalBoundariesCheck(this decimal valueToCheck, int precision, int scale)
{
    if (scale > precision) throw new ArgumentException($"BOUNDARY CHECK: Scale [{scale}] must not be higher than Percision [{precision}]");

    //Upper/lower bounds 
    var step = (decimal)Math.Pow(10, precision);
    var upperBoundry = (step - 1) * (decimal)Math.Pow(10, -scale);
    var lowerBoundry = -1 * upperBoundry;

    //Truncate decimal to scale   
    //If the truncated value does not equal the original, it must've been out of scale
    step = (decimal)Math.Pow(10, scale);
    var truncated = Math.Truncate(step * valueToCheck) / step;

    return (valueToCheck <= upperBoundry)
        && (valueToCheck >= lowerBoundry)
        && truncated == valueToCheck;
}

This is truly a very clean way to enforce the scale, but researching my use case, the rounding is acceptable, but 100% a great point about what was more important, enforcement of the scale or acceptance of the rounded value. In the case of 999.09, the value still is within the TSQL Decimal(4,1) in SQL, but will result in a storing the rounded value 999.1. In the end, based on the way I wrote my question and not being precise about this point, I think your answer still answers it, even if it doesn't completely solve my problem which would allow rounding. — TravisWhidden, Apr 25 '17 at 21:15
@TravisWhidden Thanks! At the very least, you can strip out the truncation check and the function will perform the same way yours is currently working, just without the string manipulation. — MrZander, Apr 26 '17 at 04:47

C# / TSQL decimal boundary check - Is there a cleaner way?

1 Answers1