ADO.NET 3.5 Cookbook, 2nd Edition

4.12. Using a Common Table Expression (CTE)

Problem

You need to create and access a temporary result set during the execution of a SELECT statement.

Solution

Use a Common Table Expression (CTE).

The solution uses a CTE to retrieve and display the number of employees directly reporting to each manager in the Employee table in AdventureWorks, where the ManagerID <= 25.

The C# code in Program.cs in the project ExecuteCommonTableExpression is shown in Example 4-13.

Example 4-13. Program.cs for ExecuteCommonTableExpression solution

using System;
using System.Data;
using System.Data.SqlClient;

namespace ExecuteCommonTableExpression
{
    class Program
    {
        static void Main(string[] args)
        {
            string sqlConnectString = "Data Source=(local);" +
                "Integrated security=SSPI;Initial Catalog=AdventureWorks;";

            string sqlSelect =
                "WITH ManagerEmployees(ManagerID, EmployeesPerManager) AS " +
                "( SELECT ManagerID, COUNT(*) " +
                "FROM HumanResources.Employee " +
                "WHERE ManagerID <= 25 " +
                "GROUP BY ManagerID ) " +
                "SELECT ManagerID, EmployeesPerManager " +
                "FROM ManagerEmployees " +
                "ORDER BY ManagerID";

            // Fill the DataTable
            DataTable dt = new DataTable();
            SqlDataAdapter da = new SqlDataAdapter(sqlSelect, sqlConnectString);
            da.Fill(dt);

            Console.WriteLine("ManagerID\tEmployeesPerManager");
            foreach (DataRow row in dt.Rows)
                Console.WriteLine("{0}\t\t{1}", row["ManagerID"],
                    row["EmployeesPerManager"]);

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

The output is shown in Figure 4-13.

Figure 4-13. Output ...