争怎路由网/网站教程/内容

一个ASP(VBScript)容易SQL语句构建“类”

网站教程2024-06-13 阅读
 

<%@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 = True
  End Function

  ' /----生成更新语句----/
  
  Public Function getUpdate()
   Dim strSQLTemplate : strSQLTemplate = "UPDATA {table} SET {updatecontent} {conditions}"
   If VarType(strTableName) = 1 Then Exit Function
   strSQLTemplate = Replace(strSQLTemplate, "{table}", strTableName)
   If Process(objFields, "UPDATE") <> False Then
    strSQLTemplate = Replace(strSQLTemplate, "{updatecontent}", Process(objFields, "UPDATE"))
   Else
    strSQLTemplate = Replace(strSQLTemplate, "{updatecontent}", "")
   End If
   If VarType(strCondition) <> 1 And strCondition <> "" Then
    strSQLTemplate = Replace(strSQLTemplate, "{conditions}", " WHERE " & strCondition)
   Else
    strSQLTemplate = Replace(strSQLTemplate, "{conditions}", "")
   End If
   getUpdate = strSQLTemplate
   blnState = True
  End Function

  ' /----生成删除语句----/

  Public Function getDelete()
   Dim strSQLTemplate : strSQLTemplate = "DELETE FROM {table} {conditions}"
   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
   getDelete = strSQLTemplate
   blnState = True
  End Function

  ' /----生成取记录数语句----/

  Public Function getCount()
   Dim strSQLTemplate : strSQLTemplate = "SELECT COUNT(*) FROM {table} {conditions}"
   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
   getCount = strSQLTemplate
   blnState = True
  End Function

  ' /----添加处理字段及相应值----/

  Public Sub AddField(ByVal sField, ByVal sValue)
   If VarType(sField) = 8 And Len(sField) > 0 And  (VarType(sValue) = 8 Or VarType(sValue) <> 1) Then
    objFields.Add ProcessField(sField), ProcessValue(sValue)
   End If
  End Sub
  
  ' /----添加条件字段及相应值----/

  Public Sub AddCField(ByVal sField, ByVal sValue)
   If VarType(sField) = 8 And Len(sField) > 0 And  VarType(sValue) = 8 And Len(sValue) > 0 Then
    Dim strCDTemplate : strCDTemplate = "{Field}{Operator}{value}"
    strCDTemplate = Replace(strCDTemplate,"{Field}", ProcessField(sField))
    strCDTemplate = Replace(strCDTemplate,"{Operator}"," " & strOperator & " ")
    If UCase(strOperator) = "LIKE" Then
     strCDTemplate = Replace(strCDTemplate,"{value}","'%" & Safe(sValue) & "%'")
    Else
     strCDTemplate = Replace(strCDTemplate,"{value}",ProcessValue(sValue))
    End If
    If VarType(strCondition) = 1 Then
     ReDim aContition(0)
     aContition(0) = strCDTemplate
     strCondition = Join(aContition, strLogic)
    Else
     strCondition = aContition(0)
     ReDim aContition(1)
     aContition(0) = strCondition
     aContition(1) = strCDTemplate
     strCondition = Join(aContition, strLogic)
     aContition(0) = strCondition
    End If
    'Response.Write strCondition & "<br>"
   End If
  End Sub

  ' /----指定表名或视图名----/

  Public Property Let Table(ByVal s)
   If VarType(s) = 8 And Len(s) > 0 Then strTableName = "[" & s & "]"
  End Property

  ' /----设定主键----/

  Public Property Let PrimaryKey(ByVal s)
   If VarType(s) = 8 And Len(s) > 0 Then strPKey =  "[" & s & "]"
  End Property

  ' /----主键排序----/

  Public Property Let Sort(ByVal s)
   If VarType(s) = 8 And Len(s) > 0 Then strPKeySort =  UCase(s)
  End Property

  ' /----更改条件子句操作符----/

  Public Property Let Operator(ByVal s)
   If VarType(s) = 8 And Len(s) > 0 Then strOperator =  UCase(s)
  End Property

  ' /----更改条件子句逻辑----/

  Public Property Let Logic(ByVal s)
   If VarType(s) = 8 And Len(s) > 0 Then strLogic =  " " & UCase(s) & " "
  End Property

  ' /----返回返执行状态----/

  Public Property Get ActionState
   ActionState = blnState
  End Property

 End Class

 ' /----演视开始----/

 Dim sql, T1, T2

 T1 = Timer()

 Set sql = New QuestStringBuilder
 
 sql.AddField "FRemark", "这是一个备注"
 sql.AddField "FName", "思源"
 sql.AddField "FCode", 120245
 sql.Operator = "<"
 sql.AddCField "FID", 1000
 sql.Logic = "or"
 sql.Operator = "like"
 sql.AddCField "FFriend", "思源"
 sql.Table = "FriendShip"
 sql.PrimaryKey = "FID"
 sql.Sort = "desc"

 Response.Write "<pre>"
 Response.Write "<font color=blue>基于ASP(VBS版)的简单SQL语句生成“类”:</font>"
 Response.Write "<br>" & vbCrLf
 Response.Write "查询语句:<font color=#666666>" & Sql.getSelect() & "</font>"
 Response.Write "<br>" & vbCrLf
 Response.Write "插入语句:<font color=#666666>" & Sql.getInsert() & "</font>"
 Response.Write "<br>" & vbCrLf
 Response.Write "更新语句:<font color=#666666>" & Sql.getUpdate() & "</font>"
 Response.Write "<br>" & vbCrLf
 Response.Write "删除语句:<font color=#666666>" &  Sql.getDelete() & "</font>"
 Response.Write "<br>" & vbCrLf
 Response.Write "取记录数:<font color=#666666>" &  Sql.getCount() & "</font>"

 'sql.Clear()

 Response.Write "<br>" & vbCrLf

 Response.Write "生成SQL语句是否成功:<font color=#666666>" & sql.ActionState & "</font>"
 
 Set sql = Nothing

 T2 = Timer()

 Response.Write "<br>" & vbCrLf
 
 Response.Write "程式执行时间:<font color=#666666>" & (T2 -  T2)*1000 & " 晕这个时间好像算不出来啊-_-!</font>"

 Response.Write "<br>" & vbCrLf

 Response.Write "程式作者:<font color=#666666>阿汉(思源)</font>"

 Response.Write "</pre>"

 Response.Write Err.Description
%>




……

相关阅读