一个ASP(VBScript)简单SQL语句构建“类”
作者:曾思源 来源:CSDN 时间:2008-03-12 07:08:00
作用: 构建一些简单的SQL语句,结合在提交表单时使用,可以较方便
<%@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
%>
标签:类,sql,表单,asp
0
投稿
猜你喜欢
运用mysqldump 工具时需要注意的问题
2024-01-20 07:11:56
使用python Telnet远程登录执行程序的方法
2022-12-22 11:56:57
vuejs实现ready函数加载完之后执行某个函数的方法
2024-05-29 22:48:43
js将URL网址转为16进制加密与解密函数
2024-02-26 13:37:30
如何基于Django实现上下文章跳转
2022-02-11 02:38:05
省市级联菜单的可用性
2009-02-24 16:32:00
通过Python pyecharts输出保存图片代码实例
2021-09-14 22:26:11
对pandas replace函数的使用方法小结
2022-07-04 15:20:24
Go语言中的自定义类型你了解吗
2024-02-06 00:11:36
Go语言切片常考的面试真题解析
2024-04-30 10:07:16
巧用mysql提示符prompt清晰管理数据库的方法
2024-01-24 14:05:07
python数据处理 根据颜色对图片进行分类的方法
2022-02-27 08:41:37
MySQL的指定范围随机数函数rand()的使用技巧
2024-01-17 15:25:50
Python如何发送与接收大型数组
2022-07-26 06:54:54
PyCharm更改字体和界面样式的方法步骤
2021-12-24 09:15:25
基于python list对象中嵌套元组使用sort时的排序方法
2022-11-04 03:38:21
全网首秀之Pycharm十大实用技巧(推荐)
2023-08-09 16:11:00
Python的三个重要函数详解
2022-05-19 04:58:45
教你在pycharm中使用tensorflow的方法
2021-07-07 19:09:27
Window下安装JDK1.8+Tomcat9.0.27+Mysql5.7.28的教程图解
2024-01-26 22:24:12