C# Json string to DataTable, DataSet JsonConvert.DeserializeObject loss of precision problem
Problem description
When I was converting Json string to DataTable, I found that if I have a column of decimals, but the first row is an integer, all decimal digits below will be lost.
Example
public const string _InvoiceDataJsonTable = "[{"EXCHANGE_USD":24},{"EXCHANGE_USD":15},{"EXCHANGE_USD": 191.06}]";
DataTable dtTable = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(_InvoiceDataJsonTable);
My expected result is that a DataTable has one column [EXCHANGE_USD] and three rows of data, namely
24, 15, 191.06.
But the actual result is
24, 15, 191.
After checking some relevant information, the general meaning is that when JsonConvert converts to DataTable, the first row of data will be regarded as the template data of the data type. The first row of my string is 24, and the type of the DataColumn of the JsonConvert task is Int, so directly give The attributes of this column are declared, and then when written, 191.06 will be intercepted into 191.
Solution
DataTable
public const string _InvoiceDataJsonTable = "[{"EXCHANGE_USD":24},{"EXCHANGE_USD":15},{"EXCHANGE_USD": 191.06}]";
DataTable dtTable = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(_InvoiceDataJsonTable,new ReadAheadDataTableConverter());
Declaring a rule into an entity class can solve this problem.
See below for the code of the entity class
Same as extending DataSet
DataSet
public const string _InvoiceDataJsonSet = "{"CF_INVOICE":[{"EXCHANGE_USD":24},{"EXCHANGE_USD":15}, {"EXCHANGE_USD":191.06}]}";
DataTable dtSet = Newtonsoft.Json.JsonConvert.DeserializeObject<DataTable>(_InvoiceDataJsonTable,new ReadAheadDataSetConverter());
appendix:
ReadAheadDataTableConverter
public class ReadAheadDataTableConverter : JsonConverter
{
public override bool CanConvert(Type objectType)
{
return objectType == typeof(DataTable);
}
public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
{
JArray array = JArray.Load(reader);
var dataTypes = DetermineColumnDataTypes(array);
var table = BuildDataTable(array, dataTypes);
return table;
}
private DataTable BuildDataTable(JArray array, Dictionary<string, Type> dataTypes)
{
DataTable table = new DataTable();
foreach (var kvp in dataTypes)
{
table.Columns.Add(kvp.Key, kvp.Value);
}
foreach (JObject item in array.Children<JObject>())
{
DataRow row = table.NewRow();
foreach (JProperty prop in item.Properties())
{
if (prop.Value.Type != JTokenType.Null)
{
Type dataType = dataTypes[prop.Name];
row[prop.Name] = prop.Value.ToObject(dataType);
}
}
table.Rows.Add(row);
}
return table;
}
private Dictionary<string, Type> DetermineColumnDataTypes(JArray array)
{
var dataTypes = new Dictionary<string, Type>();
foreach (JObject item in array.Children<JObject>())
{
foreach (JProperty prop in item.Properties())
{
Type currentType = GetDataType(prop.Value.Type);
if (currentType != null)
{
Type previousType;
if (!dataTypes.TryGetValue(prop.Name, out previousType) ||
(previousType == typeof(long) & amp; & amp; currentType == typeof(decimal)))
{
dataTypes[prop.Name] = currentType;
}
else if (previousType == typeof(decimal) & amp; & amp; currentType == typeof(long))
{
dataTypes[prop.Name] = previousType;
}
else if (previousType != currentType)
{
dataTypes[prop.Name] = typeof(string);
}
}
}
}
return dataTypes;
}
private Type GetDataType(JTokenType tokenType)
{
switch(tokenType)
{
case JTokenType.Null:
return null;
case JTokenType.String:
return typeof(string);
case JTokenType.Integer:
return typeof(long);
case JTokenType.Float:
return typeof(decimal);
case JTokenType.Boolean:
return typeof(bool);
case JTokenType.Date:
return typeof(DateTime);
case JTokenType.TimeSpan:
return typeof(TimeSpan);
case JTokenType.Guid:
return typeof(Guid);
case JTokenType.Bytes:
return typeof(byte[]);
case JTokenType.Array:
case JTokenType.Object:
throw new JsonException("This converter does not support complex types");
default:
return typeof(string);
}
}
public override bool CanWrite
{
get { return false; }
}
public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
{
throw new NotImplementedException();
}
}
ReadAheadDataSetConverter
public class ReadAheadDataSetConverter : JsonConverter
{
public override bool CanConvert(Type objectType)
{
return objectType == typeof(DataSet);
}
public override object ReadJson(JsonReader reader, Type objectType, object existingValue, JsonSerializer serializer)
{
JObject jObject = JObject.Load(reader);
return BuildDataSet(jObject);
}
private DataSet BuildDataSet(JObject jObject)
{
DataSet dsResult = new DataSet();
foreach (JProperty propds in jObject.Properties())
{
DataTable table = new DataTable(propds.Name);
var dataTypes = new Dictionary<string, Type>();
foreach (JObject item in propds.Value.Children<JObject>())
{
foreach (JProperty prop in item.Properties())
{
Type currentType = GetDataType(prop.Value.Type);
if (currentType != null)
{
Type previousType;
if (!dataTypes.TryGetValue(prop.Name, out previousType) ||
(previousType == typeof(long) & amp; & amp; currentType == typeof(decimal)))
{
dataTypes[prop.Name] = currentType;
}
else if (previousType == typeof(decimal) & amp; & amp; currentType == typeof(long))
{
dataTypes[prop.Name] = previousType;
}
else if (previousType != currentType)
{
dataTypes[prop.Name] = typeof(string);
}
}
}
}
foreach (var kvp in dataTypes)
{
table.Columns.Add(kvp.Key, kvp.Value);
}
foreach (JObject itemdr in propds.Value.Children<JObject>())
{
DataRow row = table.NewRow();
foreach (JProperty prop in itemdr.Properties())
{
if (prop.Value.Type != JTokenType.Null)
{
Type dataType = dataTypes[prop.Name];
row[prop.Name] = prop.Value.ToObject(dataType);
}
}
table.Rows.Add(row);
}
dsResult.Tables.Add(table);
}
return dsResult;
}
private Type GetDataType(JTokenType tokenType)
{
switch(tokenType)
{
case JTokenType.Null:
return null;
case JTokenType.String:
return typeof(string);
case JTokenType.Integer:
return typeof(long);
case JTokenType.Float:
return typeof(decimal);
case JTokenType.Boolean:
return typeof(bool);
case JTokenType.Date:
return typeof(DateTime);
case JTokenType.TimeSpan:
return typeof(TimeSpan);
case JTokenType.Guid:
return typeof(Guid);
case JTokenType.Bytes:
return typeof(byte[]);
case JTokenType.Array:
case JTokenType.Object:
throw new JsonException("This converter does not support complex types");
default:
return typeof(string);
}
}
public override bool CanWrite
{
get { return false; }
}
public override void WriteJson(JsonWriter writer, object value, JsonSerializer serializer)
{
throw new NotImplementedException();
}
}