I need to save data retrieved from API to a DataTable. JSON which is returned from API can't be deserialized directly to DataTable using this code:

DataTable dt = (DataTable)JsonConvert.DeserializeObject(json, (typeof(DataTable)));

I got an error: Unexpected JSON token when reading DataTable. I read that it's beacuse JSON format is not as it should be. Mine is as follows:

{
  "page": 1,
  "page_size": 1000,
  "items": [
    {
      "id": "e1b019b9a8bf408c9cb964c29e845104",
      "asset_id": "5adb0d87882b4e14b99bde74a967e84c",
      "alias": "Concrete Pump Yellow",
      "serial_number": "QEQ000123",
      "model": {
        "name": "Pump C50-HP"
      },
      "operating_hours": {
        "hours": 100,
        "unit_driven": true
      }
   }
  ]
}

I know I need format like [{..}] but can't find workaround, API returns JSON as above. I can deserialize it using this:

var obj = JsonConvert.DeserializeObject(json);

but how can I now add data to DataTable? I'm looking for a solution for it

CodePudding user response：

What the JsonConvert class does is it materializes your string version of the response into an object. For this to work, your string version has to match the structure of the resulting object or the class needs hints to know how to inflate the object. The runtime is telling you that there is a mismatch and it doesn't know how to resolve it.

There are a few ways to get this done. I prefer an structured approach so I would recommend you create classes to receive the data:

var payload = @"{
                      ""page"": 1,
                      ""page_size"": 1000,
                      ""items"": [
                        {
                          ""id"": ""e1b019b9a8bf408c9cb964c29e845104"",
                          ""asset_id"": ""5adb0d87882b4e14b99bde74a967e84c"",
                          ""alias"": ""Concrete Pump Yellow"",
                          ""serial_number"": ""QEQ000123"",
                          ""model"": {
                            ""name"": ""Pump C50-HP""
                          },
                          ""operating_hours"": {
                            ""hours"": 100,
                            ""unit_driven"": true
                          }
                       }
                      ]
                    }";

public class ApiResponse
{
    [JsonProperty("page")]
    public int Page { get; set; }
    
    [JsonProperty("page_size")]
    public int PageSize { get; set; }

    [JsonProperty("items")]
    public IEnumerable<ApiResponseItem> Items { get; set; }

}

public class ApiResponseItem
{
    [JsonProperty("id")]        
    public string Id { get; set; }

    [JsonProperty("asset_id")]
    public string AssetId { get; set; }

    [JsonProperty("alias")]
    public string Alias { get; set; }

    [JsonProperty("serial_number")]
    public string SerialNumber { get; set; }

    [JsonProperty("model")]
    public ApiResponseModel Model { get; set; }

    [JsonProperty("operating_hours")]
    public ApiResponseOperatingHours OperatingHours { get; set; }
}

public class ApiResponseModel
{
    [JsonProperty("name")]
    public string Name { get; set; }
}

public class ApiResponseOperatingHours
{
    [JsonProperty("hours")]
    public string Hours { get; set; }
    
    [JsonProperty("unit_driven")]
    public bool UnitDriven { get; set; }
}

var response = JsonConvert.DeserializeObject<ApiResponse>(payload);

As you can see, the classes use hint attributes to let the deserializer know about the fields. You can then loop through the response.Items enumerable and consume the items as desired.

UPDATE:

For posterity and at the suggestion of @mason, it's important to point out that there is no need to use a DataTable. A quick inspection of the payload reveals the output is a paged version of set of records so it's not equivalent to a data table.

CodePudding user response：

Your issue here is that the json you're deserializing is not a DataTable, its just an Object.

JsonConvert.DeserializeObject(request, typeof(Object)) -> Where Object would be a defined Class with parameter definitions to deserialize the json to, i.e page, page_size, id etc..

Once in this format its fairly easy to coerce it into a DataTable: https://docs.microsoft.com/en-us/dotnet/api/system.data.datatable?view=net-6.0

The Classes would look something along the lines of:

public class Items
{
    public Guid? Id {get;set;}
    public Guid? AssetId {get;set;}
    public string alias {get;set;}
    public string serial_number {get;set;}
    public Model model {get;set;}
    public OperatingHours operatingHours {get;set;}
}
public class Model
{
    public string Name { get;set;}
}
public class OperatingHours
{
    public int Hours {get;set;}
    public bool Unit_Driven {get;set;}
}
public class OverallObject
{
    public int Page {get;set;}
    public int PageSize {get;set;}
    public List<Items> AllItems {get;set;}
}