473,881 Members | 1,694 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

C# to SQL Recursive CTE Translation help

1 New Member
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 , AsSpouseFamilyI D, AsParentFamilyI D

AsChildFamilyID means the family(ID) in which the user is a child. Similarly for AsSpouseFamilyI D and AsParentFamilyI D.
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 , AsSpouseFamilyI D, AsParentFamilyI D

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 1967

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

Similar topics

10
5694
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. Not being a computer scientist, I find recursive functions to be frightening and unnatural. I'd appreciate if anyone can tell me the pythonic idiom to accomplish this. Thanks for your help,
2
1568
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 //beginning #include <vector> #include <iostream.h> using namespace std;
2
2895
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 a sequence of spaces; recursive function 2 uses input to display ascending sequence of digits; likewise, recursive function 3 uses input to display descending sequence of digits. I have not followed the instructions completely regarding the...
7
5132
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 normal functions in header files results in multiple definition errors even when include guards are used. -- STH Hatton's Law: "There is only One inviolable Law" KDevelop: http://www.kdevelop.org SuSE: http://www.suse.com Mozilla:...
7
567
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
3747
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 A2 WHERE A1.desc = A2.anc) ) SELECT anc FROM MaryAncestor
64
7341
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 suitable base case to stop the recursion. Can someone give me an example of this? Thanks!
14
1920
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 end_uid) { char ret; //ping over a range of addresses (all slaves with uid in the range from start_uid to end_uid will reply) ret = PingSlave(start_uid,end_uid);
41
3410
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 are that no local variable or global variable should be used.I have to use recursive functions.
0
9926
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9775
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11094
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10811
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
1
7952
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5780
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
5974
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4597
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3221
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.