|
<%@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
'''' /-- [1] [2] [3] 下一页 | 
注：本站部分文章源于互联网，版权归原作者所有！如有侵权，请原作者与本站联系，本站将立即删除！ 本站文章除特别注明外均可转载，但需注明出处！ [MinTao学以致用网] 