Issue converting JSON string to DataTable C#

Question

Having an issue getting a JSON string to convert into a C# Datatable...

Here is an example of the JSON string that gets returned to me code from an API

   {
  "Code": 0,
  "Result": [
    {
      "ID": 1,
      "Reference": "101",
      "Asset": 200,
      "Event": 1,
      "DateEventStart": "2/10/2017 9:08:33 PM",
      "DateEvent": "2/11/2017 1:00:14 AM"
    }
]
}

So that comes in from the API and I read it, then attempt to create a DataTable like this

        var response = (HttpWebResponse)httpWebRequest.GetResponse();
        var encoding = Encoding.GetEncoding(response.CharacterSet);
        var responseStream = response.GetResponseStream();
        var reader = new StreamReader(responseStream, encoding);
        responsetext = reader.ReadToEnd();

        var table = JsonConvert.DeserializeObject<DataTable>(responsetext);

However this gives the following error:

Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path '', line 1, position 1

I'm new to dealing with JSON in C# so any help appreciated. I think that my string is invalid in some way in terms of getting it to be parsed, but I don't know. If there is more info required let me know

EDIT: Ok I followed the link that Xingxing Qiao put - makes sense. I now get a different error which I think is due to what I've put into the class that the JSON gets deserialised into -

Cannot deserialize the current JSON object (e.g. {"name":"value"}) into type 'System.Collections.Generic.List`1[WindowsFormsApplication7.TrackingResponse]' because the type requires a JSON array (e.g. [1,2,3]) to deserialize correctly.

To fix this error either change the JSON to a JSON array (e.g. [1,2,3]) or change the deserialized type so that it is a normal .NET type (e.g. not a primitive type like integer, not a collection type like an array or List) that can be deserialized from a JSON object. JsonObjectAttribute can also be added to the type to force it to deserialize from a JSON object.

Path 'Code', line 1, position 8.

Here is that class...is it because I don't include 'Code' and 'Result' from the JSON (see string above)

public class TrackingResponse
{
    public string ID { get; set; }
    public string Reference { get; set; }
    public string Asset { get; set; }
    public string Event { get; set; }
    public string DateEventStart { get; set; }
    public string DateEvent { get; set; }

}

check this, [convert-json-to-datatable](http://stackoverflow.com/questions/11981282/convert-json-to-datatable) — Xingxing Qiao, Feb 11 '17 at 15:24
Looks like a duplicate of [Deserialize a nested DataSet from Json String with Json.NET](https://stackoverflow.com/a/42090584/3744182). As in that answer, you need a wrapper root object to contain your `DataTable`. Does that answer your question sufficiently, or do you need the specific root object? — dbc, Feb 11 '17 at 15:37
Why do you need a DataTable in the first place? Plain model objects are far easier to work with. — mason, Feb 11 '17 at 18:14

dbc · Accepted Answer · 2017-02-11T18:10:48.033

Your problem is that your root JSON container is not an array, it is an object. As explained in the JSON standard:

An array is an ordered collection of values. An array begins with [ (left bracket) and ends with ] (right bracket). Values are separated by , (comma).
An object is an unordered set of name/value pairs. An object begins with { (left brace) and ends with } (right brace).

Newtonsoft tries to map a DataTable (as well as objects of type IEnumerable, List, and Array) from and to an array, and as the root container is not an array, it fails with the Expected StartArray, got StartObject error.

Since your root container is an object with two properties "Code" and "Result", you need to create a POCO to which to bind those properties. Now, the value of "Result" is an array, so could be mapped to a DataTable, or to a List<T> for some appropriate T. To generate the necessary classes, you can use a code-generation tool such as http://json2csharp.com/ or Paste JSON As Classes in visual studio and get something like the following (slightly modified to make the root object generic):

public class Result
{
    public int ID { get; set; }
    public string Reference { get; set; }
    public int Asset { get; set; }
    public int Event { get; set; }
    public string DateEventStart { get; set; }
    public string DateEvent { get; set; }
}

public class RootObject<T>
{
    public int Code { get; set; }
    public T Result { get; set; }
}

You can now deserialize your JSON as a RootObject<DataTable> or a RootObject<List<Result>>:

var rootOfList = JsonConvert.DeserializeObject<RootObject<List<Result>>>(responsetext);

Or

var rootOfTable = JsonConvert.DeserializeObject<RootObject<DataTable>>(responsetext);
DataTable table = rootOfTable.Result;

Sample fiddle.

That has helped me immensely - much appreciated (and thanks for explaining it rather than just sticking some code on here) — reviloRetals, Feb 12 '17 at 12:05

Issue converting JSON string to DataTable C#

1 Answers1