How to deserialize JSON object to DataTable-CodePudding

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\/[email protected]\/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"]
            ]
        }
    }
}

CodePudding user response：

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)

CodePudding user response：

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.

CodePudding user response：

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