CODEWITHSUNDEEP
c#sql-serverclrdecimal
handles
handles

Reputation: 7853

Validating decimal in C# for storage in SQL Server

I have a decimal database column decimal (26,6).

As far as I can gather this means a precision of 26 and a scale of 6.

I think this means that the number can be a total of 26 digits in length and 6 of these digits can be after the decimal place.

In my WPF / C# frontend I need to validate an incoming decimal so that I can be sure that it can be stored in SQL Server without truncation etc.

So my question is there a way to check that decimal has a particular precision and scale.

Also as an aside I have heard that SQL Server stores decimal in a completely different way to the CLR, is this true and if so is it something I need to worry about?

Upvotes: 3

Views: 6167

Answers (2)

user474407
user474407

Reputation:

A straightforward way to determine if the precision,scale of a given decimal number is greater than 26,6 would be to check the length of its string equivalent.

public static bool WillItTruncate(double dNumber, int precision, int scale)
{
    string[] dString = dNumber.ToString("#.#", CultureInfo.InvariantCulture)
        .Split('.');
        
    return (dString[0].Length > (precision - scale) || 
           (dString.Length > 1
                ? dString[1].Length > scale
                : true)
    );
}

The maximum precision for the C# decimal data type seems to be 29 digits, whereas SQL's decimal type can have 38 digits. So you may not be hitting the maximum value of SQL decimal from C#.

Upvotes: 5

BorisSh
BorisSh

Reputation: 571

If you already know destination scale and precision of decimal type at compile time, do simple comparison. For decimal(13,5):

public static bool IsValidDecimal13_5(decimal value)
{
    return -99999999.99999M <= value && value <= 99999999.99999M;
}

Upvotes: 1

Related Questions