I don't agree with the solution you have adopted. Contacting database from .NET code many times, is not a good idea for a small thing.
I will rather suggest that you should have written a stored procedure for this which accepts input as UserId and provides us all of this manager(s) until the root of manager's tree.
Have a look on below query that should help you...
DECLARE @UserId INT
DECLARE @ManagerCount INT
DECLARE @tblTemp TABLE(Id INT IDENTITY(1,1), UserId INT, UserName VARCHAR(50))
SET @UserId = 6
SET @ManagerCount = -1
WHILE(@UserId > 0)
INSERT INTO @tblTemp
SELECT UserId, [Name] FROM tblChainBusiness WHERE UserId = @UserId
SELECT @UserId = ISNULL(ParentId, 0) FROM tblChainBusiness WHERE UserId = @UserId
SET @ManagerCount = @ManagerCount + 1
SELECT * FROM @tblTemp ORDER BY Id DESC
SELECT @ManagerCount [Managers]
Consider you are having a table where UserId and his ParentId both are in the same table.
This query will give you output as highest manager on top and final child (User) on last row.
In addition I have taken an additional parameter for manager count which will help you to divide their commission in percentage. If you don't need that remove.
I have tested this query for a table which has 89000 rows and maximum 23 level of tree in relationship.
Give it a try, it is quick enough in execution. :)
www.dotnetguidance.com Jahi, if this helps please login to Mark As Answer. | Alert Moderator