how to get the nested JSON objects from SQL table in ASP.Net Web API

Question

I need to provide the below mentioned JSON output through asp.Net Web API. this output will be used to display the organization hierarchy(treeview) in angular.

I have employees data in SQL table as below:

EMP_NO  EMP_NAME  DESIGNATION  Manager_ID

Required JSON output:

 myobject= {
      "parent":[
        {
          "name":"radha Acharya",
          "designation":"General Counsel",
          "state":"",
          "active":false
        },
        {
          "name":"Rakesh mishra",
          "designation":"General Counse2",
          "state":"",
          "active":false
        },
        {
          "name":"Suri patil",
          "designation":"General Counse3",
          "state":"",
          "active":true
        }
      ],
      "childs":[
        {
          "name":"child 1",
          "designation":"Deputy General Counsel",
          "state":"",
          "active":false,
          "hasChilds":true
        },
        {
          "name":"child 2",
          "designation":"Deputy General Counsel",
          "state":"",
          "active":false,
          "hasChilds":true
        },
        {
          "name":"child 3",
          "designation":"Deputy General Counsel",
          "state":"",
          "active":false,
          "hasChilds":false
        },
        {
          "name":"child 4",
          "designation":"Deputy General Counsel",
          "state":"",
          "active":false,
          "hasChilds":true
        },
        {
          "name":"child 5",
          "designation":"Deputy General Counsel",
          "state":"Resigned",
          "active":false,
          "hasChilds":true
        },
        {
          "name":"child 6",
          "designation":"Deputy General Counsel",
          "state":"",
          "active":false,
          "hasChilds":true
        }
      ]
    }

in angular it will be displaying like this Front end display image

How are you going to determine who is whose child from that JSON output? — E. Shcherbo
– E. Shcherbo, Commented Oct 4, 2020 at 10:12

Md Rahatur Rahman · Accepted Answer · 2020-10-04 10:31:39Z

Not sure how you plan to use the Json as there is no connection between the parent list and child list. But here is how you can have the JSON from SQL Server:

#Create Classes of type parent and child

#Create an object called myobject with the two array/list properties - parent and childs

#Query SQL DB and populate the myobject object

#Transform the myobject object into JSON and send from API action

public class parent
{
    public string name { get; set; } 
    public string designation { get; set; } 
    public string state { get; set; } 
    public bool active { get; set; }
}

public class child
{
    public string name { get; set; } 
    public string designation { get; set; } 
    public string state { get; set; } 
    public bool active { get; set; } 
    public bool hasChilds { get; set; }
}

public class myboject
{
    public List<Parent> parent { get; set; } 
    public List<Child> childs { get; set; }
}

myboject myboj = new myboject();
myboj.parent = new List<Parent>();
myboj.child = new List<Child>();

//Query your sql server database and populate the myboj.parent and myboj.child with proper data

Then do the Json transformation:

return Newtonsoft.Json.JsonConvert.SerializeObject(myboj);

That will transform the myObj into a json object and return.

Thanks Rahatur, now I have got an idea about this implementation, I appreciate your efforts.
@Virendra Glad that it helped. hope you dont mind accepting the answer and also upvoting!

Collectives™ on Stack Overflow

how to get the nested JSON objects from SQL table in ASP.Net Web API

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related