Click here to Skip to main content
Click here to Skip to main content
Articles » Database » Database » SQL » Downloads
 
Add your own
alternative version

A Query Builder Class For Visual Basic 2005 Hand Coders

, 12 May 2011 CPOL
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.
QueryBuilder.zip
''' <summary>
''' QueryBuilder: This class is used to create SQL queries that is parameterized in order to create an organized query code.
''' </summary>
''' <remarks></remarks>
Public Class QueryBuilder

    Public Enum CommandQuery
        cqSELECT = 0
        cqINSERT = 1
        cqUPDATE = 2
        cqDELETE = 3
    End Enum
    Public Enum SortDirection
        sdAscending = 0
        sdDescending = 1
    End Enum
    Private Const ERR_INAPPROPRIATE_QUERY As String = "This setting is inappropriate for this query."
    Private Const ERR_NEEDS_FIELDS As String = "No fields defined."
    Private Const ERR_NEEDS_FILTERS As String = "No filters defined. If you want all records in the table to be updated or deleted, add a filter with an 'nf' value."
    Private Const ERR_NEEDS_VALUES As String = "No values defined."
    Private Const ERR_NUMFIELDVALUES_DONTMATCH As String = "The number of fields and values defined in the query doesn't match."

    Private mvarCommandType As CommandQuery

    Private mvarTableName As String
    Private mvarFields As ArrayList
    Private mvarValues As ArrayList
    Private mvarOrders As ArrayList
    Private mvarGroups As ArrayList
    Private mvarFilters As ArrayList
    Private mvarHaving As ArrayList

    Private mvarDirection As SortDirection
    Private mvarDistinct As Boolean
    Private mvarMaxOutputFields As Short
    Friend Property MaxOutputFields() As Short
        Get
            MaxOutputFields = mvarMaxOutputFields
        End Get
        Set(ByVal Value As Short)
            mvarMaxOutputFields = Value
        End Set
    End Property
    Public Property Distinct() As Boolean
        Get
            Distinct = mvarDistinct
        End Get
        Set(ByVal Value As Boolean)
            mvarDistinct = Value
        End Set
    End Property
    Public Property CommandType() As CommandQuery
        Get
            CommandType = mvarCommandType
        End Get
        Set(ByVal Value As CommandQuery)
            mvarCommandType = Value
        End Set
    End Property
    Public WriteOnly Property Direction() As SortDirection
        Set(ByVal Value As SortDirection)
            mvarDirection = Value
        End Set
    End Property
    Public Property TableName() As String
        Get
            Return mvarTableName
        End Get
        Set(ByVal Value As String)
            mvarTableName = Value
        End Set
    End Property
    Public Sub AddField(ByVal FieldName As String)
        Call pAddField(FieldName, False)
    End Sub
    Public Sub AddGroup(ByVal FieldName As String)
        mvarGroups.Add(FieldName)
    End Sub
    Public Sub AddHaving(ByVal FieldName As String)
        mvarHaving.Add(FieldName)
    End Sub
    Public Sub AddFilter(ByVal Filter_Renamed As String)
        mvarFilters.Add(Filter_Renamed)
    End Sub
    Public Sub AddOrder(ByVal orders As String)
        mvarOrders.Add(orders)
    End Sub
    Public Sub AddFieldValuePair(ByVal FieldName As String, ByVal Value As String, Optional ByVal IsStringType As Boolean = False)
        Try
            Call pAddField(FieldName, True)
            Call AddValue(Value, IsStringType)
        Catch ex As Exception
            Throw New Exception("Error adding field pair!")
        End Try
    End Sub
    Public Sub AddValue(ByVal valuestr As String, Optional ByVal IsStringType As Boolean = False)
        Dim m_VStr As String

        If IsStringType Then
            m_VStr = "'" & EscapeStr(valuestr) & "'"
        Else
            m_VStr = valuestr
        End If
        Select Case mvarCommandType
            Case CommandQuery.cqSELECT, CommandQuery.cqDELETE
                Throw New Exception(ERR_INAPPROPRIATE_QUERY)
            Case CommandQuery.cqINSERT, CommandQuery.cqUPDATE
                mvarValues.Add(m_VStr)
        End Select
    End Sub
    Public Sub ClearHaving()
        mvarHaving.Clear()
    End Sub
    Public Sub ClearGroups()
        mvarGroups.Clear()
    End Sub
    Public Sub ClearFieldAndValues()
        mvarFields.Clear()
        mvarValues.Clear()
    End Sub
    Public Sub ClearFilters()
        mvarFilters.Clear()
    End Sub
    Public Sub Clear()
        mvarFields.Clear()
        mvarValues.Clear()
        mvarOrders.Clear()
        mvarFilters.Clear()
        mvarGroups.Clear()
        mvarHaving.Clear()
        mvarTableName = ""
        mvarDirection = 0
        mvarCommandType = CommandQuery.cqSELECT
    End Sub
    Public Function GetQuery() As String
        Dim tmpstr As String
        Dim i As Short
        Dim comma As String


        GetQuery = ""
        Select Case mvarCommandType
            Case CommandQuery.cqSELECT
                tmpstr = "SELECT "
                If mvarDistinct Then
                    tmpstr = tmpstr & "DISTINCT "
                End If
                'get the fields
                If mvarFields.Count = 0 Then
                    Throw New Exception(ERR_NEEDS_FIELDS)
                Else
                    tmpstr = tmpstr & String.Join(", ", ToStringArray(mvarFields.ToArray))
                End If
                'get the table
                tmpstr = tmpstr & " FROM " & mvarTableName
                'get the filters
                If mvarFilters.Count <> 0 Then
                    tmpstr = tmpstr & " WHERE " & String.Join(" AND ", ToStringArray(mvarFilters.ToArray))
                End If
                If mvarGroups.Count <> 0 Then
                    tmpstr = tmpstr & " GROUP BY " & String.Join(",", ToStringArray(mvarGroups.ToArray))
                    If mvarHaving.Count <> 0 Then
                        tmpstr = tmpstr & " HAVING " & String.Join(",", ToStringArray(mvarHaving.ToArray))
                    End If
                End If
                If mvarOrders.Count <> 0 Then
                    tmpstr = tmpstr & " ORDER BY " & String.Join(",", ToStringArray(mvarOrders.ToArray))
                End If
                'get the direction
                If mvarDirection = SortDirection.sdDescending Then
                    tmpstr = tmpstr & " DESC"
                End If
                GetQuery = tmpstr
            Case CommandQuery.cqINSERT
                tmpstr = "INSERT INTO "
                tmpstr = tmpstr & mvarTableName
                'get the fields
                If mvarFields.Count = 0 Then
                    Throw New Exception(ERR_NEEDS_FIELDS)
                Else
                    If mvarValues.Count = 0 Then
                        Throw New Exception(ERR_NEEDS_VALUES)
                    Else
                        If mvarFields.Count <> mvarValues.Count Then
                            Throw New Exception(ERR_NUMFIELDVALUES_DONTMATCH)
                        Else
                            tmpstr = tmpstr & " (" & String.Join(",", ToStringArray(mvarFields.ToArray)) & ")"
                            tmpstr = tmpstr & " VALUES "
                            tmpstr = tmpstr & "(" & String.Join(",", ToStringArray(mvarValues.ToArray)) & ")"
                        End If
                    End If
                End If
                GetQuery = tmpstr
            Case CommandQuery.cqUPDATE
                tmpstr = "UPDATE "
                'get the table
                tmpstr = tmpstr & mvarTableName & " SET "
                'get the fields along with the values
                If mvarFields.Count = 0 Then
                    Throw New Exception(ERR_NEEDS_FIELDS)
                Else
                    If mvarValues.Count = 0 Then
                        Throw New Exception(ERR_NEEDS_VALUES)
                    Else
                        If mvarFields.Count <> mvarValues.Count Then
                            Throw New Exception(ERR_NUMFIELDVALUES_DONTMATCH)
                        Else
                            'ok, loop over to set values
                            comma = ""
                            For i = 0 To mvarFields.Count - 1
                                tmpstr = tmpstr & comma & mvarFields(i) & "=" & mvarValues(i).ToString.Trim()
                                comma = ","
                            Next i
                            'ok then set the filters
                            If mvarFilters.Count = 0 Then
                                Throw New Exception(ERR_NEEDS_FILTERS)
                            Else
                                If mvarFilters.Count = 0 Then
                                    'lets see may be it's an 'nf'
                                    If mvarFilters(0).ToString.Trim.ToLower <> "nf" Then
                                        tmpstr = tmpstr & " WHERE " & mvarFilters(0)
                                    End If
                                Else
                                    'get the filters
                                    If mvarFilters.Count <> 0 Then
                                        tmpstr = tmpstr & " WHERE " & String.Join(" AND ", ToStringArray(mvarFilters.ToArray))
                                    End If
                                End If
                            End If
                            'get the groups
                            If mvarGroups.Count <> 0 Then
                                tmpstr = tmpstr & " GROUP BY " & String.Join(",", ToStringArray(mvarGroups.ToArray))
                                If mvarHaving.Count <> 0 Then
                                    tmpstr = tmpstr & " HAVING " & String.Join(",", ToStringArray(mvarHaving.ToArray))
                                End If
                            End If
                            GetQuery = tmpstr
                        End If
                    End If
                End If
            Case CommandQuery.cqDELETE
                tmpstr = "DELETE FROM "
                tmpstr = tmpstr & mvarTableName
                'set the filters
                If mvarFilters.Count = 0 Then
                    Throw New Exception(ERR_NEEDS_FILTERS)
                Else
                    If mvarFilters.Count = 0 Then
                        'lets see may be it's an 'nf'
                        If mvarFilters(0).ToString.Trim.ToLower <> "nf" Then
                            tmpstr = tmpstr & " WHERE " & mvarFilters(0)
                        End If
                    Else
                        'get the filters
                        If mvarFilters.Count <> 0 Then
                            tmpstr = tmpstr & " WHERE " & String.Join(" AND ", ToStringArray(mvarFilters.ToArray))
                        End If
                    End If
                End If
                'get the groups
                If mvarGroups.Count <> 0 Then
                    tmpstr = tmpstr & " GROUP BY " & String.Join(",", ToStringArray(mvarGroups.ToArray))
                    If mvarHaving.Count <> 0 Then
                        tmpstr = tmpstr & " HAVING " & String.Join(",", ToStringArray(mvarHaving.ToArray))
                    End If
                End If
                GetQuery = tmpstr
        End Select
    End Function
    Public Sub New()
        mvarMaxOutputFields = 32767
        mvarFields = New ArrayList
        mvarValues = New ArrayList
        mvarOrders = New ArrayList
        mvarGroups = New ArrayList
        mvarFilters = New ArrayList
        mvarHaving = New ArrayList
    End Sub
    Public Sub New(ByVal TableNameStr As String)
        mvarTableName = TableNameStr
    End Sub
    Private Sub pAddField(ByRef FieldName As String, ByRef IgnoreLimit As Boolean)
        Select Case mvarCommandType
            Case CommandQuery.cqSELECT, CommandQuery.cqUPDATE, CommandQuery.cqINSERT
                If Not IgnoreLimit Then
                    If mvarFields.Count > mvarMaxOutputFields Then
                        Exit Sub
                    End If
                End If
                mvarFields.Add(FieldName)
            Case Else
                Throw New Exception(ERR_INAPPROPRIATE_QUERY)
        End Select
    End Sub

    Protected Overrides Sub Finalize()
        MyBase.Finalize()
    End Sub
    Private Function EscapeStr(ByVal str As String) As String
        Dim m_str As String = ""
        If Not str Is Nothing Then
            If str.Length > 0 Then
                m_str = str.Replace("\", "\\")
                m_str = m_str.Replace(Convert.ToChar(34), "\""")
                m_str = m_str.Replace("'", "\'")
            End If
        End If
        Return m_str
    End Function
    Private Function ToStringArray(ByVal objArray() As Object) As String()
        Dim i As Integer
        Dim strArray(objArray.Length - 1) As String

        For i = 0 To objArray.Length - 1
            strArray(i) = objArray(i).ToString
        Next
        Return strArray
    End Function
End Class

By viewing downloads associated with this article you agree to the Terms of Service and the article's licence.

If a file you wish to view isn't highlighted, and is a text file (not binary), please let us know and we'll add colourisation support for it.

License

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

Share

About the Author

Elizalde G. Baguinon
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.
Follow on   Twitter

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.150326.1 | Last Updated 12 May 2011
Article Copyright 2008 by Elizalde G. Baguinon
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid