By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,296 Members | 1,564 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,296 IT Pros & Developers. It's quick & easy.

C# to SQL Recursive CTE Translation help

P: 1
I wrote an algorithm to get all the relatives of a person in a family tree. I'm basically getting all the users from the DB and am doing the recursive logic in code, so that there is only 1 call made to the DB. However, I am trying to do the same thing within a stored procedure in SQL using recursive CTEs (I think the performance might be better) but I'm finding it really tough to craft the CTE. I would really appreciate if someone could translate the following code to a recursive CTE in SQL. I tried to write the SP myself but I am not getting the right results. I tried to write the sql to get me just the ancestors to start off with, but even that is not coming out right. Here's the (incorrect) SP, and just below that is the C# code that works correctly:

// INCORRECT SQL CODE:

Expand|Select|Wrap|Line Numbers
  1. ALTER PROCEDURE [dbo].[spGetFamilyMembersForUser] 
  2.     -- Add the parameters for the stored procedure here
  3.     @UserName varchar(50)
  4. AS
  5. BEGIN
  6.  
  7. declare @familyid int
  8. set @familyid = (select AsChildFamilyID from Users where UserName = @UserName);
  9.  
  10.     WITH Ancestors(UserName, Gender, AsChildFamilyID, AsSpouseFamilyID, AsParentFamilyID) AS
  11.     (
  12.        -- Base case (get father of user)       
  13.        SELECT u.UserName, u.Gender, u.AsChildFamilyID, u.AsSpouseFamilyID, u.AsParentFamilyID
  14.        FROM Users u
  15.        WHERE u.AsParentFamilyID = @familyid AND u.Gender = 'Male'
  16.  
  17.        UNION
  18.  
  19.         -- Base case (get mother of user)       
  20.        SELECT u.UserName, u.Gender, u.AsChildFamilyID, u.AsSpouseFamilyID, u.AsParentFamilyID
  21.        FROM Users u
  22.        WHERE u.AsParentFamilyID = @familyid AND u.Gender = 'Female'
  23.  
  24.  
  25.        UNION ALL
  26.  
  27.        -- Recursive step
  28.        SELECT u.UserName, u.Gender, u.AsChildFamilyID, u.AsSpouseFamilyID, u.AsParentFamilyID
  29.        FROM Users u
  30.        INNER JOIN Ancestors a ON a.AsChildFamilyID = u.AsParentFamilyID 
  31.          WHERE u.AsChildFamilyID <> -1 
  32.     )
  33.  
  34.     SELECT * FROM Ancestors
  35.     WHERE Ancestors.UserName <> @UserName 
  36.  
  37. END
  38.  
// C# CODE THAT WORKS

// ASSUMPTIONS
1. _dtAllUsers contains all users
2. _dtAllRelatives is initially empty, and is a clone of _dtAllUsers
3. The Users table has the following columns:

UserName, Gender, AsChildFamilyID, AsSpouseFamilyID, AsParentFamilyID

AsChildFamilyID means the family(ID) in which the user is a child. Similarly for AsSpouseFamilyID and AsParentFamilyID.
A value of -1 means that the user does not have that role in the family. For instance, if AsChildFamilyID is -1, then user is not a child in that family.

Example:
UserName, Gender, AsChildFamilyID, AsSpouseFamilyID, AsParentFamilyID

Joe, Male, 1, -1, -1
Jack, Male, 2, 1, 1
Jill, Female, -1, 1, 1
Jim, Male, -1, -1, 2

In this example, Jack and Jill are Joe's parents. Jim is Jack's father.

// POST-CONDITION
1. _dtAllRelatives contains all relatives for user

Expand|Select|Wrap|Line Numbers
  1. public void ComputeAllRelativesForUser(string userName)
  2.  {
  3.            DataTable dtUser = UserManager.GetUser(userName);
  4.  
  5.                 int asChildFamilyID = (int)dtUser.Rows[0]["AsChildFamilyID"];
  6.                 int asSpouseFamilyID = (int)dtUser.Rows[0]["AsSpouseFamilyID"];
  7.                 int asParentFamilyID = (int)dtUser.Rows[0]["AsParentFamilyID"];
  8.  
  9.                 string motherUserName = string.Empty; //the username of the user's mother
  10.                 string fatherUserName = string.Empty;
  11.                 string spouseUserName = string.Empty;
  12.                 string siblingUserName = string.Empty;
  13.                 string childUserName = string.Empty;
  14.  
  15.                 int fathersAsChildFamilyID = -1; //the AsChildFamilyID of user's father
  16.                 int mothersAsChildFamilyID = -1; 
  17.                 int spousesAsChildFamilyID = -1;
  18.                 int siblingsAsChildFamilyID = -1;
  19.                 int childsAsChildFamilyID = -1;           
  20.  
  21.                 foreach (DataRow row in _dtAllUsers.Rows)
  22.                 {
  23.                     //get Father
  24.                     if ((int)row["AsParentFamilyID"] == asChildFamilyID && asChildFamilyID != -1 && (string)row["Gender"] == "Male")
  25.                     {
  26.                         _dtAllRelatives.Rows.Add(row);
  27.                         fatherUserName = (string)row["UserName"];
  28.                         fathersAsChildFamilyID = (int)row["AsChildFamilyID"];                        
  29.                     }
  30.  
  31.                     //get Mother
  32.                     if ((int)row["AsParentFamilyID"] == asChildFamilyID && asChildFamilyID != -1 && (string)row["Gender"] == "Female")
  33.                     {
  34.                         _dtAllRelatives.Rows.Add(row);
  35.                         motherUserName = (string)row["UserName"];
  36.                         mothersAsChildFamilyID = (int)row["AsChildFamilyID"];                        
  37.                     }
  38.  
  39.                     //get Spouse
  40.                     if ((int)row["AsSpouseFamilyID"] == asSpouseFamilyID && asSpouseFamilyID != -1)
  41.                     {
  42.                         _dtAllRelatives.Rows.Add(row);
  43.                         spouseUserName = (string)row["UserName"];
  44.                         spousesAsChildFamilyID = (int)row["AsChildFamilyID"];                        
  45.                     }
  46.  
  47.                     //get Sibling
  48.                     if ((int)row["AsChildFamilyID"] == asChildFamilyID && asChildFamilyID != -1)
  49.                     {
  50.                         _dtAllRelatives.Rows.Add(row);
  51.                         siblingUserName = (string)row["UserName"];
  52.                         siblingsAsChildFamilyID = (int)row["AsChildFamilyID"];       
  53.                     }
  54.  
  55.                     //get Child
  56.                     if ((int)row["AsChildFamilyID"] == asParentFamilyID && asParentFamilyID != -1)
  57.                     {
  58.                         _dtAllRelatives.Rows.Add(row);
  59.                         childUserName = (string)row["UserName"];
  60.                         childsAsChildFamilyID = (int)row["AsChildFamilyID"];
  61.                     }
  62.  
  63.                     //recursive for father 
  64.                     if (fathersAsChildFamilyID != -1)
  65.                         ComputeAllRelativesForUser(fatherUserName);
  66.  
  67.                     //recursive for mother 
  68.                     if (mothersAsChildFamilyID != -1)
  69.                         ComputeAllRelativesForUser(motherUserName);
  70.  
  71.                     //recursive for spouse 
  72.                     if (spousesAsChildFamilyID != -1)
  73.                         ComputeAllRelativesForUser(spouseUserName);
  74.  
  75.                     //recursive for sibling 
  76.                     if (siblingsAsChildFamilyID != -1)
  77.                         ComputeAllRelativesForUser(siblingUserName);
  78.  
  79.                     //recursive for child
  80.                     if (childsAsChildFamilyID != -1)     
  81.                         ComputeAllRelativesForUser(childUserName);
  82.                 }
  83.         }
Mar 19 '07 #1
Share this question for a faster answer!
Share on Google+

Post your reply

Sign in to post your reply or Sign up for a free account.