Introduction
This is a Visual Basic class that semi-automates SQL query building. It is useful for database queries with numbers of columns that you find very hard to code every comma, quote, or field name.
Background
I am mostly familiar with the classic ASP and PHP programming methods. I do not usually use designers to create business logic and code implementations. While ADO.NET's classes are easy to understand and implement, I wanted some more coding to my heart's content. This class originated from classic ASP (VBScript), then moved to a VB6 class, to a PHP class, and finally to a Visual Studio 2005 (Visual Basic) class.
Using the code
Download the class file (QueryBuilder.zip) above and add it to your Visual Basic project. A typical use of this class is the SELECT
query to a database. We will use a table called company.
Dim m_Qry As QueryBuilder
m_Qry = New QueryBuilder
m_Qry.TableName = "company"
m_Qry.CommandType = QueryBuilder.CommandQuery.cqSELECT
m_Qry.AddFilter("Company_Id=1")
m_Qry.AddField("Company_Number")
m_Qry.AddField("Short_Name")
m_Qry.AddField("Full_Name")
m_Qry.AddField("Address1")
m_Qry.AddField("Address2")
m_Qry.AddField("City_Name")
m_Qry.AddField("Phone_Number")
m_Qry.AddField("Fax_Number")
m_Qry.AddOrder("Full_Name")
Dim m_SQL As String = m_Qry.GetQuery()
Debug.Print m_SQL
Note: To create joined queries, the TableName
property should contain all the JOIN
syntax (e.g., Company INNER JOIN Department ON Company.Company_Id=Department.Company_Id
). The columns should include the table name or alias names, like company.Company_Number or A.Company_Number.
This class is very useful in queries that have many columns to add and set values. Look at the example below for an UPDATE
:
Dim m_Qry As QueryBuilder
m_Qry = New QueryBuilder
m_Qry.TableName = "company"
m_Qry.CommandType = QueryBuilder.CommandQuery.cqUPDATE
m_Qry.AddFilter("Company_Id=1")
m_Qry.AddFieldValuePair("Company_Number", ."MyCompanyNumber001", True)
m_Qry.AddFieldValuePair("Short_Name", "EGBC", True)
m_Qry.AddFieldValuePair("Full_Name", "EagleBush Software", True)
m_Qry.AddFieldValuePair("Address1", "Some Address in the Philippines", True)
m_Qry.AddFieldValuePair("Address2", "My Other Address in the Philippines", True)
m_Qry.AddFieldValuePair("City_Name", "Mandaluyong City", True)
m_Qry.AddFieldValuePair("Phone_Number", "+639174169922", True)
m_Qry.AddFieldValuePair("Fax_Number", "1800-FAXME", True)
Dim m_SQL As String = m_Qry.GetQuery()
Debug.Print m_SQL
As for the INSERT
command, we will just remove the AddFilter()
function, and modify the command type.
Dim m_Qry As QueryBuilder
m_Qry = New QueryBuilder
m_Qry.TableName = "company"
m_Qry.CommandType = QueryBuilder.CommandQuery.cqINSERT
m_Qry.AddFieldValuePair("Company_Number", ."MyCompanyNumber001", True)
m_Qry.AddFieldValuePair("Short_Name", "EGBC", True)
m_Qry.AddFieldValuePair("Full_Name", "EagleBush Software", True)
m_Qry.AddFieldValuePair("Address1", "Some Address in the Philippines", True)
m_Qry.AddFieldValuePair("Address2", "My Other Address in the Philippines", True)
m_Qry.AddFieldValuePair("City_Name", "Mandaluyong City", True)
m_Qry.AddFieldValuePair("Phone_Number", "+639174169922", True)
m_Qry.AddFieldValuePair("Fax_Number", "1800-FAXME", True)
Dim m_SQL As String = m_Qry.GetQuery()
Debug.Print m_SQL
The DELETE
command only requires filters. Please be careful not to omit the AddFilter()
function, or all your data will be deleted. The class should warn you for DELETE
queries with no filters.
Points of interest
I still use this class in most of my projects when data manipulation is required. This practice enables me to seamlessly transfer from one programming language to another. Also, this practice of creating readable code makes it easy to port from one scripting language to another, where program designers mostly fail.
History
The code is not complete as it should be for general usage. I add methods and properties as time goes by, or as is required by the application I'm doing. If you have implemented a more robust and elegant code than my own, please give me a copy.