clsupdate.cls

visual basic 关于数据查询操作练习很好的工具

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsUPDATE"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Option Explicit

'This class provides an easier and more consistent way to build a SQL UPDATE statement
'Developed by Daniel Chirca

Private Const cnstUPDATE = "UPDATE"
Private Const cnstSET = "SET"
Private Const cnstWHERE = "WHERE"
Private Const cnstAND = "AND"
Private Const cnstOR = "OR"
Private Const cnstNOT = "NOT"
Private Const cnstSep = " "

Private m_TableName As String
Private m_TableAlias As String
Private m_CustomCondition As String
Private m_UPDATE As String

Private Type tValueType
  vValue As Variant
  eComparedDataType As eDataType
  bCompareValueNotTranslated As Boolean
End Type

Private Type tConditionType
  sColumnName As String
  eOperator As eOperatorType
  tValue As tValueType
End Type

Private Type tUpdateType
  sColumnName As String
  tValue As tValueType
End Type

Private m_Updates() As tUpdateType
Private m_Conditions() As tConditionType

Public Sub AddTable(strTableName As String, Optional strTableAlias As String)
  If Len(strTableName) > 0 Then
    m_TableName = strTableName
    
    If Not IsMissing(strTableAlias) And Len(strTableAlias) > 0 Then
      m_TableAlias = strTableAlias
    Else
      m_TableAlias = vbNullString
    End If
  End If
End Sub

Public Sub AddConditionColumn(strConditionColumnName As String, _
                        eOperator As eOperatorType, _
                        varVal As Variant, _
                        Optional eValDataType As eDataType = eString, _
                        Optional bDoNotTranslate As Boolean = False)

  If Len(strConditionColumnName) > 0 Then
    ReDim Preserve m_Conditions(0 To UBound(m_Conditions) + 1)
    
    m_Conditions(UBound(m_Conditions)).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
  End If
    
End Sub

Public Sub AddUpdateColumn(strUpdateColumnName As String, _
                        varVal As Variant, _
                        Optional eValDataType As eDataType = eString, _
                        Optional bDoNotTranslate As Boolean = False)

  If Len(strUpdateColumnName) > 0 Then
    ReDim Preserve m_Updates(0 To UBound(m_Updates) + 1)
    
    m_Updates(UBound(m_Updates)).sColumnName = strUpdateColumnName
    m_Updates(UBound(m_Updates)).tValue.vValue = varVal
    
    If Not IsMissing(eValDataType) Then
      m_Updates(UBound(m_Updates)).tValue.eComparedDataType = eValDataType
    Else
      m_Updates(UBound(m_Updates)).tValue.eComparedDataType = eString
    End If
    
    If Not IsMissing(bDoNotTranslate) Then
      m_Updates(UBound(m_Updates)).tValue.bCompareValueNotTranslated = bDoNotTranslate
    Else
      m_Updates(UBound(m_Updates)).tValue.bCompareValueNotTranslated = False
    End If
  End If
    
End Sub

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

Public Function BuildUPDATE() As String
  On Error GoTo ErrorHandler
    
  Dim StrTemp As String
  Dim i As Integer
  
  StrTemp = StrTemp + cnstUPDATE + cnstSep
  StrTemp = StrTemp + m_TableName
  If Len(m_TableAlias) > 0 Then
    StrTemp = StrTemp + cnstSep + m_TableAlias
  End If
  
  If UBound(m_Updates) > 0 Then
    StrTemp = StrTemp + cnstSep + cnstSET + cnstSep
    For i = 1 To UBound(m_Updates)
      With m_Updates(i)
        If m_TableAlias <> vbNullString Then
          StrTemp = StrTemp + m_TableAlias + "."
        Else
          StrTemp = StrTemp + m_TableName + "."
        End If
        
        StrTemp = StrTemp + .sColumnName
        StrTemp = StrTemp + cnstSep + "=" + cnstSep
      
        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_Updates) Then
          StrTemp = StrTemp + "," + cnstSep
        End If
      End With
    Next
  End If
  
  If Len(m_CustomCondition) > 0 Then
    StrTemp = StrTemp + cnstSep + cnstWHERE + cnstSep + m_CustomCondition + 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 m_TableAlias <> vbNullString Then
            StrTemp = StrTemp + m_TableAlias + "."
          Else
            StrTemp = StrTemp + m_TableName + "."
          End If
          StrTemp = StrTemp + .sColumnName
          
          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
  
  BuildUPDATE = StrTemp
  
  Exit Function
  
ErrorHandler:
  On Error Resume Next
End Function

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

  m_UPDATE = ""

  m_TableName = ""
  m_TableAlias = ""
  m_CustomCondition = ""
  
  ReDim m_Conditions(0)
  ReDim m_Updates(0)
End Sub

Public Sub ClearSQL()
  ReDim m_Updates(0)
  ReDim m_Conditions(0)
End Sub

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