Overflow exception while reading decimal values ​​from SQL Server

Question

I am wondering if this is a mistake or if something is wrong.

I load values ​​using SqlDataReader from a SQL Server 2008 database, but under certain circumstances it cannot convert SQL values ​​to .net values. (.NET 4.0)

I traced it to a test case that shows the actual problem:

Working example:

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

Exmaple does not work:

 "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 reviewed the actual values ​​that SQL Server saved. They differ in that the non-worker uses 4 integers to express the value, and the worker uses only 3.

I also checked the source code with .net Reflector, which imagined that an exception is thrown if the value exists for more than 3 values, but I don’t understand what the mechanics are behind.

So, I am wondering if this is a genuine bug in the .NET Framework.

Answer 1

Not that you are doing wrong, except that you are perhaps too accurate. I do not think this is a new problem.

You can argue that this is a bug, or just a gap in functionality. The .Net Decimal structure simply cannot represent the value that is stored in your SQL Server Decimal , so an OverflowException .

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

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

It is probably easiest to use only a compatible Decimal definition if you really don't need this precision. If you do, I would be interested to know why.