My sql procedure gives the below output.
If you look into the table id 1,2,5 and 6 has childs 0r 2,3,6,7 has parents. Corresponding parent id is given in parentid column.
Now in C#.Net I have to convert this table into nested json. That is child element inside parent element like below.
How to do it. Please help thanks
This should help get you started at least, but you'll need SQL Server 2012. You didn't specify the version you're using.
declare @t table (id int, name varchar(50), value varchar(50), HasParent varchar(3), ParentID int, HasChild varchar(3))
insert into @t
exec dbo.YourStoredProc
select
id
, name
, value
, HasChild 'attributes.HasChild'
, HasParent 'attributes.HasParent'
, ParentID 'attributes.ParentId'
, child.id 'children.id'
, child.name 'children.name'
, child.value 'children.value'
, child.haschild 'children.attributes.haschild'
, child.hasparent 'children.attributes.hasparent'
, child.parentid 'children.attributes.parentid'
, gchild.id 'children.children.id'
, gchild.name 'children.children.name'
, gchild.value 'children.children.value'
, gchild.haschild 'children.children.attributes.haschild'
, gchild.hasparent 'children.children.attributes.hasparent'
, gchild.parentid 'children.children.attributes.parentid'
from @t t
join @t child on child.ParentID = t.id
join @t gchild on gchild.ParentID = child.id
for json auto
You could just create an object that has properties corresponding to the properties in your table, then serialize it into JSON.
If I had to take a stab at what those classes would be, probably something like:
public class Person
{
public int Id {get; set;}
public string Name {get; set;}
public string Value {get; set;}
public PersonAttributes attributes {get; set;}
public List<Person> Children {get; set;}
}
public class PersonAttributes
{
public bool HasChild {get; set;}
public bool HasParent {get; set;}
public int ParentId {get; set;}
}
Then you could code your constructor to take the DataTable to sort out and assign the data as you need.
For the serialization, I recommend JSON.net, I use it all the time and it has never failed me.
