0

I am trying to load large decimal values from MySQL and Oracle database which results in exception. I understand that when it is trying to convert value into decimal datatype of .Net then it results in exception but what is the way to read such huge values from database?

Exception from MySQL database

System.Number.ParseDecimal(String value, NumberStyles options, NumberFormatInfo numfmt) at System.Convert.ToDecimal(String value, IFormatProvider provider) at MySql.Data.Types.MySqlDecimal.MySql.Data.Types.IMySqlValue.get_Value() at MySql.Data.MySqlClient.MySqlDataReader.GetValue(Int32 i) at MySql.Data.MySqlClient.MySqlDataReader.GetValues(Object[] values) at System.Data.ProviderBase.SchemaMapping.LoadDataRow() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

Exception from Oracle database

System.OverflowException: Arithmetic operation resulted in an overflow. at Oracle.DataAccess.Types.DecimalConv.GetDecimal(IntPtr numCtx) at Oracle.DataAccess.Client.OracleDataReader.GetDecimal(Int32 i) at Oracle.DataAccess.Client.OracleDataReader.GetValue(Int32 i) at Oracle.DataAccess.Client.OracleDataReader.GetValues(Object[] values) at System.Data.ProviderBase.SchemaMapping.LoadDataRow() at System.Data.Common.DataAdapter.FillLoadDataRow(SchemaMapping mapping) at System.Data.Common.DataAdapter.FillFromReader(DataSet dataset, DataTable datatable, String srcTable, DataReaderContainer dataReader, Int32 startRecord, Int32 maxRecords, DataColumn parentChapterColumn, Object parentChapterValue) at System.Data.Common.DataAdapter.Fill(DataTable[] dataTables, IDataReader dataReader, Int32 startRecord, Int32 maxRecords) at Oracle.DataAccess.Client.OracleDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior) at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

Here is my sample code in VB.Net

Using comm = createCommand(strSql, CommandType.Text,60)
   Using Da = _providerFactory.CreateDataAdapter
       Da.MissingSchemaAction = MissingSchemaAction.Add
       Da.SelectCommand = comm
       Da.SelectCommand.CommandTimeout = 60
       Dim dt As New DataTable
       Da.Fill(dt)                  
       Return dt
   End Using
End Using

Sample value which I am trying to read

792281625142643375935439503351.000000000000000000000000000012

I found various other links such as this one for my problem but none works for me. I am open to read the value as string and do conversions later on.

Edit 1:

Database queries are not under my control so I cannot edit the queries passed by the clients. Also I provide support for multiple database like MySql, Oracle, DB2, Sqlite, Sqlserver etc using factory pattern for creating DataAdapter, Hence it won't be feasible to edit the queries. My tool fetch data using queries provided by users and provide the output in xml format.

Edit 2:

I tried using Java to fetch the data and resultset.getString("column_name") is working fine to fetch data as String. Do we have something similar in .Net?

DataReader.GetString() provided in .Net is not working.

prem
  • 3,348
  • 1
  • 25
  • 57
  • 1
    Could you post one of those large values? So we get a grasp of the dimensions we are speaking of. Which links did you try and what were the problems with them? – DeveloperExceptionError Aug 07 '18 at 13:07
  • @DeveloperExceptionError: Question updated. – prem Aug 07 '18 at 13:12
  • @Mate Nope, double only has 15 digit precision. That was my first thought too :( – Lews Therin Aug 07 '18 at 13:17
  • The best way of handling this is to use BIGINT. Then multiply the number by 1xE30 so all the numbers are integers. You can simply remove the decimal point and parse the string with BigInt. – jdweng Aug 07 '18 at 13:57
  • @jdweng Conversion will work after fetching the data. It throws error while fetching the data. Even DataReader.GetString() is not working. – prem Aug 08 '18 at 10:26
  • Try following : Dim results = dt.AsEnumerable().Select(Function(x) x.Field(string)("column_name")).ToArray() – jdweng Aug 08 '18 at 11:20
  • @jdweng You code lines will work after dt is loaded but it is giving exception on Da.Fill(dt). This is the main problem that I am not able to read the data. – prem Aug 08 '18 at 13:41
  • The java driver is returning strings. The c# driver is probably using oledb which isn't using strings. You may be able to use a schema to force the numbers to be returned as strings. – jdweng Aug 08 '18 at 14:05

2 Answers2

0

As far as I know, the .NET Framework itself does not support that large decimal values at this moment.

But the stackoverflow user Gigo implemented a custom .NET Big Decimal variant (Source code). It is not production grade yet, but will work for most scenarios.

I would recommend trying his variant and reporting any issues to the repository, so they have the ability to fix them.

DeveloperExceptionError
  • 689
  • 11
  • 22
  • I think conversion is not the acutal issue for me. I am not able to read the value. Even I tried reading row by row using Data reader and storing the result as String but then also it throws the same error. – prem Aug 07 '18 at 13:26
0

Since the value is too large to be read as a .NET Decimal, you will have to convert it to a string on the database before executing a MySqlDataReader or using a MySqlDataAdapter to read it.

Change your SQL to:

SELECT CAST(BigDecimalValue AS CHAR), ... FROM table ...

Then, when you read this value, it will be a String containing the decimal digits. You will have to “read the value as string and do conversions later on.”

Bradley Grainger
  • 27,458
  • 4
  • 91
  • 108
  • Queries are not under my control. Client runs their own queries using my tool. – prem Aug 07 '18 at 13:33
  • @prem In that case I'm not aware of any other workarounds; because the column is typed as `DECIMAL`, MySQL Connector/NET will try to read it as a `decimal` (then fail because it's too large). – Bradley Grainger Aug 07 '18 at 13:35
  • Is is possible to read such large decimal values in .Net? If I can get some link that it is not possible then that can also help. – prem Aug 07 '18 at 13:48
  • @prem The documentation https://msdn.microsoft.com/en-us/library/system.decimal.aspx says “The Decimal value type represents decimal numbers ranging from positive 79,228,162,514,264,337,593,543,950,335 to negative 79,228,162,514,264,337,593,543,950,335.” Your example number 792,281,625,142,643,375,935,439,503,351 is ten times larger than the maximum decimal; therefore, it cannot be read as a `Decimal`. It is not possible to read this value as a `Decimal`. – Bradley Grainger Aug 07 '18 at 13:59
  • @prem "Queries are not under my control" but you do have strSQL. Can't you parse the strSQL and if the problem column is in there, then edit strSQL to reflect the Cast provided by Bradley Grainger – Mary Aug 07 '18 at 15:48
  • @Mary I cannot edit the queries passed by users as it is not permitted to me and also it won't be possible for me as my tool is database independent. I don't know which database client is going to use. It can be Oracle, Mysql, DB2, Sqlite , Sqlserver or something else. I use factory pattern to create DataAdapter and provide accessibility to all the database. – prem Aug 08 '18 at 10:17
  • @prem It sounds like you will just have to tell your users that “Storing values larger than Decimal.MaxValue is not supported.” – Bradley Grainger Aug 08 '18 at 15:51