csqlmaker.cls

一个vb开发,可以动态生成SQL的"拼装类". 例如: sql.strsql="select ? from ? " sql.setFieldEx "field1" sql.setFieldE

VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "CSQLMaker"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False
Attribute VB_Ext_KEY = "SavedWithClassBuilder6" ,"Yes"
Attribute VB_Ext_KEY = "Top_Level" ,"Yes"
Attribute VB_Ext_KEY = "Member0" ,"Class1"
Attribute VB_Ext_KEY = "Member1" ,"CTitleMaker"
'local variable(s) to hold property value(s)
Private mvarStrSql As String 'local copy
Private itemDic() As String
Private fieldCount As Long
Private tmpStr As String
Private indicater As Long '计数器
Const char As String = "?"

'构造函数
Private Sub Class_Initialize()
    mvarStrSql = ""
    fieldCount = 0
    indicater = 0
End Sub

'析构函数
Private Sub Class_Terminate()
  Set mvarCTitleMaker = Nothing
End Sub



'说明：StrSql属性的set方法
'功能: 保存sql语句并统计出?的个数

Public Property Let StrSql(ByVal vData As String)
    Dim startPos As Long
    fieldCount = 0
    indicater = 0
    mvarStrSql = vData
    Do
        startPos = InStr(startPos + 1, mvarStrSql, char, vbTextCompare)
        If startPos <> 0 Then fieldCount = fieldCount + 1
    Loop Until startPos = 0
    If fieldCount <> 0 Then ReDim itemDic(fieldCount)
End Property

'说明：StrSql属性的get方法
'功能: 生成sql语句的输出结果并返回该结果
Public Property Get StrSql() As String
    Call MakeResultStr
    StrSql = tmpStr
    indicater = 0
End Property

'
Public Sub Reset()
    indicater = 0
End Sub


Public Sub SetDate(ByVal Position As Long, ByVal dDate As Date)
    Call SetString(Position, Trim(Str(dDate)))
End Sub

Public Sub SetString(ByVal Position As Long, ByVal sStr As String)
    itemDic(Position) = "'" & sStr & "'"
End Sub
Public Sub SetField(ByVal Position As Long, ByVal sStr As String)
    itemDic(Position) = sStr
End Sub

Public Sub SetInteger(ByVal Position As Long, ByVal lNum As Long)
    itemDic(Position) = Trim(Str(lNum))
End Sub

Public Sub SetBit(ByVal Postion As Long, ByVal bVal As Boolean)
    itemDic(Position) = Trim(Str(Abs(CInt(bVal))))
End Sub

'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'''''''''''''''''''''EX版
''''''''''''''''
Public Sub SetDateEx(ByVal dDate As Date)
    Call SetString(GetIndicater, Trim(Str(dDate)))
End Sub

Public Sub SetStringEx(ByVal sStr As String)
    itemDic(GetIndicater) = "'" & sStr & "'"
End Sub
Public Sub SetFieldEx(ByVal sStr As String)
    itemDic(GetIndicater) = sStr
End Sub

Public Sub SetIntegerEx(ByVal lNum As Long)
    itemDic(GetIndicater) = Trim(Str(lNum))
End Sub

Public Sub SetBitEx(ByVal bVal As Boolean)
    itemDic(GetIndicater) = Trim(Str(Abs(CInt(bVal))))
End Sub

Private Function GetIndicater() As Long
    If indicate < fieldCount Then
        indicater = indicater + 1
        GetIndicater = indicater
    Else
        Err.Raise 1080, "", "SQL生成器的下标越界,越界值" & indicate & "/额定值" & fieldCount
    End If
End Function
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
Private Function FindThePos(ByVal Position As Long) As Long
Dim startPos As Long
    If Position = 0 Then startPos = 0
    
    If Position >= 1 Then startPos = InStr(1, mvarStrSql, char, vbTextCompare)
    
    If Position > 1 Then
        For i = 2 To Position
            startPos = InStr(startPos + 1, mvarStrSql, char, vbTextCompare)
        Next
    End If
    '当代码执行到此时
    '无论postion的值是多少
    'startPos中记录的就是返回值
    FindThePos = startPos
End Function


Private Function MakeResultStr()
    Dim key As String       '用于定位itemDic字典
    Dim begPos As Long   '截取原字段的开始位置
    Dim strLen As Long   '截取长度
    Dim FindPos As Long  '临时存放问号所在位置
    
    Dim itemValue As String
    
    '有字典项目时,进行以下处理
        '初始化临时变量
        begPos = 1
        strLen = 0
        tmpStr = ""
        '利用循环将原字符串分割,并与字典项目重组到tmpStr中
        '以下代码为此模块的关键
            Dim i As Long
            For i = 1 To fieldCount Step 1
                key = Str(i)
                '根据i判断该位置的？是否有值
                If Not (itemDic(i) = "") Then
                    '有值则先取出改值
                        itemValue = itemDic(i)
                    '取得?在原字符串的位置
                    FindPos = FindThePos(i)
                    '将结束位置定位到?之前
                    strLen = FindPos - begPos
                    tmpStr = tmpStr _
                             & Mid(mvarStrSql, begPos, strLen) _
                             & itemValue
                    begPos = FindPos + 1
                End If
            Next
            strLen = Len(mvarStrSql) - begPos + 1
            tmpStr = tmpStr & Mid(mvarStrSql, begPos, strLen)
End Function