473,378 Members | 1,544 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,378 software developers and data experts.

C# to SQL Recursive CTE Translation help

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
0 1939

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

Similar topics

10
by: Steve Goldman | last post by:
Hi, I am trying to come up with a way to develop all n-length permutations of a given list of values. The short function below seems to work, but I can't help thinking there's a better way. ...
2
by: lugal | last post by:
I'm new to C++, coming from a Python background. I wrote the following code in C++ based on the Python code found here: http://aspn.activestate.com/ASPN/Cookbook/Python/Recipe/302478 ...
2
by: | last post by:
OK: Purpose: Using user's input and 3 recursive functions, construct an hour glass figure. Main can only have user input, loops and function calls. Recursive function 1 takes input and displays...
7
by: Steven T. Hatton | last post by:
Is there anything that gives a good description of how source code is converted into a translation unit, then object code, and then linked. I'm particularly interested in understanding why putting...
7
by: Jon Slaughter | last post by:
#pragma once #include <vector> class empty_class { }; template <int _I, int _J, class _element, class _property> class RDES_T {
12
by: Mikito Harakiri | last post by:
I wonder if WITH RECURSIVE MaryAncestor(anc,desc) AS ( (SELECT parent as anc, child as desc FROM ParentOf WHERE desc = "Mary") UNION (SELECT A1.anc, A2.desc FROM MaryAncestor A1, MaryAncestor...
64
by: dmattis | last post by:
I am trying to write a recursive version of Power(x,n) that works by breaking n down into halves(where half of n=n/2), squaring Power(x,n/2), and multiplying by x again if n was odd, and to find a...
14
by: BQ | last post by:
Due to a lack of resources, I have to translate the following recursive function in its iterative form. It's a kind of dichotomic search. void SearchSlaves(unsigned long start_uid, unsigned long...
41
by: Harry | last post by:
Hi all, 1)I need your help to solve a problem. I have a function whose prototype is int reclen(char *) This function has to find the length of the string passed to it.But the conditions...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.