IDC资讯中心
<%@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
%>
