|
|
''' <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.
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.
Submit an article or tip