Home

Microsoft Visual C#: Common Table Expressions (CTE)
     

Introduction

A common table expression, or CTE, is a temporary selection or other query operation of records from one or more tables. You use it to get an idea of what the query operation would produce if performed on a table. You can generate a CTE to create, select, merge, or delete records.

There are two types of common table expressions: recursive and non-recursive.

Practical LearningPractical Learning: Introducing Common Table Expressions
  1. Start Microsoft Visual Studio
  2. To create a new application, on the main menu, click File -> New Project...
  3. In the middle list, click Windows Forms Application
  4. Change the Name to HotelManagement1
  5. Click OK
  6. In the Solution Explorer, right click Form1.cs and click Rename
  7. Type HotelManagement.cs and press enter
  8. Double-click the middle of the form to generate its Load event
  9. To create a new database, implement the event as follows:
    using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace HotelManagement1
{
    public partial class HotelManagement : Form
    {
        public HotelManagement()
        {
            InitializeComponent();
        }

        void CreateHotelRooms()
        {
            using (SqlConnection conHotelManagement =
                new SqlConnection("Data Source=(local);" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdHotelManagement =
            new SqlCommand("IF  EXISTS(SELECT name " +
                           "    FROM sys.databases " +
                           "    WHERE name = N'HotelManagement1') " +
                           "DROP DATABASE HotelManagement1;" +
                           "CREATE DATABASE HotelManagement1;",
                           conHotelManagement);
                conHotelManagement.Open();

                cmdHotelManagement.ExecuteNonQuery();
                MessageBox.Show("The HotelManagement1 database has been created.",
                                "Ceil Inn - Hotel Management", MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
            }

            using (SqlConnection conHotelManagement =
                new SqlConnection("Data Source=(local);" +
                                  "Database=HotelManagement1;" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdHotelManagement =
                    new SqlCommand("CREATE TABLE Bedrooms(" +
                                  "RoomNumber nchar(10) not null," +
                                  "RoomType nvarchar(20) default N'Bedroom'," +
                                  "BedType nvarchar(40) default N'Queen'," +
                                  "Rate money default 75.85, Available bit default 0);",
                                   conHotelManagement);
                conHotelManagement.Open();

                cmdHotelManagement.ExecuteNonQuery();
                MessageBox.Show("A table named Bedrooms has been created.",
                                "Ceil Inn - Hotel Management", MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
            }

            using (SqlConnection conHotelManagement =
                new SqlConnection("Data Source=(local);" +
                                  "Database=HotelManagement1;" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdHotelManagement =
                    new SqlCommand("INSERT INTO Bedrooms(RoomNumber, BedType, Rate, Available)" +
                                  "VALUES(N'104', N'Queen', 80.25, 0)," +
                                  "      (N'105', N'King', 95.50, 1)," +
                                  "      (N'106', N'Queen', 65.95, 1)," +
                                  "      (N'107', N'Queen', 65.95, 1)," +
                                  "      (N'108', N'King', 92.50, 1)," +
                                  "      (N'109', N'Queen', 68.95, 0)," +
                                  "      (N'110', N'Queen', 74.95, 1);",
                                   conHotelManagement);
                conHotelManagement.Open();

                cmdHotelManagement.ExecuteNonQuery();
                MessageBox.Show("Records have been added to the Bedrooms table.",
                                "Ceil Inn - Hotel Management", MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
            }

            using (SqlConnection cnnHotelManagement =
                new SqlConnection("Data Source=(local);" +
                                  "Database=HotelManagement1;" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdHotelManagement =
                    new SqlCommand("CREATE TABLE ConferenceRooms(" +
                                  "RoomNumber nchar(10) not null," +
                                  "RoomType nvarchar(20) default N'Conference'," +
                                  "BedType nvarchar(40), Rate money default 75.85," +
                                  "Available bit default 0);", cnnHotelManagement);
                cnnHotelManagement.Open();

                cmdHotelManagement.ExecuteNonQuery();
                MessageBox.Show("A table named ConferenceRooms has been created.",
                                "Ceil Inn - Hotel Management", MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
            }

            using (SqlConnection conHotelManagement =
                new SqlConnection("Data Source=(local);" +
                                  "Database=HotelManagement1;" +
                                  "Integrated Security=SSPI;"))
            {
                SqlCommand cmdHotelManagement =
                    new SqlCommand("INSERT INTO ConferenceRooms(RoomNumber, Rate, Available)" +
                                   "VALUES(N'C-120', 525.00, 1);" +
                                   "INSERT INTO Bedrooms(RoomNumber, RoomType, BedType, Rate, Available)" +
                                   "VALUES(N'116', N'Studio', N'King', 112.95, 0);" +
                                   "INSERT INTO ConferenceRooms(RoomNumber, Rate, Available)" +
                                   "VALUES(N'C-122', 450.00, 1);" +
                                   "INSERT INTO Bedrooms(RoomNumber, RoomType, BedType, Rate, Available)" +
                                   "VALUES(N'202', N'Studio', N'King', 105.95, 1)," +
                                   "      (N'203', N'Studio', N'Queen', 102.50, 1);" +
                                   "INSERT INTO Bedrooms(RoomNumber, BedType, Rate, Available)" +
                                   "VALUES(N'204', N'Double', 115.95, 1)," +
                                   "      (N'205', N'Queen', 82.95, 0)," +
                                   "      (N'206', N'King', 98.50, 1)" +
                                   "INSERT INTO ConferenceRooms(RoomNumber, Rate, Available)" +
                                   "VALUES(N'C-302', 650.00, 1);",
                                   conHotelManagement);
                conHotelManagement.Open();

                cmdHotelManagement.ExecuteNonQuery();
                MessageBox.Show("Records have been added to the ConferenceRooms table.",
                                "Ceil Inn - Hotel Management", MessageBoxButtons.OK,
                                MessageBoxIcon.Information);
            }
        }

        private void HotelManagement_Load(object sender, EventArgs e)
        {
            CreateHotelRooms();
        }
    }
}
  10. Press F5 to execute
  11. After clicking OK on the message boxes, close the form and return to your programming environment

Creating a Common Table Expression

The formula to create a common table expression (CTE) is:

[ WITH <common_table_expression> [ ,...n ] ]

<common_table_expression>::=
        expression_name [ ( column_name [ ,...n ] ) ]
    AS
        ( CTE_query_definition )

You start with the WITH keyword followed by a name for the temporary set. The name must be different from any table that will be used in the CTE's expression. Later we will see the parameters you can add after the name. After the name, type AS followed by parentheses. In the parentheses, create a simple or composite SELECT expression.

After the code that defines the CTE, that is, after the AS(CTE_query_definition) expression, create a SELECT statement that will produce the results.

Practical LearningPractical Learning: Creating a Common Table Expression
  1. Change the design of the form as follows:
     
    Ceil Inn - All Bedrooms
    Control Text Name Anchor
    Button Show Rooms btnAllBedrooms  
    DataGridView   dgvRooms Top, Bottom, Left, Right
  2. Double-click the Show Rooms button and change the file as follows:
    private void HotelManagement_Load(object sender, EventArgs e)
{
    //CreateHotelRooms();
}

private void btnAllBedrooms_Click(object sender, EventArgs e)
{
    using (SqlConnection conHotelManagement =
	new SqlConnection("Data Source=(local);" +
              		  "Database='HotelManagement1';" +
              		  "Integrated Security='SSPI';"))
    {
        SqlCommand cmdHotelManagement =
            new SqlCommand("WITH SleepingRooms AS ( " +
                           "SELECT * FROM Bedrooms) " +
                           "SELECT * FROM SleepingRooms;",
                           conHotelManagement);
        SqlDataAdapter sdaHotelManagement =
            new SqlDataAdapter(cmdHotelManagement);
        BindingSource bsHotelManagement = new BindingSource();

        DataSet dsHotelManagement = new DataSet("RoomsSet");
        sdaHotelManagement.Fill(dsHotelManagement);

        conHotelManagement.Open();
        bsHotelManagement.DataSource = dsHotelManagement.Tables[0];
        dgvRooms.DataSource = bsHotelManagement;
    }
}
  3. Press F5 to see the result
  4. Click Show Rooms
     
    Ceil Inn - All Bedrooms
  5. Close the form and return to your programming environment
  6. Change the design of the form as follows:
     
    Ceil Inn - Available Bedrooms
    Control Text Name Anchor
    Button All Bedrooms btnAllBedrooms  
    Button Available Bedrooms btnAvailableBedrooms  
    DataGridView   dgvRooms Top, Bottom, Left, Right
  7. Double-click the Available Bedrooms button
  8. To be able to see a list of available rooms, implement its event as follows:
    private void btnAvailableBedrooms_Click(object sender, EventArgs e)
{
    using (SqlConnection conHotelManagement =
    new SqlConnection("Data Source=(local);" +
                      "Database='HotelManagement1';" +
                      "Integrated Security='SSPI';"))
    {
        SqlCommand cmdHotelManagement =
            new SqlCommand("WITH SleepingRooms AS" +
                           "(" +
                           "    SELECT * FROM Bedrooms WHERE Available = 1" +
                           ")" +
                           "SELECT * FROM SleepingRooms;",
                           conHotelManagement);
        SqlDataAdapter sdaHotelManagement =
            new SqlDataAdapter(cmdHotelManagement);
        BindingSource bsHotelManagement = new BindingSource();

        DataSet dsHotelManagement = new DataSet("RoomsSet");
        sdaHotelManagement.Fill(dsHotelManagement);

        conHotelManagement.Open();
        bsHotelManagement.DataSource = dsHotelManagement.Tables[0];
        dgvRooms.DataSource = bsHotelManagement;
    }
}
  9. Press F5 to execute
  10. To see a list of all rooms, click the Show All Rooms button
  11. To see  available rooms, click the other button
     
    Ceil Inn
  12. Close the form and return to your programming environment

A CTE With Parameters

To make sure you can externally control the results of a CTE, you can pass a type of parameter to it. To do this, after the name of the CTE and before the AS operator, add the parentheses and pass one or more parameters, each represented by a name. The names of parameters must be the exact same names of columns of the table(s) from which the CTE's statement will be based. The number of columns must be the same as the number of columns that will be involved in the final SELECT statement.

In the body of the CTE, use the parameter(s) as you wish. For example, you can involve the parameter(s) in a condition in the CTE.

Practical LearningPractical Learning: Passing Parameters to a CTE
  1. Change the design of the form as follows:
     
    Ceil Inn - Queen Bedrooms
    Control Text Name
    Button All Bedrooms btnAllBedrooms
    Button All Queen Size Rooms btnAllQueenRooms
    Button Available Bedrooms btnAvailableBedrooms
    Button Available Queen Rooms btnAvailableQueenRooms
  2. Double-click the All Queen Size Rooms button
  3. To be able to see a list of available rooms, implement its event as follows:
    private void btnAllQueenRooms_Click(object sender, EventArgs e)
{
    using (SqlConnection conHotelManagement =
    new SqlConnection("Data Source=(local);" +
                      "Database='HotelManagement1';" +
                      "Integrated Security='SSPI';"))
    {
        SqlCommand cmdHotelManagement =
            new SqlCommand("WITH SleepingRoomt(RoomNumber, RoomType, BedType, Rate, Available)" +
                           "AS" +
                           "(" +
                           "	SELECT RoomNumber, RoomType, BedType, Rate, Available " +
                           "	FROM Bedrooms " +
                           "	WHERE BedType = N'Queen'" +
                           ")" +
                           "SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms;",
                           conHotelManagement);
        SqlDataAdapter sdaHotelManagement =
            new SqlDataAdapter(cmdHotelManagement);
        BindingSource bsHotelManagement = new BindingSource();

        DataSet dsHotelManagement = new DataSet("RoomsSet");
        sdaHotelManagement.Fill(dsHotelManagement);

        conHotelManagement.Open();
        bsHotelManagement.DataSource = dsHotelManagement.Tables[0];
        dgvRooms.DataSource = bsHotelManagement;
    }
}
  4. Press F5 to execute
  5. To see a list of all rooms, click the Show All Rooms button
  6. To see a list of queen size rooms, click the All Queen Size Rooms button
     
    Ceil Inn - Queen Size Rooms
  7. Close the form and return to your programming environment
  8. Double-click the Available Queen Rooms button
  9. Implement its event as follows:
    private void btnAvailableQueenRooms_Click(object sender, EventArgs e)
{
    using (SqlConnection conHotelManagement =
    new SqlConnection("Data Source=(local);" +
                      "Database='HotelManagement1';" +
                      "Integrated Security='SSPI';"))
    {
        SqlCommand cmdHotelManagement =
            new SqlCommand("WITH SleepingRooms(RoomNumber, RoomType, BedType, Rate, Available)" +
                           "AS" +
                           "(" +
                           "    SELECT RoomNumber, RoomType, BedType, Rate, Available " +
                           "    FROM Bedrooms " +
                           "    WHERE BedType = N'Queen'" +
                           ")" +
                           "SELECT RoomNumber, RoomType, Rate, Available " +
                           "FROM BedRooms " +
                           "WHERE Available = 1;",
                           conHotelManagement);
        SqlDataAdapter sdaHotelManagement =
            new SqlDataAdapter(cmdHotelManagement);
        BindingSource bsHotelManagement = new BindingSource();

        DataSet dsHotelManagement = new DataSet("RoomsSet");
        sdaHotelManagement.Fill(dsHotelManagement);

        conHotelManagement.Open();
        bsHotelManagement.DataSource = dsHotelManagement.Tables[0];
        dgvRooms.DataSource = bsHotelManagement;
    }
}
  10. Press F5 to execute
  11. To see a list of all rooms, click the Show All Rooms button
  12. To see a list of queen size rooms, click the Show Queen Rooms button
  13. To see a list of available queen size rooms, click the Available Queen Rooms button
     
    Ceil Inn - Queen Size Rooms
  14. Close the form and return to your programming environment

Topics on Common Table Expressions
 

Using Conditions

In all of our SELECT expressions so far, we considered all records of the intended table. In some cases, you can set a condition that would select only some records. You can use the condition in one or both SELECT statements.

Recursive Common Table Expressions

A recursive common table expression is a CTE that can contain more than one SELECT statement. In the body of the CTE, you can create as many SELECT statements as you want but those statements must be joined. To join them, you can use a UNION, UNION ALL, or MERGER operator.

Practical LearningPractical Learning: Creating a Recursive CTE
  1. Change the design of the form as follows:
     
    Ceil Inn - Hotel Rooms
    Control Text Name Anchor
    Button All Hotel Rooms btnAllHotelRooms  
    Button All Bedrooms btnAllBedrooms  
    Button All Queen Rooms btnAllQueenRooms  
    Button Available Hotel Rooms btnAvailableHotelRooms  
    Button Available Bedrooms btnAvailableBedrooms  
    Button Available Queen Rooms btnAvailableQueenRooms  
    DataGridView   dgvRooms Top, Bottom, Left, Right
  2. Double-click All Hotel Rooms
  3. Implement its event as follows:
    private void btnHotelRooms_Click(object sender, EventArgs e)
{
    using (SqlConnection conHotelManagement =
    new SqlConnection("Data Source=(local);" +
                      "Database='HotelManagement1';" +
                      "Integrated Security='SSPI';"))
    {
        SqlCommand cmdHotelManagement =
            new SqlCommand("WITH HotelRooms " +
                           "AS" +
                           "(" +
                           "    SELECT * FROM Bedrooms " +
                           "    UNION " +
                           "    SELECT * FROM ConferenceRooms" +
                           ")" +
                           "SELECT * FROM HotelRooms;",
                           conHotelManagement);
        SqlDataAdapter sdaHotelManagement =
            new SqlDataAdapter(cmdHotelManagement);
        BindingSource bsHotelManagement = new BindingSource();

        DataSet dsHotelManagement = new DataSet("RoomsSet");
        sdaHotelManagement.Fill(dsHotelManagement);

        conHotelManagement.Open();
        bsHotelManagement.DataSource = dsHotelManagement.Tables[0];
        dgvRooms.DataSource = bsHotelManagement;
    }
}
  4. Press F5 to execute
  5. Click the All Hotel Rooms button
     
    Ceil Inn
  6. Close the form and  return to your programming environment
  7. Double-click the Available Hotel Room button
  8. Implement its event as follows:
    private void btnAvailableHotelRooms_Click(object sender, EventArgs e)
{
    using (SqlConnection conHotelManagement =
    new SqlConnection("Data Source=(local);" +
                      "Database='HotelManagement1';" +
                      "Integrated Security='SSPI';"))
    {
        SqlCommand cmdHotelManagement =
            new SqlCommand("WITH HotelRooms " +
                           "AS" +
                           "(" +
                           "    SELECT * FROM Bedrooms " +
                           "    UNION " +
                           "    SELECT * FROM ConferenceRooms" +
                           ")" +
                           "SELECT RoomNumber, RoomType, BedType, Rate " +
                           "FROM HotelRooms " +
                           "WHERE Available = 1;",
                           conHotelManagement);
        SqlDataAdapter sdaHotelManagement =
            new SqlDataAdapter(cmdHotelManagement);
        BindingSource bsHotelManagement = new BindingSource();

        DataSet dsHotelManagement = new DataSet("RoomsSet");
        sdaHotelManagement.Fill(dsHotelManagement);

        conHotelManagement.Open();
        bsHotelManagement.DataSource = dsHotelManagement.Tables[0];
        dgvRooms.DataSource = bsHotelManagement;
    }
}
  9. Click the Available Hotel Rooms button
     
    Ceil Inn
  10. Close the form and return to your programming environment

Non-Recursive Common Table Expressions

A non-recursive common table expression is a CTE that can be followed by only one SELECT, INSERT, UPDATE, or DELETE statement that involves a column from inside the CTE's body.

 
Home Copyright © 2011-2012 FunctionX