C# code to retrieve hierarchy data from table

Question

1.00/5 (1 vote)

See more:

Hi i am trying to write a code to retrieve data from the table, referencing each other from Parent to Child (as hierarchy)

Suppose i want to retrieve data of ParentId "3" then it will return ChildId "4".

Now here again the loop will run to retrieve ChildId where the ParentId is "4" and will return ChildId "5" and so on

The final result will be displayed as:
3 - 4
4 - 5
5 - NULL

Thanks in advance for solving my problem.
Regards

What I have tried:

SQL

create table [dbo].[Table1](ParentId int primary key, ChildId int)

insert into Table1 values (1, 2)
insert into Table1 values (2, 3)
insert into Table1 values (3, 4)
insert into Table1 values (4, 5)
insert into Table1 values (5, NULL)

Table Data:
ParentId   ChildId
1              2
2              3
3              4
4              5
5              NULL

C#

String Cid = "1";  //The parent id from where the loop will start
nCon.Open();
SqlCommand gCmd = new SqlCommand("SELECT ParentId, ChildId FROM Table1 WHERE ParentId = @Childid", nCon);
gCmd.Parameters.AddWithValue("@Childid", Cid);

SqlDataAdapter ds = new SqlDataAdapter(gCmd);
DataTable Dt = new DataTable();
ds.Fill(Dt);

for (int i = 0; i < Dt.Rows.Count; i++)
{
    Response.Write(Dt.Rows[i]["ParentId"].ToString() + 
                   Dt.Rows[i]["ChildId"].ToString());

    Cid = Dt.Rows[i]["ChildId"].ToString();
}

Posted 4-Apr-18 3:32am

GeoFinex

Updated 4-Apr-18 9:02am

#realJSOP

v2

Add a Solution

Comments

F-ES Sitecore 4-Apr-18 9:49am

You probably need to JOIN table1 on itself

select fields from table1 p join table1 c on c.ChildId = p.ParentID

something like that

CHill60 4-Apr-18 10:54am

Have a look at this example in my article Processing Loops in SQL Server - Traversing a hierarchy[^]

2 solutions

Solution 1

What you are looking for is called "Recursion"; and to do this, you will need to go through each ChildID and run the same SQL statement, again performing the same statement until you get no results.

I generally do this within my SQL Server utilizing a CTE based Stored Procedure to reduce the network chitchat and layers of objects within the calling application.
In eCommerce applications, this is what brings out the Category Trees...
And what I do for those is to have this procedure run off of a trigger and saved to a new table, and then I only need to do a simple SQL query as to not tax the server countless times when shopping is busy

Posted 4-Apr-18 4:08am

MadMyche

Comments

Maciej Los 5-Apr-18 2:57am

5ed!

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Wendelius · Accepted Answer · 2018-04-04T09:03:00

As far as I can see you can do the whole thing in a single, recursive query. Consider the following query using the test material you provided

SQL

WITH HQuery (ParentId, ChildId, Relation) AS (
-- anchor query
SELECT  t.ParentId,
        t.ChildId,
		CAST(t.ParentId AS varchar(100)) + ' - ' + CAST(t.ChildId AS varchar(100))
FROM    Table1 t
WHERE   t.ParentId = 3 -- starting point
UNION ALL
-- recursive portion
SELECT  t.ParentId,
        t.ChildId,
		CAST(t.ParentId AS varchar(100)) + ' - ' + COALESCE(CAST(t.ChildId AS varchar(100)), 'NULL')
FROM    Table1 t,
        HQuery hq
WHERE   t.ParentId = hq.ChildId
)
-- using query
SELECT * FROM HQuery

For more information, have a look at WITH common_table_expression (Transact-SQL) | Microsoft Docs[^]