Executing a Recursive Query

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, sqlConnectString);
da.Fill(dt);

Console.WriteLine("MgrID\tEmpID\tTitle\t\t\tLevel\tName");
foreach (DataRow row in dt.Rows)
Console.WriteLine("{0}\t{1}\t{2}\t{3}\t{4}, {5}",
row["ManagerID"],row["EmployeeID"],
row["Title"].ToString().PadRight(23).Substring(0,23),
row["EmployeeLevel"], row["LastName"], row["FirstName"]);

Console.WriteLine("\nPress any key to continue.");
Console.ReadKey();
}
}
}

No comments: