<%@LANGUAGE="VBSCRIPT" CODEPAGE="936"%>
<% Option Explicit %>
<% Response.Buffer = True %>
<%
'''' /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
'''' ///
'''' ///  文件名: SQLBuilderForVbs
'''' ///  作用: 构建一些简单的SQL语句，结合在提交表单时使用，可以较方便
'''' ///  程式编写者: 曾思源
'''' ///  说明: 简单SQL语句构建“类”,VBS版,只要保留本注释段，无论是否涉及商业，您可以任意使用，转载或引用
'''' ///  日期: 2005-1-8
'''' ///_________________________________________________________________________________________________
'''' /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
%>
<%
 On Error Resume Next

 Class QuestStringBuilder

  Private objFields
  Private strTableName
  Private strPKey
  Private strPKeySort
  Private strCondition
  Private aContition()
  Private strOperator
  Private strLogic
  Private blnState

  ''''/-----初始化-----/

  Private Sub Class_Initialize()
   Set objFields = Server.CreateObject("Scripting.Dictionary")
   strTableName = Null
   strPKey = Null
   strPKeySort = Null
   strCondition = Null
   ReDim aContition(1)
   strOperator = "="
   strLogic = " AND "
   blnState = False
  End Sub

  Private Sub Class_Terminate()
   Set objFields = Nothing
   strTableName = Null
   strPKey = Null
   strPKeySort = Null
   strCondition = Null
   Erase aContition
   strOperator = Null
   strLogic = Null
   blnState = False
  End Sub

  '''' /----字段名处理----/

  Private Function ProcessField(ByVal sField)
   ProcessField = "[" & sField & "]"
  End Function

  '''' /-----字段值处理-----/

  Private Function ProcessValue(ByVal sValue)
   Dim tmpType : tmpType = VarType(sValue)
   Select Case tmpType
    Case 2,3,4,5,11 '''' 数字类型，布尔类型
     ProcessValue = sValue
    Case 8  '''' 字符类型
     ProcessValue = "''''" & Safe(sValue) & "''''"
    Case Else  '''' 其它类型
     ProcessValue = "''''" & Safe(sValue) & "''''"
   End Select
  End Function

  '''' /-----综合处理-----/

  Private Function Process(ByRef obj, ByVal strType)
   Dim Keys : Keys = obj.Keys
   Dim Items : Items = obj.Items
   Dim intCount : intCount = obj.Count
   Dim tmp()
   ReDim tmp(1)
   If intCount > 0 Then
    Dim tmpArray(), I
    ReDim tmpArray(intCount-1)
    For I=0 To intCount - 1
     tmpArray(I) = Keys(I) & "=" & Items(I)
    Next
    Select Case UCase(Trim(strType))
     Case "UPDATE"
      Process = Join(tmpArray, ", ")
     Case "SELECT"
      Process = Join(Keys, " ,")
     Case "INSERT"
      tmp(0) = Join(Keys, " ,")
      tmp(1) = Join(Items, " ,")
      Process = tmp
      Erase tmp
    End Select
    Erase tmpArray
   Else
    Select Case UCase(Trim(strType))
     Case "UPDATE"
      Process = False
     Case "SELECT"
      Process = "*"
     Case "INSERT"
      Process = tmp
    End Select
   End If
  End Function

  '''' /-----小小的安全处理-----/

  Private Function Safe(s)
   Safe = Replace(s,"''''","''''''''")
  End Function

  '''' /-----清空上一次输入的参数，但保留TableName-----/

  Public Sub Clear()
   objFields.RemoveAll
   ''''strTableName = Null
   strPKey = Null
   strPKeySort = Null
   strCondition = Null
   Erase aContition
   strOperator = "="
   strLogic = " AND "
   blnState = False
  End Sub

  '''' /----生成查询语句----/

  Public Function getSelect()
   Dim strSQLTemplate : strSQLTemplate = "SELECT {fields} FROM {table} {conditions} {orderby} {sort}"   
   strSQLTemplate = Replace(strSQLTemplate, "{fields}", Process(objFields, "SELECT"))
   If VarType(strTableName) = 1 Then Exit Function
   strSQLTemplate = Replace(strSQLTemplate, "{table}", strTableName)
   If VarType(strCondition) <> 1 And strCondition <> "" Then
    strSQLTemplate = Replace(strSQLTemplate, "{conditions}", " WHERE " & strCondition)
   Else
    strSQLTemplate = Replace(strSQLTemplate, "{conditions}", "")
   End If
   If VarType(strPKey) <> 1 And strPKey <> "" Then
    strSQLTemplate = Replace(strSQLTemplate, "{orderby}", " ORDER BY " & strPKey)
   Else
    strSQLTemplate = Replace(strSQLTemplate, "{orderby}", "")
   End If
   If VarType(strPKeySort) <> 1 And strPKeySort <> "" Then
    strSQLTemplate = Replace(strSQLTemplate, "{sort}", strPKeySort)
   Else
    strSQLTemplate = Replace(strSQLTemplate, "{sort}", "")
   End If
   getSelect = strSQLTemplate
   blnState = True
  End Function

  '''' /----生成插入语句----/

  Public Function getInsert()
   Dim strSQLTemplate : strSQLTemplate = "INSERT INTO {table}({fields}) VALUES({values})"
   If VarType(strTableName) = 1 Then Exit Function
   strSQLTemplate = Replace(strSQLTemplate, "{table}", strTableName)
   Dim srtInsertContent : srtInsertContent = Process(objFields, "INSERT")
   If VarType(srtInsertContent) <> 11 Then
    strSQLTemplate = Replace(strSQLTemplate, "{fields}", srtInsertContent(0))
    strSQLTemplate = Replace(strSQLTemplate, "{values}", srtInsertContent(1))    
   Else
    Exit Function
   End If
   getInsert = strSQLTemplate
   blnState

[1] [2] [3]  下一页