clsselect.cls

由visual basic编写的的简单SQL数据库系统

    m_Conditions(UBound(m_Conditions)).tConditionColumn.tColumn.sColumnName = strConditionColumnName
    m_Conditions(UBound(m_Conditions)).eOperator = eOperator
    m_Conditions(UBound(m_Conditions)).tValue.vValue = varVal
    
    If Not IsMissing(eValDataType) Then
      m_Conditions(UBound(m_Conditions)).tValue.eComparedDataType = eValDataType
    Else
      m_Conditions(UBound(m_Conditions)).tValue.eComparedDataType = eString
    End If
    
    If Not IsMissing(bDoNotTranslate) Then
      m_Conditions(UBound(m_Conditions)).tValue.bCompareValueNotTranslated = bDoNotTranslate
    Else
      m_Conditions(UBound(m_Conditions)).tValue.bCompareValueNotTranslated = False
    End If
    
    If Not IsMissing(strColumnAlias) And Len(strColumnAlias) > 0 Then
      m_Conditions(UBound(m_Conditions)).tConditionColumn.tColumn.sColumnAlias = strColumnAlias
    Else
      m_Conditions(UBound(m_Conditions)).tConditionColumn.tColumn.sColumnAlias = vbNullString
    End If
    
    If Not IsMissing(strTableName) And Len(strTableName) > 0 Then
      bFound = False
      For i = 1 To UBound(m_Tables)
        If strTableName = m_Tables(i).sTableName Then
          m_Conditions(UBound(m_Conditions)).tConditionColumn.tTable.sTableName = m_Tables(i).sTableName
          m_Conditions(UBound(m_Conditions)).tConditionColumn.tTable.sTableAlias = m_Tables(i).sTableAlias
          bFound = True
          Exit For
        End If
      Next i
      If Not bFound Then
        m_Conditions(UBound(m_Conditions)).tConditionColumn.tTable = m_NullTable
      End If
    Else
      If Not IsMissing(strTableAlias) And Len(strTableAlias) > 0 Then
        bFound = False
        For i = 1 To UBound(m_Tables)
          If strTableAlias = m_Tables(i).sTableAlias Then
            m_Conditions(UBound(m_Conditions)).tConditionColumn.tTable.sTableName = m_Tables(i).sTableName
            m_Conditions(UBound(m_Conditions)).tConditionColumn.tTable.sTableAlias = m_Tables(i).sTableAlias
            bFound = True
            Exit For
          End If
        Next i
        If Not bFound Then
          m_Conditions(UBound(m_Conditions)).tConditionColumn.tTable = m_NullTable
        End If
      Else
        m_Conditions(UBound(m_Conditions)).tConditionColumn.tTable = m_NullTable
      End If
    End If
  End If
End Sub

Public Sub AddCustomCondition(strCustomCondition As String)
  If Len(strCustomCondition) > 0 Then
    m_CustomCondition.sCustomCondition = strCustomCondition
  End If
End Sub

Public Function BuildSELECT() As String
  On Error GoTo ErrorHandler
    
  Dim StrTemp As String
  Dim i As Integer
  
  StrTemp = StrTemp + cnstSELECT + cnstSep
  For i = 1 To UBound(m_SelectColumns)
    With m_SelectColumns(i)
      If .tTable.sTableAlias <> vbNullString Then
        StrTemp = StrTemp + .tTable.sTableAlias + "."
      ElseIf .tTable.sTableName <> vbNullString Then
        StrTemp = StrTemp + .tTable.sTableName + "."
      End If
      StrTemp = StrTemp + .tColumn.sColumnName
      If .tColumn.sColumnAlias <> vbNullString Then
        StrTemp = StrTemp + " " + cnstAS + " " + .tColumn.sColumnAlias
      End If
      If i <> UBound(m_SelectColumns) Then
        StrTemp = StrTemp + ", "
      End If
    End With
  Next
  
  StrTemp = StrTemp + cnstSep + cnstFROM + cnstSep
  For i = 1 To UBound(m_Tables)
    With m_Tables(i)
      StrTemp = StrTemp + .sTableName + cnstSep
      If .sTableAlias <> vbNullString Then
        StrTemp = StrTemp + " " + .sTableAlias
      End If
      If i <> UBound(m_Tables) Then
        StrTemp = StrTemp + ", " + cnstSep
      End If
    End With
  Next
  
  If Len(m_CustomCondition.sCustomCondition) > 0 Then
    StrTemp = StrTemp + cnstSep + cnstWHERE + cnstSep + m_CustomCondition.sCustomCondition + cnstSep
  Else
    If UBound(m_Conditions) > 0 Then
      StrTemp = StrTemp + cnstSep + cnstWHERE + cnstSep
      For i = 1 To UBound(m_Conditions)
        With m_Conditions(i)
          If .tConditionColumn.tTable.sTableAlias <> vbNullString Then
            StrTemp = StrTemp + .tConditionColumn.tTable.sTableAlias + "."
          ElseIf .tConditionColumn.tTable.sTableName <> vbNullString Then
            StrTemp = StrTemp + .tConditionColumn.tTable.sTableName + "."
          End If
          If .tConditionColumn.tColumn.sColumnAlias <> vbNullString Then
            StrTemp = StrTemp + .tConditionColumn.tColumn.sColumnAlias
          ElseIf .tConditionColumn.tColumn.sColumnName <> vbNullString Then
            StrTemp = StrTemp + .tConditionColumn.tColumn.sColumnName
          End If
          
          Select Case .eOperator
            Case cEqual
              StrTemp = StrTemp + cnstSep + "=" + cnstSep
            Case cLowerEqual
              StrTemp = StrTemp + cnstSep + "<=" + cnstSep
            Case cEqualGreater
              StrTemp = StrTemp + cnstSep + ">=" + cnstSep
            Case cLower
              StrTemp = StrTemp + cnstSep + "<" + cnstSep
            Case cGreater
              StrTemp = StrTemp + cnstSep + ">" + cnstSep
            Case cNonEqual
              StrTemp = StrTemp + cnstSep + "<>" + cnstSep
            Case cLIKE
              StrTemp = StrTemp + cnstSep + "LIKE" + cnstSep
            Case cNULL
              StrTemp = StrTemp + cnstSep + "IS NULL" + cnstSep
            Case cNOTNULL
              StrTemp = StrTemp + cnstSep + "IS NOT NULL" + cnstSep
          End Select
        
          If .tValue.bCompareValueNotTranslated = True Then
            StrTemp = StrTemp + CStr(.tValue.vValue)
          Else
            If Len(CStr(.tValue.vValue)) > 0 Then
              Select Case .tValue.eComparedDataType
                Case eString
                  StrTemp = StrTemp + "'" + CStr(.tValue.vValue) + "'"
                Case eInteger
                  StrTemp = StrTemp + Format(CStr(.tValue.vValue), "General number")
                Case eDate
                  StrTemp = StrTemp + "'" + Format(CStr(.tValue.vValue), "mm/dd/yyyy") + "'"
                Case eDecimal
                  StrTemp = StrTemp + Format(CStr(.tValue.vValue), "Standard")
                Case eBool
                  If CBool(.tValue.vValue) = True Then
                    StrTemp = StrTemp + CStr(1)
                  Else
                    StrTemp = StrTemp + CStr(0)
                  End If
              End Select
            End If
          End If
          
          If i <> UBound(m_Conditions) Then
            StrTemp = StrTemp + cnstSep + cnstAND + cnstSep
          End If
        End With
      Next
    End If
  End If
  
  If UBound(m_Order) > 0 Then
    StrTemp = StrTemp + cnstSep + cnstORDERBY + cnstSep
    For i = 1 To UBound(m_Order)
      With m_Order(i)
        If .tOrderColumn.tTable.sTableAlias <> vbNullString Then
          StrTemp = StrTemp + .tOrderColumn.tTable.sTableAlias + "."
        ElseIf .tOrderColumn.tTable.sTableName <> vbNullString Then
          StrTemp = StrTemp + .tOrderColumn.tTable.sTableName + "."
        End If
        If .tOrderColumn.tColumn.sColumnAlias <> vbNullString Then
          StrTemp = StrTemp + .tOrderColumn.tColumn.sColumnAlias
        ElseIf .tOrderColumn.tColumn.sColumnName <> vbNullString Then
          StrTemp = StrTemp + .tOrderColumn.tColumn.sColumnName
        End If
        Select Case .eOrderByType
          Case cASC
            StrTemp = StrTemp + cnstSep + "ASC"
          Case cDESC
            StrTemp = StrTemp + cnstSep + "DESC"
        End Select
        If i <> UBound(m_Order) Then
          StrTemp = StrTemp + "," + cnstSep
        End If
      End With
    Next
  End If
  
  BuildSELECT = StrTemp
  
  Exit Function
  
ErrorHandler:
  On Error Resume Next
End Function

Private Sub Class_Initialize()
  Debug.Print "clsSELECT class instance started."

  m_NullTable.sTableName = vbNullString
  m_NullTable.sTableAlias = vbNullString

  m_SELECT = ""

  ReDim m_Tables(0)
  ReDim m_SelectColumns(0)
  ReDim m_AggregateColumns(0)
  ReDim m_OrderColumns(0)
  ReDim m_Conditions(0)
  ReDim m_Order(0)
End Sub

Public Sub ClearSQL()
  ReDim m_Tables(0)
  ReDim m_SelectColumns(0)
  ReDim m_AggregateColumns(0)
  ReDim m_OrderColumns(0)
  ReDim m_Conditions(0)
  ReDim m_Order(0)
End Sub

Private Sub Class_Terminate()
  Debug.Print "clsSELECT class instance closed."
End Sub