How to deserialize JSON object to DataTable

Question

I have JSON data as below. I would like to know how to deserialize JSON object to DataTable. Please check the below JSON string and advise how to do this.

{
    "response":
    {
        "uri":"\/api\/junaid@khindia.com\/Gloves",
        "action":"EXPORT",
        "result":
        {
            "column_order":["AttendanceDate","DivCode","Division","DeptCode","Department","Section","CostType","Gender","Religion","EmpLevel","DegLevel","CurrentSection","EmployeeCode","EmployeeName","Strength","Presents","Absents","LayOffs","Weaving","Wallet","OutSide","PresentIn","PresentOut","AbsentIn","AbsentOut","LayoffIn","LayoffOut","Attendance","Working","LastUpDatedDate","Stage","AreaName","AreaDirection","AreaSerial","ImageURL","ShiftCode","ShiftTime","CurrentDepartment","CurrentDivision","C%_StaffOrWorker","C%_MajorDivision","M_Division","Worked_Hours","Worked_OtHours","Lateby","Earlyby","OverTime1","OverTime2","C%_OtherDivision","C%_ShiftFrom"],
            "rows":[
                ["05/05/2022 00:00:00","AM","Leather Goods","MC","Bags","Production","Direct","Female","Hindus","Worker","Upto X","Arind","12519","SANGEETHA. M","1","1","0","0","0","0","0","1","0","0","0","0","0","P","Gloves","05/05/2022 20:15:00","","Arappakam","A         ","-2","http:\/\/103.76.188.138:85\/EmployeePhotos\/12519.JPG","30","07:00 - 15:30","Bags","Bags","Worker","Bags","Leather Goods","6.28","2.5","103","0","150","0","Bags","2022-05-05 07:00:00"],
                ["05/05/2022 00:00:00","AM","Leather Goods","MB","Wallet","Production","Direct","Male","Muslims","Worker","Degree","Arind","12548","MD NAIMUDDEEN.R","1","1","0","0","0","0","0","1","0","0","0","0","0","P","Gloves","05/05/2022 20:15:00","","Kaspa","V         ","17","http:\/\/103.76.188.138:85\/EmployeePhotos\/12548.JPG","30","07:00 - 15:30","Wallet","Wallet","Worker","Wallet","Leather Goods","7.08","1.5","55","0","90","0","Wallet","2022-05-05 07:00:00"],
                ["05/05/2022 00:00:00","AM","Leather Goods","MB","Wallet","Production","Direct","Female","Hindus","Worker","Upto XII","Arind","12567","SWETHA.P","1","1","0","0","0","0","0","1","0","0","0","0","0","P","Gloves","05/05/2022 20:15:00","","Melakuppam","A         ","-4","http:\/\/103.76.188.138:85\/EmployeePhotos\/12567.JPG","30","07:00 - 15:30","Wallet","Wallet","Worker","Wallet","Leather Goods","7.13","1.5","52","0","90","0","Wallet","2022-05-05 07:00:00"],
                ["05/05/2022 00:00:00","AM","Leather Goods","MB","Wallet","Production","Direct","Male","Muslims","Worker","Upto V","Arind","12605","IRFAN AHMED.C","1","0","1","0","0","0","0","0","0","1","0","0","0","A","Gloves","05/05/2022 20:15:00","","Pernambet","V         ","0","http:\/\/103.76.188.138:85\/EmployeePhotos\/12605.JPG","30","07:00 - 15:30","Wallet","Wallet","Worker","Wallet","Leather Goods","0","0","0","0","0","0","Wallet","2022-05-05 07:00:00"],
                ["05/05/2022 00:00:00","AM","Leather Goods","MD","Belts","Edge Inking","Direct","Male","Hindus","Worker","Upto X","Arind","12633","SIVAKUMAR.S","1","0","1","0","0","0","0","0","0","1","0","0","0","A","Gloves","05/05/2022 20:15:00","","Perumugai","V         ","0","http:\/\/103.76.188.138:85\/EmployeePhotos\/12633.JPG","30","07:00 - 15:30","Belts","Belts","Worker","Belts","Leather Goods","0","0","0","0","0","0","Belts","2022-05-05 07:00:00"],
                ["05/05/2022 00:00:00","AF","Assembly","FA","Pre-Sewing","Applique Stitch","Direct","Male","Hindus","Worker","Upto X","Arind","12940","KULOTHUNGAN. G","1","1","0","0","0","0","0","1","0","0","0","0","0","P","Gloves","05/05/2022 20:15:00","PreSewing","Sathuvachari","V         ","6","http:\/\/103.76.188.138:85\/EmployeePhotos\/12940.JPG","32","08:00 - 16:30","Pre-Sewing","Gloves","Worker","Gloves","Gloves","8","0","0","0","0","0","Gloves","2022-05-05 08:00:00"],
                ["05/05/2022 00:00:00","AM","Leather Goods","MD","Belts","Preparation","Direct","Female","Hindus","Worker","Upto X","Arind","11830","ASWINI","1","1","0","0","0","0","0","1","0","0","0","0","0","P","Gloves","05/05/2022 20:15:00","","Pillaiyar Kuppam","A         ","-1","http:\/\/103.76.188.138:85\/EmployeePhotos\/11830.JPG","30","07:00 - 15:30","Belts","Belts","Worker","Belts","Leather Goods","8","0","0","0","0","0","Belts","2022-05-05 07:00:00"],
                ["05/05/2022 00:00:00","AP","Training","PA","Training","Training","Direct","Female","Hindus","Worker","Upto X","Arind","12957","NALINI.R","1","1","0","0","0","0","0","1","0","0","0","0","0","P","Gloves","05/05/2022 20:15:00","","Melvisharam","A         ","-9","http:\/\/103.76.188.138:85\/EmployeePhotos\/12957.JPG","30","07:00 - 15:30","Training","Training","Worker","Training","Training","7.1","1.5","54","0","90","0","Training","2022-05-05 07:00:00"],
                ["05/05/2022 00:00:00","AM","Leather Goods","MA","Weaving","Weaving","Direct","Female","Hindus","Worker","Upto XII","Arind","12818","HEMALATHA.R","1","1","0","0","0","0","0","1","0","0","0","0","0","P","Gloves","05/05/2022 20:15:00","","Melakuppam","A         ","-4","http:\/\/103.76.188.138:85\/EmployeePhotos\/12818.JPG","30","07:00 - 15:30","Weaving","Weaving","Worker","Weaving","Leather Goods","6.17","2.5","110","0","150","0","Weaving","2022-05-05 07:00:00"],
                ["05/05/2022 00:00:00","AM","Leather Goods","MD","Belts","Sample","Indirect","Female","Hindus","Worker","Upto X","Arind","11869","KALPANA.K","1","1","0","0","0","0","0","1","0","0","0","0","0","P","Gloves","05/05/2022 20:15:00","","Arappakam","A         ","-2","http:\/\/103.76.188.138:85\/EmployeePhotos\/11869.JPG","30","07:00 - 15:30","Belts","Belts","Worker","Belts","Leather Goods","8","0","0","0","0","0","Belts","2022-05-05 07:00:00"]
            ]
        }
    }
}

do you want to convert only `"rows"` json array to datatable? if not, better create `c#` object model — hamaronooo, Jun 07 '22 at 07:30
I would like to convert rows and column and bind it to Grid control (C#). — EDP, Jun 07 '22 at 07:37
anyway, you need to parse json to object first, then create DataTable — hamaronooo, Jun 07 '22 at 07:39
You can take a look at this post which is helpful in your case: https://stackoverflow.com/questions/11981282/convert-json-to-datatable — Gabriel Marius Popescu, Jun 07 '22 at 07:48

Yong Shun · Answer 1 · 2022-06-07T08:10:55.623

Deserialize json.
Get colNames array from response["response"]["result"]["column_order"].
Get rows array from response["response"]["result"]["rows"].
Create Datatable with configure the columns with colNames.
Add DataRow with rows based on colNames sequence.

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

JObject response = JObject.Parse(json);
JArray colNames = response["response"]["result"]["column_order"] as JArray;
JArray rows = response["response"]["result"]["rows"] as JArray;
        
DataTable dt = new DataTable();
        
foreach (var col in colNames)
{
    dt.Columns.Add(col.ToString(), typeof(string)); 
}
        
foreach (var row in rows)
{
    var newRow = dt.NewRow();
        
    for (int i = 0; i < colNames.Count; i++)
    {
        var col = colNames[i].ToString();
        newRow[col] = row[i];
    }
            
    dt.Rows.Add(newRow);
}

Sample .NET Fiddle Demo

You may also apply the convert to Datatable logic as an extension/helper method if you also handle the data with a similar structure:

JObject response = JObject.Parse(json);
List<string> colNames = (response["response"]["result"]["column_order"] as JArray).ToObject<List<string>>();
List<List<string>> rows = (response["response"]["result"]["rows"] as JArray).ToObject<List<List<string>>>();
        
DataTable dt = DataTableExtensions.ToDataTable(colNames, rows);

public static class DataTableExtensions
{
    public static DataTable ToDataTable(List<string> colNames, List<List<string>> rows)
    {
        DataTable dt = new DataTable();
        
        foreach (var col in colNames)
        {
            dt.Columns.Add(col, typeof(string));    
        }
        
        foreach (var row in rows)
        {
            var newRow = dt.NewRow();
            
            for (int i = 0; i < colNames.Count; i++)
            {
                var col = colNames[i];
                newRow[col] = row[i];
            }
            
            dt.Rows.Add(newRow);
        }
        
        return dt;
    }
}

Sample .NET Fiddle Demo (As extension/helper)

score 0 · Accepted Answer · answered Jun 07 '22 at 07:44

Construct an object model using one of the countless conversion utilities or even the capability to paste JSON as a class that is built into Visual Studio. (Have you even tried to do this?)

    public class Response
    {
        public string uri { get; set; }
        public string action { get; set; }
        public Result result { get; set; }
    }

    public class Result
    {
        public List<string> column_order { get; set; }
        public List<List<string>> rows { get; set; }
    }

    public class Root
    {
        public Response response { get; set; }
    }

Then parse the data. Assuming the JSON is in a string this is trivial.

var response = System.Text.Json.JsonSeralizer.Deserialize<Root>(jsonString).Response;

Then iterate the values of response.Result and populate your data table object. Since you haven't seen fit to provide details of that you can't have details for that part of the answer.

Incidentally, your JSON is invalid, it's missing a trailing brace.

score 0 · Answer 3 · answered Jun 07 '22 at 08:06

Your object is:

public class Response
{
    public string uri { get; set; }
    public string action { get; set; }
    public Result result { get; set; }
}

public class Result
{
    public List<string> column_order { get; set; }
    public List<List<string>> rows { get; set; }
}

public class Root
{
    public Response response { get; set; }
}

Then use Newtonsoft.Json to parse it like:

Root root = JsonConvert.DeserializeObject<Root>(jsonString);

Then create DataTable:

DataTable dt = new DataTable(); 
dt.Clear();

foreach(var column in root.response.result.column_order) {
    dt.Columns.Add(column);
}

foreach(var jsonRow in root.response.result.rows) {
    for(int i =0; i < jsonRow.Length; i++) {
         DataRow row = dt.NewRow();
         row[i] = jsonRow[i];
         dt.Rows.Add(row);
    }
}

How to deserialize JSON object to DataTable

3 Answers3