CODEWITHSUNDEEP
c#sqlasp.net-mvcasp.net-web-apiwebapi
Virendra
Virendra

Reputation: 61

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

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

Upvotes: 0

Views: 1496

Answers (1)

Md Rahatur Rahman
Md Rahatur Rahman

Reputation: 3244

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.

Upvotes: 1

Related Questions