title: N-tiered Parent/Child Breadcrumb Trail - Bottom to Top Loop
body: <p>I am trying to building a breadcrumb trail based on a parent/child relationship in a SQL database table. I have done these in the past, but what I need to do now is walk my way up through the hierarchy from the bottom up (backwards).</p>
  
  <p>Here is what my DataTable looks like:</p>
  
  <p><strong>DocID</strong> | <strong>ParentDocID</strong> | <strong>Name</strong><br />
  7 | 0 | Images (Root)<br />
  24 | 7 | JPG (Level 2)<br />
  34 | 24 | Size (Level 3)<br /></p>
  
  <p>So this is an N-tiered architecture. I always know what level I am at via a query string variable. I need to loop from the bottom up so that a user can bookmark what level in the tier structure they are at. I am having trouble figuring this out. I cannot use session or viewstate.</p>
  
  <p><strong>Desired Results:</strong></p>
  
  <p>User goes to this link:
  Default.aspx?DocId=34 – Then they see the breadcrumb trail as such:<br />
  <strong>Home</strong> > <strong>Images</strong> > <strong>JPG</strong> > <em>Size</em></p>
  
  <p>Default.aspx?DocId=24 – Then they see the breadcrumb trail as such:<br />
  <strong>Home</strong> > <strong>Images</strong> > <em>JPG</em> </p>
  
  <p>The “Home” element I have hard-coded. I also need each item in the trail to link except for the level you are on.</p>
  
  <p>I would rather this be done in C# code. I also am open to SQL level Stored Procedure options.</p>
  
  <p>Any help would be greatly appreciated. Thank-you in advance!</p>
  
  <p>Here is some SQL I am trying but not getting the results I need. I hope this helps:</p>
  
  <pre><code>WITH Hierachy([DocID], [ParentDocID], [Name], [Level]
  AS
  (
  SELECT [DocID], [ParentDocID], [Name], 0 AS [Level]
  FROM [Documents] d
  WHERE (d.[ParentDocID] = 7) AND ([Folder] = 1) AND ([Status] = 'A') AND ([AppGroupID] = 4)
  UNION ALL
  SELECT d.[DocID], d.[ParentDocID], d.[Name], dh.[Level] + 1
  FROM [Documents] d
  INNER JOIN Hierachy dh
  --ON d.ParentDocID = dh.DocID
  ON dh.[ParentDocID] = d.[DocID] -- bottom up apporach
  WHERE ([Folder] = 1) AND ([Status] = 'A') AND ([AppGroupID] = 4)
  )
  SELECT [DocID], [ParentDocID], [Name]
  FROM Hierachy
  --WHERE [Level] &gt; 0
  </code></pre>
  
  <p>I got it! Here is the SQL that works:</p>
  
  <pre><code>WITH Hierachy([DocID], [ParentDocID], [Name], [Level])
  AS
  (
  SELECT [DocID], [ParentDocID], [Name], 0 AS [Level]
  FROM [Documents] d
  WHERE (d.[DocID] = @ParentDocID) AND ([Folder] = 1) 
  AND ([Status] = 'A') AND ([AppGroupID] = @AppGroupID)
  UNION ALL
  SELECT d.[DocID], d.[ParentDocID], d.[Name], dh.[Level] + 1
  FROM [Documents] d
  INNER JOIN Hierachy dh
  ON dh.[ParentDocID] = d.[DocID]
  WHERE ([Folder] = 1) 
  AND ([Status] = 'A') AND ([AppGroupID] = @AppGroupID)
  )
  SELECT [DocID], [ParentDocID], [Name]
  FROM Hierachy
  ORDER BY [Level] DESC
  )
  </code></pre>