Using SQL Common Table Expressions to Improve VB Queries

Introduction

Being able to use SQL with Visual Basic is essential; it is a must have for any seasoned as well as aspiring programmer. Having advanced knowledge of the SQL language is even more vital. Today, you will learn about SQL Common Table Expressions and how to make use of them through Visual Basic.

First, the basics…

Databases

A database’s sole function is to store information, hence the name: data base. A database consists of the following objects:

  • Tables
  • Queries
  • Views
  • Stored Procedures
  • Functions
  • Tables

Tables are the main building blocks of any database. This is where all the information will be stored. A table consists of rows (records) and columns (fields). A row or record contains all the information pertaining to one topic. A column or field is once piece of information. Here is a small example of records and fields:

CTE1
Figure 1: Records and Fields

In Figure 1, you can see that there are seven records and three fields. This database will be created a bit later.

Although you may have many columns in each table and many tables at your disposal in a database, it doesn’t mean that you have to make use of all of them. With databases, less is more. This means that the fewer fields and the fewer tables used in your database will amount to the best performance. Let me give you another example:

CTE2
Figure 2: Database Tables used productively

This is from one of my programs I had to implement at my work. Its whole purpose is to track student progress according to which course the student is doing. Each course has different subjects. I show this here to show you that a simple program can make use of multiple, inter-connected tables. An inexperienced programmer would add all the fields into one table, which is possible, but is also wrong. This system has been running successfully for twelve years already. There is more than enough space to store the student’s personal information, as well as more than enough space for custom courses and custom subjects.

All these tables are connected by a term called relationships. If you look closely at the above tables inside Figure 2, you will see that inside each table there is a reference to another table’s field. This forms a relationship between tables. A relationship becomes possible when there is a Primary key inside one table referencing a foreign key. This is not really the main topic of today’s article, so let me not get ahead of myself (as always) again. More information on database tables can be found here.

SQL

Structured Query Language (SQL) is the programming language you use to build databases, extract information from databases, and store information into databases. In an earlier article, I spoke about the term database, so if you haven’t read it yet, have a read here.

To extract any information from any database, we need to write an SQL query. A query can be explained as a request for information which it then presents ultimately. Now, as I have mentioned, all database information gets stored inside tables. These tables contain all the information that has been stored. We must write a query to get this information and present it to the user.

Queries

The simplest form of a query would look like this:

SELECT * FROM TableName

This will give you all the data stored in that particular table. Remember now, some tables can have millions of records inside them, so, in this case, this simple query can give you all the millions of records. Usually, this is not really needed. Usually, there is some sort of condition involved when extracting information. A query with a condition will look like the following:

SELECT * FROM TableName WHERE Field = Value

A query similar to the above now has the capability to return only certain information. Let me use a bank as an example. With any bank, they deal with millions of customers. Many of these customers may have the same last names, or even the exact same names. Any customer of the bank may have more than one type of account. Now, taking all of this into consideration, a query to return all the information to a specific person may be troublesome. If conditions did not exist, this would have caused you to stand in the banking queue for a very long time while the poor teller sifts through all the information just to find the correct person with the correct information.

We can take conditions further, and write a query like the following:

SELECT * FROM TableName WHERE Field1 = Value1
   AND Field2 > Value2 OR Field3 < Value3

It gets trickier now, because here we deal with more than one field’s value and more than one condition. The above query can be translated into layman’s terms to mean:

Select all the data from the table named TableName where Column1 is equal to Value1 AND Column2 is greater than Value2 OR Column3 is less than Value3.

It can get more complicated…

SELECT * FROM Table1, Table2, Table3
   WHERE Table1.Field1
   = Value1 AND Table2.Field2 > Value2
   OR Table3.Field3 < Value3

This SQL code extracts certain data from three different tables, based on various conditions set.

Views

A View is a dynamic result set. This means that we can create a view based on a query and it will not affect any data that is stored. More information on Views can be found here.

Stored Procedures

A Stored Procedure is a sequence of SQL code that can perform tedious tasks quickly. By making use of Stored Procedures, you will save a lot of time and won’t need to run each query (that can reside in a Stored Procedure) manually. More information on Stored Procedures can be found here.

Functions

There are many functions inside the SQL Language. These functions can help with string manipulation, date manipulation, as well as computing Averages, just to name a few. A complete list of SQL Functions can be found here.

Common Table Expressions

A Common Table Expression (CTE) specifies a temporary named result set. A CTE is derived from an ordinary query and defined within the execution scope of a SELECT, INSERT, UPDATE, or DELETE statement. A WITH clause also can be used in a CREATE VIEW statement as part of its defining SELECT statements. A CTE can references itself, which is referred to as a recursive common table expression.

An Example of a CTE in a Stored Procedure Follows:

CREATE PROC [dbo].[CategoryListShow
@ID INT,
@CategoryResult VARCHAR(50) OUTPUT
   AS
   BEGIN
   ;WITH
cteFirstLine(StudentName, StudentSurname, StudentID,
      CourseID, Active) AS
   (
      SELECT TOP 50 S.StudentName, S.StudentSurname,
         S.StudentID, C.CourseID, 1 AS Active
      FROM Students S
      LEFT JOIN Courses C ON S.StudentID = C.StudentID
      ORDER BY StudentName DESC
   )
,
cteSecondLine(StudentName, StudentSurname, StudentID,
      CourseID, Active) AS
   (
      SELECT TOP 50 S.StudentName, S.StudentSurname,
         S.StudentID, C.CourseID, 2 AS Active
      FROM Students S
      LEFT JOIN Courses C ON S.StudentID = C.StudentID
      ORDER BY StudentName DESC
   )
,
cteUnified (StudentName, StudentSurname, StudentID,
   CourseID, Active) AS
(
   SELECT StudentName, StudentSurname, StudentID,
      CourseID, Active
   FROM cteFirstLine

   UNION

   SELECT StudentName, StudentSurname, StudentID,
      CourseID, Active
   FROM cteSecondLine

)

INSERT INTO InActiveStudents(StudentName, StudentSurname,
   StudentID, CourseID, Active)

SELECT TOP 2000 StudentName, StudentSurname, StudentID,
   CourseID, Active
FROM
cteUnified

SELECT * FROM InActiveStudents ORDER BY StudentName DESC

It looks much more complicated than what it is. This particular CTE joins two different result sets from the same table. On one line of the result set, it will show all the Active Students; on the other, it will show all the Inactive students. This CTE is hosted inside a Stored Procedure.

Visual Basic Project

Create a Visual Basic Windows Forms Project and add one button onto it. Add the required Namespace for all the SQL commands to work:

Imports System.Data.SqlClient

Add the following code behind Button1’s Click event:

      Private Sub Button1_Click(sender As Object, e As EventArgs) _
         Handles Button1.Click

      Dim connCTE As SqlConnection = New _
         SqlConnection("Server=HTG;" & _
                       "uid=sa;pwd=;")

      Using (connCTE)

         Dim sqlComm As New SqlCommand

         sqlComm.Connection = connCTE

         sqlComm.CommandText = "CTEStoredProcedure"
         sqlComm.CommandType = CommandType.StoredProcedure


         connCTE.Open()

         sqlComm.ExecuteNonQuery()
      End Using

   End Sub

All I did was to create a connection object, create an SQL Command object, and supply the name of the Stored Procedure hosting the SQL Common Table Expression that will be executed by the command object.

Conclusion

Common Table Expressions can save a lot of time. With practice, knowing when and how to use them will come naturally. Until next time, cheers!

Hannes DuPreez
Hannes DuPreez
Ockert J. du Preez is a passionate coder and always willing to learn. He has written hundreds of developer articles over the years detailing his programming quests and adventures. He has written the following books: Visual Studio 2019 In-Depth (BpB Publications) JavaScript for Gurus (BpB Publications) He was the Technical Editor for Professional C++, 5th Edition (Wiley) He was a Microsoft Most Valuable Professional for .NET (2008–2017).

More by Author

Get the Free Newsletter!

Subscribe to Developer Insider for top news, trends & analysis

Must Read