ASP.Net/C# - How to store and retrive data in SQL in Binary tree structure

Help me out...planning for leave right after getting this code done....

I am working on Multiple level marketing project.......

i have a data base saved as binary tree....
Eg-:
There are three rows...
1.username
2.user_id.
3.Ref_id.

user_id is primary key, unique for each member and generated randomly using C#.
to join this organization, member has to provide user_id of prvious member which will be Ref_id for new member. Each member can appoint two other members. This means there should be only 2 same Ref_id...in this way Binary tree is formed...

Now i want to retrive a SUB TREE of Binary Tree...i.e all the members below any member till 12th level...with there username and user_id..and display them using C# in ASP.NET.

Check out msdn [dot] microsoft [dot] com/en-us/vcsharp/aa336800.aspx for some good articles on data structures, including binary trees.

Btw, I'm a new poster so I couldn't put that url in normally....

I know the algorithms of all...
but the motive over here is to how we can create Database so that we can retrive this Binary tree eaisly in our application....
It might be possible by using some recurssive queries in SQL or by using Array's and While loop in programming.

This is just like Multi level marketing....

OK, in which case you should probably have a read up on nested sets. These work by using "left" and "right" columns which effectively refer to the child items that are contained by the parent item. This means you can start at any point in the tree and retrieve all child items for instance. There are plenty of database centred articles on nested sets around. You could also look into recursive queries (check out msdn [dot] microsoft [dot] com/en-us/library/ms186243.aspx for sql 2005) but as the name suggests these are queries running recursively so won't be as efficient.

You are right i am not going to use any recurssive query.....
I have used TREEVIEW to display binary tree to the end user's. Its simple and easy to implement...But before this i had done lots of R&D...finally success.

Now next milestone for me is to calculate the income of each node according to the child nodes. Appreciate you if you help me on this.
If you need details about the income format then i can provide you.

Thanks,
Saif.

OK, well I'd come back to nested sets again! If you have a nested set table design, this sort of thing is really simple. For instance you can pull back all child nodes for a parent node with one select:

SELECT N1.<columns>
FROM NestedSetTable N1 INNER JOIN NestedSetTable N2
ON N1.Left > N2.Left AND N1.Right < N2.Right
WHERE N2.Id = @ParentId

Or you can pull back aggregate info in a similar way:

SELECT N1.Id, SUM(N1.SomeAmount)
FROM NestedSetTable N1 INNER JOIN NestedSetTable N2
ON N1.Left > N2.Left AND N1.Right < N2.Right
WHERE N2.Id = @ParentId
GROUP BY N1.Id

The latter would sum the column 'SomeAmount' for each child under the parent with Id = @ParentId.

I appreciate your response but as per my client there is different plan for the income...It is like MLM(Multi Level Marketing) which forms Binary tree. I have to check for the pairs...if there is pair then income generated else not.
Here is a link, you will get the plan for the income from my client.

http://aimsonline.org/aims.html

Do i have to maintain the level and pairs for each members.......
I have used treeview control to display binary tree...
Here ID is 16-digit alphanumeric and it is almost impossible to know whose id is greater.

I am sure u can help me in this.
Till then Happy weekend.
Saif.

Without something in your data structure to help you I don't see how you can do this without doing something recursive. You have to check for the existence of a given UserId in the RefId column and if you find a pair, you then have to check those pairs for pairs in the same way and so on. Because your tree can be as many levels as you like I don't think you can do this in one operation. If you're not going to change your data structure I think you have to look at recursive queries.

You mention that you've loaded data into a treeview, while the treeview isn't relevant it does suggest you've already loaded the data into your application in a usable form. You also mentioned that you knew the algorithms so presumably you're storing your data in a binary tree structure in your application? In which case the method would be to traverse your tree for a given node to get it's child node pairs and calculate the income in this way. There are details about traversing data structures in the articles I posted above.

HTH

Yes, you are right. I have to work on datastructure for this...
Hope you read the payment plan.
Here i have to maintain the level as well as pairs of child for each parent node... i will let u know about the progress...and if stuck then contact u again....
Thanks.
Take care n keep smiling.

No problem, hope it goes well!

Do you know how to count No's of nodes in each level of treeview??

Example:
Root Node
-----ParentA1 Node
--------ParentB1 Node
-------------childB1 Nodes
-------------childB2 Nodes
--------ParentB2 Node
-----ParentA2 Node
-------ChildA1 Node
-------ChildA2 Node

Root Node is on Level-0, (No's of node= 1)
ParentA1 and parentA2 in level-1 (No's of node = 2)
ParentB1 and B2 and childA1 and A2 nodes are in level-2 (No's of node = 4).