How to fetch sql data and covert to jquery object?

r p

13y1.8k.NET

I have the hierarchy of an organization in the SQL database. It looks like this -

Main Manager

Manager - 1

Manager - 1.1

Manager - 1.1.1

Manager - 2

Manager - 2.1

Manager - 2.1.1

Manager - 2.2

etc.

I want to fetch the values in the database and convert to a jquery object just like the example below.

How can I fetch the sql data in the database in hierarchical order and create a jquery object like in the example below?

var data = { Name: "Manager - 1",

ID: "1",

children: [ {

Name: "Manager - 1.1",

ID: "1.1",

children: [ {

Name: "Manager - 1.1.1",

ID: "1.1.1"}

]

Name: "Manager - 1.2",

ID: "1.2",

children: [ {

Name: "Manager - 1.2.1",

ID: "1"}

]

}

]

};

7 Replies

Know the answer? Post it — somebody with the same question will find it here.

Jaganathan Bantheswaran13y ago
Hi,

Follow the link for detailed article on EF Navigation Properties with source code
http://weblogs.asp.net/manavi/archive/2011/05/17/associations-in-ef-4-1-code-first-part-6-many-valued-associations.aspx
0
r p13y ago
thanks Jaganathan,
Do you have any example?
Thanks,
Robins
0
Jaganathan Bantheswaran13y ago
I suggest you to use EF. Bcoz in EF, if you access the Table, if it has one to many relationship, then the table collection will have all the data related to one-many replationship. Then you can easily convert the table collection to json string using Json.Net.
0
r p13y ago
I can use either Entity Framework or the traditional way.
I mean I want the best and quickest way.
0
Jaganathan Bantheswaran13y ago
How are you accessing the DB? do you have any ORM or direct access to DB using SQL classes like SqlConnection....
0
r p13y ago
I am collecting the data from the database using stored procedure.
It has fields like - ID, ParentID and Name
0
Jaganathan Bantheswaran13y ago
How is your Table structure?
0