Application Security Testing: An Integral Part of DevOps
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...
- Stored Procedures
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:
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:
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.
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.
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.
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.
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.
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:
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.
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!