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.
Elizalde Baguinon started programming while he was working at the Philippine Women's University in Taft Ave, Manila. He was an Audio Visual Technician then. His interest in programming started when he noticed there are so much forms required and filled up by the clients and so much documents to organize.
His break came when he was hired as a contractual Systems Developer for Lyceum of the Philippines University. There, he designed databases, web applications and client-server applications such as the Mynerva Library System.
His past engagements were with a solutions company where MIFARE SmartCards for PC application is the main product, SmartPoint Solutions Corp, and with a pharmaceutical company called Unilab Consumer Health, a subsidiary of Unilab, where he handled web apps coded in ASP.NET 2005.
He also worked at Armed Forces & Police Mutual Benefit Association (AFPMBAI) as a .NET Developer developing membership and insurance modules.
He is currently working at McKenzie Distribution Company, Inc. at Libis, Quezon City as a Senior Programmer.