[SOLVED] Saving JSON to DataTable

Issue

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

Solution

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;}
}

Answered By – RoryF

Answer Checked By – Mildred Charles (BugsFixing Admin)

Leave a Reply

Your email address will not be published. Required fields are marked *