ADO.NET 3.5 Cookbook, 2nd Edition

4.13. Executing a Recursive Query

Problem

You need to execute a recursive query—that is a query that references itself.

Solution

Use a recursive Common Table Expression (CTE).

This solution uses a recursive CTE to return a list of employees and their managers from the HumanResources.Employee table in AdventureWorks. The top 20 records are displayed.

The C# code in Program.cs in the project ExecuteRecursiveQuery is shown in Example 4-14.

Example 4-14. File: Program.cs for ExecuteRecursiveQuery solution

using System; using System.Data; using System.Data.SqlClient; namespace ExecuteRecursiveQuery { class Program { static void Main(string[] args) { string sqlConnectString = "Data Source=(local);" + "Integrated security=SSPI;Initial Catalog=AdventureWorks;"; string sqlSelect = "WITH DirectReports( " + "ManagerID, EmployeeID, Title, FirstName, LastName, EmployeeLevel) AS " + "(SELECT e.ManagerID, e.EmployeeID, e.Title, c.FirstName, c.LastName, " + "0 AS EmployeeLevel " + "FROM HumanResources.Employee e " + "JOIN Person.Contact AS c ON e.ContactID = c.ContactID " + "WHERE ManagerID IS NULL " + "UNION ALL " + "SELECT e.ManagerID, e.EmployeeID, e.Title, c.FirstName, c.LastName, " + "EmployeeLevel + 1 " + "FROM HumanResources.Employee e " + "INNER JOIN DirectReports d ON e.ManagerID = d.EmployeeID " + "JOIN Person.Contact AS c ON e.ContactID = c.ContactID) " + "SELECT TOP 20 * " + "FROM DirectReports"; // Fill the DataTable DataTable dt = new DataTable(); SqlDataAdapter da = new SqlDataAdapter(sqlSelect, ...