Convert JSON response from REST API into DataTable

Question

I am trying to convert a json response from REST API into a DataTable. The following is my code.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using RestSharp;
using Newtonsoft.Json;
using System.Data;

namespace RestDemo1
{
    class Program
    {
        static void Main(string[] args)
        {
            getEmployeeData();
        }

        public static void getEmployeeData()
        {
            var client = new RestClient("https://crmscf.vidyasystems.com/api/gen/items.php");
            var request = new RestRequest("items");
            var response = client.Execute(request);
            string vJ = "";

            if (response.StatusCode == System.Net.HttpStatusCode.OK)
            {
                string rawResponse = response.Content;
                DataTable tester = (DataTable)JsonConvert.DeserializeObject(rawResponse, (typeof(DataTable)));
            }
        }

        public class Rootobject
        {
            public int success { get; set; }
            public Item[] items { get; set; }

            public class Item
            {
                public string ItemID { get; set; }
                public string ItemName { get; set; }
                public string ItemDesc { get; set; }
                public string MRP { get; set; }
                public string Rate { get; set; }
                public string Unit { get; set; }
                public string Weight { get; set; }
                public string ItemGroup { get; set; }
            }
        }
    }
}

When I try get the data into a table format, I get error:

DataTable tester = (DataTable)JsonConvert.DeserializeObject(rawResponse, (typeof(DataTable)));

Error message- Newtonsoft.Json.JsonSerializationException: 'Unexpected JSON token when reading DataTable. Expected StartArray, got StartObject. Path '', line 1, position 1.'

Answer 1

Sorry for the earlier answer, I think this will solve your issue:

public static async Task Main(string[] args)
{
    using(HttpClient client = new())
    {
        var response = await client.GetStringAsync("https://crmscf.vidyasystems.com/api/gen/items.php");
        JObject jsonObject = JObject.Parse(response);
        var items = JsonConvert.SerializeObject(jsonObject["items"]);
        DataTable tester = (DataTable)JsonConvert.DeserializeObject(items, (typeof(DataTable)));
    }
    Console.WriteLine("Hello, World!");
}

Answer 2

Your response is a Rootobject type, but not a DataTable type. Hence, you can't deserialize the response.Content to DataTable type.

What you need to do:

1. Deserialize response.Content to the Rootobject instance.

2. Extract the items (a list with List<Rootobject.Item> type) from the Rootobject instance.

3. Convert the List<Rootobject.Item> to DataTable via the extension method IListExtensions.ToDataTable<T>(this IList<T> list).

string rawResponse = response.Content;
                
var root = JsonConvert.DeserializeObject<Rootobject>(rawResponse);
                
DataTable dt = root.items.ToDataTable<Rootobject.Item>();

using System.Collections.Generic;
using System.ComponentModel;
using System.Data;

public static class IListExtensions
{
    public static DataTable ToDataTable<T>(this IList<T> list)
    {
        PropertyDescriptorCollection properties = TypeDescriptor.GetProperties(typeof(T));
        DataTable dataTable = new DataTable();

        foreach (PropertyDescriptor property in properties)
            dataTable.Columns.Add(property.Name, Nullable.GetUnderlyingType(property.PropertyType) ?? property.PropertyType);

        if (list == null)
            return dataTable;

        foreach (T item in list)
        {
            DataRow row = dataTable.NewRow();
            foreach (PropertyDescriptor property in properties)
                row[property.Name] = property.GetValue(item) ?? DBNull.Value;

            dataTable.Rows.Add(row);
        }

        return dataTable;
    }
}