JsonConvert.DeserializeObject<DataTable>(jsondata); force datetime to string column

2
votes

this is my json :

[
 {"ID":"1","DATETIME":"2018-05-02T00:00:00"},
 {"ID":"2","DATETIME":"2018-05-02T00:00:00"},
 {"ID":"3","DATETIME":""},
 {"ID":"4","DATETIME":"2018-05-02T00:00:00"}
]

I'm using DataTable dt = JsonConvert.DeserializeObject<DataTable>(jsondata); to convert from json to datatable, however with the null datetime column I get the following error:

String was not recognized as a valid DateTime.Couldn't store <> in DATETIMEColumn. Expected type is DateTime.

So how can I convert my json to datatable with all columns is string

2
c#jsonjson.net
Why not define a class and deserialize as an IEnumerable of that? - GPW
@GPW my json have dynamic columns - jimbo R
@jimboR There is nothing dynamic based on your example - maccettura
@DavidG: Depending on the condition that my json string may have some other attributes,but ID and DATETIME are always there. - jimbo R
@jimboR You could define the class as having nullable fields for the optional data and it should work fine - those fields will just be null if they're not in the JSON... that's what I would do. Accepted answer below is good for your specific question though. - GPW

2 Answers

7
votes

You can define the Serializer settings to Ignore DateTime parsing so that the dynamic JOSN can be converted into DataTable. Downnside of that that DateTime will be read as strings in DataTable.

var settings = new JsonSerializerSettings
        {
            DateParseHandling = DateParseHandling.None
        };
        
string json = "[{\"ID\":\"1\",\"DATETIME\":\"2018-05-02T00:00:00\"}, {\"ID\":\"1\",\"DATETIME\":\"\"}]";

var jsonObj = JsonConvert.DeserializeObject<DataTable>(json, settings);
        
Console.WriteLine(jsonObj.Rows.Count);

You can read DateParseHandling setting at here.

Check this Live Fiddle

0
votes

The problem of serialising to a DataTable is that you get on control of how the columns are created, and in your example the empty date is the problem. While it is possible to create your own custom JsonConverter, that would be quote complex and lots of code, my advice would be instead to deserialise to List<Dictionary<string, string>>. For example:

var result = JsonConvert.DeserializeObject<List<Dictionary<string, string>>>(jsondata);

Now you can loop through your dynamic data:

foreach (var dictionary in result)
{
    foreach (var kvp in dictionary)
    {
        Console.WriteLine($"Key: {kvp.Key}, Value: {kvp.Value}");
    }
}

Which would give you output like this: (including any additional properties are in your JSON data:

Key: ID, Value: 1
Key: DATETIME, Value: 2018-05-02T00:00:00
Key: ID, Value: 2
Key: DATETIME, Value: 2018-05-02T00:00:00
Key: ID, Value: 3
Key: DATETIME, Value: 
Key: ID, Value: 4
Key: DATETIME, Value: 2018-05-02T00:00:00