Click here to Skip to main content
15,359,445 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
Goal: create an Web API which returns below format JSON object.
{ 
 "parent": 
    {
      "name": "Doe",
      "empno": "324"
    },
"child": 
    {
      "name": "krishna",
      "empno": "789"
    }
 }


(sp_GetOrgTree) stored procedure which returns two tables (parentTable, childTable).

how to get these two tables in webapi project using ADO.Net and how to return this as nested JSON object.

What I have tried:

public static DataTable GetOrgTree(int emp_id)
{
do ado.net stuff
return table
}
Posted
Updated 28-Oct-20 7:08am
Comments
Richard MacCutchan 28-Oct-20 13:06pm
   
Great, I guess it is working correctly then?
F-ES Sitecore 28-Oct-20 13:07pm
   
Without knowing what data your sp returns it is impossible to say. Is it literally two tables? like

select * from parent
select * from child

or are the two tables joined into a single result set? Regardless the question is too broad and it just reads like you want us to do your work for you. Google how to convert data from a stored procedure into json, or you might have to read the results into your own class objects and just convert that to json.
   
two separate tables, i was stuck hence posted here. thanks for ur time

1 solution

Why on earth would you want to use an SQL SP to play with JSON in C#?

Just install Json.NET - Newtonsoft[^] and import the classes directly to your C# code!

If you want to retrieve table values from SQL and return it to the API caller, then fetch it from SQL as normal, convert it into classes and use Json.NET to generate the JSON code by serializing them.
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900