Json Array convert to Data table

Feroz Khan

2y1.2kC#

Dear All, i am using api and there is data coming in Json i need to convert into Datatable and i created public class as well and trying to desrilized but not working. please need your support.

below my code and json format.

var response = await client.SendAsync(request);
response.EnsureSuccessStatusCode();

string responseData = await response.Content.ReadAsStringAsync();
var data = JsonConvert.DeserializeObject(responseData);

public class Branch
{
public string id { get; set; }
public Name name { get; set; }
}
// private DateTime date = "2023-10-23";
public class Datum
{
public string id { get; set; }
public string type { get; set; }
public string origin { get; set; }
public string documentNumber { get; set; }
public DateTime documentDate { get; set; }
public string currency { get; set; }
public Supplier supplier { get; set; }
public Retailer retailer { get; set; }
public Location location { get; set; }
public Branch branch { get; set; }
public List items { get; set; }
public Totals totals { get; set; }
}

public class Item
{
public ItemCategory itemCategory { get; set; }
public double lineAmount { get; set; }
public int discount { get; set; }
public double subTotal { get; set; }
public int taxRate { get; set; }
public string taxCode { get; set; }
public double taxAmount { get; set; }
public double total { get; set; }
}

public class ItemCategory
{
public string id { get; set; }
public Name name { get; set; }
}

public class Location
{
public string id { get; set; }
public Name name { get; set; }
}

public class Metadata
{
public int count { get; set; }
public int total { get; set; }
}

public class Name
{
public string en { get; set; }
public object ar { get; set; }
}

public class Retailer
{
public string id { get; set; }
public Name name { get; set; }
}

public class Root
{
public List data { get; set; }
public Metadata metadata { get; set; }
}

public class Supplier
{
public string id { get; set; }
public Name name { get; set; }
}

public class Totals
{
public double taxAmount { get; set; }
public double subTotal { get; set; }
public double total { get; set; }
}

Json file

{

"data": [

{

"id": "653f7b98175871ae0da687b0",

"type": "invoice",

"origin": "purchase",

"documentNumber": "2023/10/00218",

"documentDate": "2023-10-28T14:00:06.805Z",

"currency": "SAR",

"supplier": {

"id": "6537a057fc7d89c5400713ef",

"name": {

"en": "Supplier Name"

}

"retailer": {

"id": "64e4527cc78560ea7f3bb2b8",

"name": {

"en": "Company Name"

}

"location": {

"id": "64e4527cc78560ea7f3bb2c1",

"name": {

"en": "Food"

}

"branch": {

"id": "64e4527cc78560ea7f3bb2ba",

"name": {

"en": "ABC",

"ar": null

}

"items": [

{

"itemCategory": {

"id": "64e477e4b5958a0428e41440",

"name": {

"en": "Meat",

"ar": null

}

"lineAmount": 684.552,

"discount": 0,

"subTotal": 684.552,

"taxRate": 15,

"taxCode": "VAT15",

"taxAmount": 102.6828,

"total": 787.2348000000001

}

"totals": {

"taxAmount": 102.6828,

"subTotal": 684.552,

"total": 787.2348000000001

}

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Amit Mohanty2y ago

Accepted answer

It seems a bit ambiguous which data you intend to convert to a datatable. To address this, consider reviewing the following code:

var root = JsonConvert.DeserializeObject(jsonData);
foreach (var datum in root.data)
{
    Console.WriteLine($"ID: {datum.id}");
    Console.WriteLine($"Type: {datum.type}");
    // ... other properties

    // Accessing supplier information
    Console.WriteLine($"Supplier ID: {datum.supplier.id}");
    Console.WriteLine($"Supplier Name: {datum.supplier.name.en}");

    // Accessing item information
    foreach (var item in datum.items)
    {
        Console.WriteLine($"Item Category ID: {item.itemCategory.id}");
        Console.WriteLine($"Item Category Name: {item.itemCategory.name.en}");
        // ... other item properties
    }

    // Accessing totals information
    Console.WriteLine($"Tax Amount: {datum.totals.taxAmount}");
    Console.WriteLine($"SubTotal: {datum.totals.subTotal}");
    Console.WriteLine($"Total: {datum.totals.total}");
}

Amit Mohanty2y ago
Just remove the Utility. In my code Utility is the class name where i have declared the ConvertToDataTable method
Feroz Khan2y ago
Dear Amir,

Thank you for your quick reply. Now i am getting this error.

Amit Mohanty2y ago

public static DataTable ConvertToDataTable(List dataList)
{
    DataTable dataTable = new DataTable(typeof(T).Name);
    PropertyInfo[] properties = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
    foreach (PropertyInfo prop in properties)
    {
        dataTable.Columns.Add(prop.Name, prop.PropertyType);
    }
    foreach (T data in dataList)
    {
        DataRow row = dataTable.NewRow();
        foreach (PropertyInfo prop in properties)
        {
            row[prop.Name] = prop.GetValue(data, null);
        }
        dataTable.Rows.Add(row);
    }
    return dataTable;
}

You can use the above method to convert your data to datatable, like:

var dataTable = ConvertToDataTable(root.data);
foreach (var datum in root.data)
{
    var dataTable1 = ConvertToDataTable(datum.items);

    List totals = new List();
    totals.Add(datum.totals);
    var dataTable2 = ConvertToDataTable(totals);
}

Remember you have to adjust the class and its properties based on your actual scenario.

Feroz Khan2y ago
Dear Amit,

I tried your code i can see the data all the field but just one question to convert into datatable i will create datatable and i will create all the columns then i will assing the value as per columns right is it correct?

Jignesh Kumar2y ago

Hello Feroz Khan,

Please refer below one,

using Newtonsoft.Json;
using System;
using System.Data;

class Program
{
    static void Main()
    {
        string jsonData = @"
        {
            ""data"": [
                {
                    ""id"": ""653f7b98175871ae0da687b0"",
                    ""type"": ""invoice"",
                    ""origin"": ""purchase"",
                    ""documentNumber"": ""2023/10/00218"",
                    ""documentDate"": ""2023-10-28T14:00:06.805Z"",
                    ""currency"": ""SAR"",
                    ""supplier"": {
                        ""id"": ""6537a057fc7d89c5400713ef"",
                        ""name"": {
                            ""en"": ""Supplier Name""
                        }
                    },
                    ""retailer"": {
                        ""id"": ""64e4527cc78560ea7f3bb2b8"",
                        ""name"": {
                            ""en"": ""Company Name""
                        }
                    },
                    ""location"": {
                        ""id"": ""64e4527cc78560ea7f3bb2c1"",
                        ""name"": {
                            ""en"": ""Food""
                        }
                    },
                    ""branch"": {
                        ""id"": ""64e4527cc78560ea7f3bb2ba"",
                        ""name"": {
                            ""en"": ""ABC"",
                            ""ar"": null
                        }
                    },
                    ""items"": [
                        {
                            ""itemCategory"": {
                                ""id"": ""64e477e4b5958a0428e41440"",
                                ""name"": {
                                    ""en"": ""Meat"",
                                    ""ar"": null
                                }
                            },
                            ""lineAmount"": 684.552,
                            ""discount"": 0,
                            ""subTotal"": 684.552,
                            ""taxRate"": 15,
                            ""taxCode"": ""VAT15"",
                            ""taxAmount"": 102.6828,
                            ""total"": 787.2348000000001
                        }
                    ],
                    ""totals"": {
                        ""taxAmount"": 102.6828,
                        ""subTotal"": 684.552,
                        ""total"": 787.2348000000001
                    }
                }
            ]
        }";

        // Parse the JSON string to a JObject
        var jsonObject = JsonConvert.DeserializeObject(jsonData);

        // Access the 'data' array
        var data = jsonObject["data"];

        // Create a DataTable
        DataTable dataTable = new DataTable();

        // Add columns to the DataTable based on the JSON structure
        if (data.HasValues)
        {
            var firstData = data[0];
            AddColumnsToDataTable(dataTable, firstData);
            
            // Add rows to the DataTable
            foreach (var item in data)
            {
                AddRowToDataTable(dataTable, item);
            }
        }

        // Use the DataTable as needed
        // For example, print the DataTable content
        foreach (DataRow row in dataTable.Rows)
        {
            foreach (DataColumn col in dataTable.Columns)
            {
                Console.WriteLine($"{col.ColumnName}: {row[col]}");
            }
            Console.WriteLine();
        }
    }

    static void AddColumnsToDataTable(DataTable dataTable, dynamic jsonObject)
    {
        foreach (var property in jsonObject)
        {
            if (property.Value is JObject)
            {
                var innerObject = (JObject)property.Value;
                foreach (var innerProperty in innerObject)
                {
                    if (!dataTable.Columns.Contains(innerProperty.Key))
                    {
                        dataTable.Columns.Add(innerProperty.Key, typeof(string));
                    }
                }
            }
            else
            {
                if (!dataTable.Columns.Contains(property.Key))
                {
                    dataTable.Columns.Add(property.Key, typeof(string));
                }
            }
        }
    }

    static void AddRowToDataTable(DataTable dataTable, dynamic jsonObject)
    {
        DataRow newRow = dataTable.NewRow();

        foreach (var property in jsonObject)
        {
            if (property.Value is JObject)
            {
                var innerObject = (JObject)property.Value;
                foreach (var innerProperty in innerObject)
                {
                    newRow[innerProperty.Key] = innerProperty.Value.ToString();
                }
            }
            else
            {
                newRow[property.Key] = property.Value.ToString();
            }
        }

        dataTable.Rows.Add(newRow);
    }
}