This is a known limitation with the DataTableConverter that ships with Json.Net. The converter assumes the first row of data in the JSON is a representative sample for all the rows and uses that to determine the data types for the columns in the DataTable.
If you know in advance what data types you have in your JSON, one way to work around the problem is to deserialize to a List<T> instead of a DataTable, where T is a class with property names and types matching the JSON. Then, if you still need a table, you can construct it from the list as a post-processing step.
However, you said that your JSON is dynamic, so you will need to use a custom JsonConverter instead. It's possible to make one which can read ahead through the JSON to determine what is the best data type to use for each column. Something like the following should work. Feel free to customize it to your needs.
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) && currentType == typeof(decimal)))
{
dataTypes[prop.Name] = currentType;
}
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();
}
}
To use the converter, pass an instance to the DeserializeObject method like this:
DataTable dt = JsonConvert.DeserializeObject<DataTable>(data, new ReadAheadDataTableConverter());
Note that this converter will run a little slower than the OOB DataTableConverter due to the extra processing. With small data sets it should not be noticeable.
Working demo here: https://dotnetfiddle.net/iZ0u6Y
dynamicinsteadDataTable? - Mohammed Sajid