• Download source code - 2.37 KB

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
        
'/* Create the builder object */
m_Qry = New QueryBuilder
m_Qry.TableName = "company"
'/* Set the command type, 
'the default setting is QueryBuilder.CommandQuery.cqSELECT */
m_Qry.CommandType = QueryBuilder.CommandQuery.cqSELECT
'/* Add a filter. Each new filter will be 
'  considered as AND. To execute an OR or LIKE, create a string that looks
'  like this: m_Qry.AddFilter("Company_Id LIKE 'My%'")
'*/
m_Qry.AddFilter("Company_Id=1")
'/* Add Columns */
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")

'/* if you want to sort, just add: */
m_Qry.AddOrder("Full_Name")
'/* Generate the SQL */
Dim m_SQL As String = m_Qry.GetQuery()

'/* Try to display the output */
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

'/* Create the builder object */
m_Qry = New QueryBuilder
m_Qry.TableName = "company"
'/* Set the command type, the default setting is QueryBuilder.CommandQuery.cqSELECT */
m_Qry.CommandType = QueryBuilder.CommandQuery.cqUPDATE
'/* Add a filter. Each new filter will be 
'  considered as AND. To execute an OR or LIKE, create a string that looks
'  like this: m_Qry.AddFilter("Company_Id LIKE 'My%'")
'*/
m_Qry.AddFilter("Company_Id=1")
'/* Add Columns and Values */
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)

'/* Generate the SQL */
Dim m_SQL As String = m_Qry.GetQuery()

'/* Try to display the output */
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

'/* Create the builder object */
m_Qry = New QueryBuilder
m_Qry.TableName = "company"
'/* Set the command type, the default setting is QueryBuilder.CommandQuery.cqSELECT */
m_Qry.CommandType = QueryBuilder.CommandQuery.cqINSERT
'/* Add Columns and Values */
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)

'/* Generate the SQL */
Dim m_SQL As String = m_Qry.GetQuery()

'/* Try to display the output */
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.