Click here to Skip to main content
15,867,594 members
Articles / Programming Languages / SQL

A Query Builder Class For Visual Basic 2005 Hand Coders

Rate me:
Please Sign up or sign in to vote.
4.60/5 (16 votes)
12 May 2011CPOL2 min read 54.8K   837   49   13
Most programmers prefer hand-coding database queries. This is a class that automates query building, while the code is as readable as the program logic.

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.

VB
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:

VB
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.

VB
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.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


Written By
Software Developer McKenzie Distribution Company, Inc.
Philippines Philippines
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.

Comments and Discussions

 
QuestionGood project, I am interested in contributing Pin
Member 56554010-May-17 10:47
Member 56554010-May-17 10:47 
GeneralMy vote of 4 Pin
Wooters12-May-11 8:49
Wooters12-May-11 8:49 
GeneralRe: My vote of 4 Pin
Elizalde G. Baguinon12-May-11 14:26
Elizalde G. Baguinon12-May-11 14:26 
RantVote for Me!!! Pin
Elizalde G. Baguinon14-Dec-08 18:19
Elizalde G. Baguinon14-Dec-08 18:19 
QuestionQuestion - Does this handle relations (joins)? Pin
Techpro19-Nov-08 5:12
Techpro19-Nov-08 5:12 
AnswerRe: Question - Does this handle relations (joins)? Pin
Elizalde G. Baguinon19-Nov-08 14:11
Elizalde G. Baguinon19-Nov-08 14:11 
GeneralQueryBuilder Fix Pin
Elizalde G. Baguinon16-Nov-08 13:57
Elizalde G. Baguinon16-Nov-08 13:57 
GeneralRe: QueryBuilder Fix Pin
ddelft18-Nov-08 4:38
ddelft18-Nov-08 4:38 
GeneralRe: QueryBuilder Fix Pin
Baligh Mehrez11-May-11 21:00
professionalBaligh Mehrez11-May-11 21:00 
GeneralRe: QueryBuilder Fix Pin
Elizalde G. Baguinon12-May-11 14:27
Elizalde G. Baguinon12-May-11 14:27 
GeneralRe: QueryBuilder Fix Pin
Baligh Mehrez13-May-11 20:56
professionalBaligh Mehrez13-May-11 20:56 
QuestionLooks good but a compile error - 'Private m_DF As Functions' Pin
Dean_SF14-Nov-08 5:49
Dean_SF14-Nov-08 5:49 
AnswerRe: Looks good but a compile error - 'Private m_DF As Functions' Pin
ddelft14-Nov-08 5:56
ddelft14-Nov-08 5:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.