CODEWITHSUNDEEP
c#.netdecimal
vicch
vicch

Reputation: 564

Set an SqlDecimal variable to the max value it can store

In a certain scenario, I have to set in .net code the value of a decimal variable to a very high value so I use Decimal.MaxValue. Unfortunately, the value of the variable will be saved to the DB on a decimal(18,6) field and when that happens I obviously end up with an arithmetic overflow error.

I don't wan't to hardcode the variable value to 999999999999.999999 so I was wondering if there would be a way to set an instance of a SqlDecimal to the largest number it can handle and then get the Decimal value from it. For example, I can instantiate an SqlDecimal like this:

SqlDecimal sqldec = new SqlDecimal(18,6,true, new int[]{0});

but don't know if I can set it to the max value that can be stored in this instance without hardcoding to the value above.

Upvotes: 1

Views: 1133

Answers (1)

Renaud Bancel
Renaud Bancel

Reputation: 893

To obtain the maximum binary representation of a SqlDecimal of a given precision and scale use : 

SqlDecimal.Parse("".PadRight(precision-scale,'9')+"."+"".PadRight(scale,'9'));

For example for decimal(18,6): 

public static readonly SqlDecimal MaxDecimal18_6 = SqlDecimal.Parse("".PadRight(18-6,'9')+"."+"".PadRight(6,'9'));

To make your boot faster you can take a look at _maxDecimal18_6.Data array in debug mode and convert the previous line to : 

public static readonly SqlDecimal MaxDecimal18_6 = new SqlDecimal(18, 6, true, -1486618625, 232830643, 0, 0);

For a better understanding the precision+scale give the maximum number of digits. These digits are stored as a maximum of 38 decimal digits in a 128 bits binary number (accessed via Data as a four int array). You should notice that each precision+scale give a distinct maximum value and because a 128 bit number may be greater than (10^39)-1 = 999999 .... 38 times, it is impossible to use new SqlDecimal(38,6,true, -1, -1, -1, -1); to obtain the highest value (-1 signed gives all bits to one) because this initializer is higher than the highest decimal value.

Upvotes: 1

Related Questions