SqlBuilder - simple class for building SQL statements dynamically

Access XP, 2000, 97, ...; VB 6, 5, ...

Download SqlBuilder v.1.0.0, (1744 bytes):

SqlBuilder class helps you manage construction of SQL statements in your code in those cases, when select list, list of tables, and/or search conditions, ..., depend on parameters, known at run-time only. Let's start from example.

Example of SqlBuilder usage

'DictID, DictType and DictIsArchived are some externally defined parameters
Dim Sql1 As SqlBuilder
Dim strSql As String

  Set Sql1 = New SqlBuilder
  Sql1.AppendSelectList "Dict.DictLevel, Dict.DictParentID, DictL.*"
  Sql1.AppendFrom "Dict Inner Join DictL On Dict.DictID=DictL.DictID And DictL.LanguageID=" & FNumSql(lang)
  
  If DictID <> 0 Then
    'filter for DictID column is on!
    Sql1.AppendWhere "Dict.DictID=" & FNumSql(DictID)
  End If
  If DictType <> gconDictType_all Then
    ' ... AND exact "DictType"
    Sql1.AppendWhere "Dict.DictTypeID=" & FNumSql(DictType)
  End If
  
  'additional columns and table(s) are required from some types of data
  Select Case DictType
  Case gconDictType_EntPropType
    'It's simple to join another table and to add another column to the select list!
    Sql1.AppendFrom "Inner Join EntPropType On Dict.DictID=EntPropType.EntPropTypeID"
    Sql1.AppendSelectList "EntPropType.ValueTypeID"
  End Select
  
  If Not DictIsArchived Then
    ' ... AND only items, that are not archived 
    Sql1.AppendWhere "Dict.DictIsArchived=0"
  End If
  
  Sql1.OrderBy = "DictL.DictOrder"

  'Now all run-time parameters are collected, let's get SQL statement
  strSql = Sql1.ToString

I think, that you've got a clue. For details please see source code of SqlBuilder.cls.

Author: Yuri Volkov
Last changed: 2003-09-30