Overflow exception when reading decimal values from SQL Server

Question

I'm wondering whether this is a bug or if I'm going something wrong.

I'm loading values with a SqlDataReader from a SQL Server 2008 database but under certain circumstances, it fails to convert the SQL values into .net values. (.NET 4.0)

I have traced it down to an test-case which demonstrates the actual problem:

Working example:

"select convert(decimal(38, 19), 260000 ) as test" rs.GetValue(1); --> returns 260000 (decimal)

Not working exmaple:

"select convert(decimal(36, 26), 260000 ) as test" rs.GetValue(1); --> throws System.OverflowException: Conversion overflows. at System.Data.SqlClient.SqlBuffer.get_Decimal() at System.Data.SqlClient.SqlBuffer.get_Value() at System.Data.SqlClient.SqlDataReader.GetValueInternal(Int32 i) at System.Data.SqlClient.SqlDataReader.GetValues(Object[] values)

I have examined the actual values that the SQL Server retured. They differ that the non-working one uses 4 integers to express the value, the working one only 3.

I did also check the source code with .net Reflector which unveiled that an exception is thrown if the value exists of more then 3 values, but I dont understand the mechanics behind them.

So, I'm wondering whether this is a genuine bug in the .net framework.

Precision for decimal values is only upto 28 .Please go thru the link stackoverflow.com/questions/745270/c-sharp-sizeof-decimal — praveen
– praveen, Commented Jul 4, 2012 at 9:35

Vala Khosravi · Accepted Answer · 2022-09-03 08:00:48Z

It's not something you are doing wrong, apart from being overly precise perhaps. I don't think its a new problem either.

You could argue it's a bug or just a gap in functionality. The .Net Decimal structure just can't represent the value that is stored in your SQL Server decimal so an OverflowException is thrown.

Either you need to manipulate the value to something compatible in the database before you retrieve it or, read the data out in a raw binary or string format and manipulate it on the .Net side.

Alternatively, you could write a new type that handles it.

It's probably simpler just to use a compatible decimal definition in the first place unless you really need that precision. If you do I'd be interested to know why.

The precision parameters were chosen by the SQL server itself. I was originally doing some basic calculations on the server between (10,2) and (10,4) fields. The server picked (36,26) as the return type. I changed all fields to (18,4) now, and its working so far.
@Jodrell - like your username.. I live about 15 minutes from Jodrell Bank. Love visiting that area. Also this issue is so annoying we have a (30,20) in SQL we need to store it like that and we cant save or read it back.. even though it fits in Decimal. System.Data.DataException : Error parsing column 1 (FactorCO2e=4 - Int32) ----> System.OverflowException : Conversion overflows.

Collectives™ on Stack Overflow

Overflow exception when reading decimal values from SQL Server

1 Answer 1

2 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Linked

Related