<% @ Language = "vbscript" codepage = "936"%> <% option explicit%> <% response.buffer = true%> <% '/' /// '/// file name: sqlbuilderforvbs' //// Role: Build some simple SQL statement, combine to use when submitting the form, can be more convenient to '/////> Description: Simple SQL statement builds "class", VBS version, just keep this note segment, whether commercial, you are free to use, reproduce or quote '/// date: 2005-1-8' /// _________________________________________________________________________________________________ '/%> <% On Error Resume Next
Class QueststringBuilder
Private Objfields Private Strtablename Private Strivate Strcondition Private AContition () Private Stroperator Private Strlogic Private BLNState
'/-----initialization-----/
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
'/ ---- field name processing ---- /
Private function processfield (byval sfield) processfield = "[" & sfield & "]" End function
'/ ----- Field value processing ----- /
Private Function ProcessValue (ByVal sValue) Dim tmpType: tmpType = VarType (sValue) Select Case tmpType Case 2,3,4,5,11 'numeric type, boolean ProcessValue = sValue Case 8' character type ProcessValue = " '" & Safe (Svalue) & "'" Case Else' Other Type ProcessValue = "'" & Safe (Svalue) & "" End Select End Function' / ---- Integrated Processing ----- /
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 , ")) = TMP ERASE TMP END SELECT ERASE TMPARRAY ELSE SELECT ERASE (TRIM (STRTYPE)) Case" Update "Process = false case" select "process =" * "case" insert "process = tmp end select end IF end function
'/ ----- Small safety treatment ----- /
Private function safe (s) safe = replace (s, "'", "' ') end function
'/ ----- Clear the parameters of the previous input, but keep Tablename ----- /
Public Sub Clear () objFields.RemoveAll 'strTableName = Null strPKey = Null strPKeySort = Null strCondition = Null Erase aContition strOperator = "=" strLogic = "AND" blnState = False End Sub' / ---- generated query --- - /
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 f getselect = strsqltemplate BLNSTATE = True End Function
'/ ---- Generate Insert Statements ---- /
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 '/ ---- generate an updated statement ---- / 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
'/ ---- Generate Delete Statements ---- /
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 '/ ---- Generate Recording Documentary ---- /
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
'/ ---- Add Process field and the corresponding value ---- /
Public Sub Addfield (Byval Sfield, Byval Svalue) ife (sfield) = 8 And len (sfield)> 0 and (varType (svalue) = 8 or Vartype <> 1) Then objfields.add processfield (sfield), ProcessValue (Svalue) end if End sub '/ ---- Add condition field and corresponding value ---- /
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 ififf 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 & "
" End if End Sub' / - - Specify a table name or view name ---- /
Public Property Let Table (Byval S) ife (s) = 8 And Len (s)> 0 Then StrtABLENAME = "[" & S & "]" End Property
'/ ---- Set the primary key ---- /
Public Property Let PrimaryKey (Byval S) ife (s) = 8 and Len (s)> 0 Then Strpkey = "[" & S & "]" End Property
'/ ---- Main button Sort ---- /
Public Property Let Sort (Byval S) ife (s) = 8 And Len (s)> 0 Then Strpkeysort = ucase (s) End Property
'/ ---- Change the condition clause operator ---- /
Public Property Let Operator (Byval S) if Vartype (s) = 8 and Len (s)> 0 Then stroperator = ucase (s) end protection '/ ---- Change condition clause logic ---- /
Public property let logic (byval s) if Vartype (s) = 8 and Len (s)> 0 Then strogic = "" & ucase (s) & "" End Property
'/ ---- Return to the Status ---- /
Public property Get ActionState ActionState = BLNSTATE End Property
END CLASS
'/ ---- Dark Start ---- /
DIM SQL, T1, T2
T1 = Timer ()
SET SQL = New QueststringBuilder Sql.addfield "Fremark", "This is a note" SQL.Addfield "FName", "Siyuan" SQL.Addfield "fcode", 120245 SQL.Operator = "<" SQL.Addcfield "FID", 1000 sql.logic = "or" sql.operator = "like" sql.addcfield "ffriend", "Siyuan" sql.table = "friendship" sql.primarykey = "fid" sql.sort = "desc"
Response.Write "
" response.write " Simple SQL statement based on ASP (VBS version) Generates" Class ": " Response.write "
" & Vbcrlf Resonse. Write "query statement: " & sql.getSelect () & "" response.write "
" & vbcrponse.write "insert statement: "& SQL.GetInsert () &" "Response.write"
"& vbcrf response.write" update statement: & sql.getupdate () & " "Response.write"
"& vbrlf response.write" delete statement: "& sql.getdelete () &" "Response.write"
"& vbcrlf Response.write "Number of records: " & SQL.Getcount () & "" sql.clear ()Response.write "
" & VBCRLFResponse.write "Generates whether the SQL statement is successful: " & SQL.ActionState & "" set SQL = Nothing
T2 = Timer ()
Response.write "
" & vbcrlf response.write "program execution time: " & (t2 - t2) * 1000 & "Halo this time seems to be not coming out --_-! "Response.write "
" & VBCRLFResponse.Write "program Author: A Han (the source) "
Response.write ""
Response.write Err.Description%> Dark address: http://www.6dz.net/portfolio/sqlbuilderforvbs.asp
