How To Convert JSON To Data Table Without Decimal Values Rounding

News Knowledge Base
1

When using JsonConvert.DeserializeObject(Of DataTable)(), sometimes, double values are rounded after the conversion it is made. How to avoid this rounding issue?

Issue Description: When converting a Json string to a data table using the JsonConvert.DeserializeObject(Of DataTable)() method, double values may be rounded after the conversion is made.

Example of Issue:

Observe Json String that is passed in for conversion. Make note of the sapAmout as well as unitPrice & lineAmount.

Now notice the output of the data table after using the JsonConvert.DeserializeObject(Of DataTable)() method.

The SAPAmount in the second row should have been 12.86, however, the value was rounded to 13.

The value for unitPrice & lineAmount remained as they should. The initial value in the Json is a double (244.98) and the output remains 244.98 without rounding.

Root Cause: The cause of this issue is due to a known limitation of the DataTableConverter that is part of the Json Library. When the JsonConverter attempts to convert the data from the Json to a data table, the first value that is encountered for each column is what the JsonConverter uses as the default data type for the column.

For the example above, the first value that is encountered for SAPAmount looks to be an integer (14). The JsonConverter sees that the datatype is an integer, and it assumes the entire column for SAP Amount should be set to integer values. (Which causes the rounding issue.)

Counter Example:

Observe Json String that is passed in for conversion. Make note of the sapAmout as well as unitPrice & lineAmount.

Now notice the output of the data table after using the JsonConvert.DeserializeObject(Of DataTable)() method.

In the counter example above the SAPAmount in the second row was not rounded, and it remained to be 12.86. The difference here is that the first value of the SAPAmount in the Json is a double (14.00) and not an integer (14) as in the first example.

Also notice that the value for unitPrice & lineAmount look to remain as they should. The initial value n the Json is a double (244.98) and the output remains 244.98 without rounding.

Resolution: To avoid this rounding issue when converting Json to a Datatable, one possible solution is to try Deserializing the data to a JSON Array (or List<>)

Example Solution:

  1. Build a Data Table with the required columns and set the desired data type for each column
  2. Deserialize the Json String
  3. Deserialize Json Array from the Json Object that was Output from Step 2.
  4. For Each item in the JsonArray, Add a Data Row to your Datatable with the required values.

Using the method in the example above results in the following output:

Even though the first value of SAPAmount is an integer (14), this has no impact on how the second SAPAmount is read. The value does not round, at it remains a double.


Also, refer the post Deserialization Of Dynamic Json To A Datatable Is Losing Decimals When First Few Json Items Have Non - Decimal Values .

1 Like