ASP操作数据库的类 - 简易SQL操作类 v1.2
简易SQL操作类 v1.2QUOTE:
<%
'转发或修改时,请务必保留注释说明.
' ____ __
' ___ \ \ / /
' | | \ \ / / 领
' | | \ \ / /
' | | \ // / 星
' | | _ / // \
' | || | / / \ \ 动
' | || | / / \ \
' | || | /_/ \___\ 网
' | || |___
' |___||_____| [url=http://www.]www.[/url]
'
'版权所有.使用时请联系告知原作者.
'############################################################
'* 名称: 简易SQL操作类 v1.2
'* 说明: 简易生成SQL子句: INSERT / UPDATE / DELETE
'* 日期: 2007-8-8 状态: 就绪
'############################################################
'TODO:处理Request("...")
Class clsSQL
Private ErrMsg,bHasErr
Private sall,sp1,sp2,curo,curtab
Private TrueVal,curMemo,mVarType,mNull
Private MinNum,MaxNum
Private mConn,mRS
Private mIsAccess,mAccess,mSQLServer,mDatabase,mUserID,mPassword
Public AscLen
Private Sub Class_Initialize()
On Error Resume Next
ErrMsg="":bHasErr=False
Set sall=New cls_StrCat_S__
Set sp1=New cls_StrCat_S__
Set sp2=New cls_StrCat_S__
curo=0
TrueVal="-1"
mVarType=-1
mNull=False
curMemo=False
MinNum=Empty
MaxNum=Empty
Set mConn=conn
On Error GoTo 0
End Sub
Private Sub Class_Terminate()
Set sall=Nothing
Set sp1=Nothing
Set sp2=Nothing
Set mConn=Nothing
End Sub
Private Sub AddErr(s)
ErrMsg=ErrMsg&"SQL操作出错信息 - "&s&HTML_BR
bHasErr=True
End Sub
Public Function GetErr()
GetErr=ErrMsg
End Function
Public Sub ErrClear()
ErrMsg=""
bHasErr=False
End Sub
Public Property Get HasErr()
HasErr=bHasErr
End Property
Function SetRS(VarName)
Set VarName = Server.CreateObject("ADODB.Recordset")
Set SetRS=VarName
Set mRS=VarName
End Function
Function SetConn(VarName)
Set VarName = Server.CreateObject("ADODB.Connection")
Set SetConn=VarName
Set mConn=VarName
End Function
Public Property Get IsAccess()
IsAccess=mIsAccess
End Property
Public Property Let IsAccess(v)
If v Then
mIsAccess=True
TrueVal="-1"
Else
mIsAccess=False
TrueVal="1"
End If
End Property
Public Property Get Access()
Access=mAccess
End Property
Public Property Let Access(v)
mAccess=v
End Property
Public Property Get SQLServer()
SQLServer=mSQLServer
End Property
Public Property Let SQLServer(v)
mSQLServer=v
End Property
Public Property Get Database()
Database=mDatabase
End Property
Public Property Let Database(v)
mDatabase=v
End Property
Public Property Get UserID()
UserID=mUserID
End Property
Public Property Let UserID(v)
mUserID=v
End Property
Public Property Get Password()
Password=mPassword
End Property
Public Property Let Password(v)
mPassword=v
End Property
Function Open()
Dim connstr
On Error Resume Next
If Len(Access)>0 And IsAccess Then
c & Server.MapPath(Access)
Else
c & UserID &";Password=" & Password & ";Initial Catalog=" & Database& ";Data Source=" & SQLServer & ""
End If
mConn.Open connstr
If Err Then
AddErr Err.Description
Err.Clear
Else
Open=connstr
End If
On Error GoTo 0
End Function
Function Close()
On Error Resume Next
mConn.Close
Set mConn=Nothing
On Error GoTo 0
End Function
Public Function InitAutoID(Table,Field)
On Error Resume Next
InitAutoID=True
If IsAccess Then
mConn.Execute "ALTER TABLE " & Table & " ALTER COLUMN " & Field & " COUNTER (1, 1)"
Else
mConn.Execute "ALTER TABLE " & Table & " ALTER COLUMN " & Field & " IDENTITY (1, 1)"
End If
If Err Then
AddErr Err.Description
Err.Clear
InitAutoID=False
End If
On Error GoTo 0
End Function
Private Function StrLen(str)
Dim l,t,c,i
l=Len(str)
t=0
For i=1 to l
c=Abs(Asc(Mid(str,i,1)))
If c>255 Then
t=t+2
Else
t=t+1
End If
Next
StrLen=t
End Function
'开始组织SQL的INSERT语句
'obj.Insert "数据库表名"
Public Sub Insert(tabname)
If Len(tabname)>0 Then
curo=1
curtab=tabname
sall.Reset
sp1.Reset
sp2.Reset
sall()="INSERT INTO ["
sall()=tabname
sall()="] "
Else
curo=0
End If
End Sub
'开始组织SQL的UPDATE语句
'obj.Update "数据库表名"
Public Sub Update(tabname)
If Len(tabname)>0 Then
curo=2
curtab=tabname
sall.Reset
sp1.Reset
sp2.Reset
sall()="UPDATE ["
sall()=tabname
sall()="] "
Else
curo=0
End If
End Sub
'开始组织SQL的DELETE语句
'obj.Delete "数据库表名"
Public Sub Delete(tabname)
If Len(tabname)>0 Then
curo=3
curtab=tabname
sall.Reset
sp1.Reset
sp2.Reset
sall()="DELETE FROM ["
sall()=tabname
sall()="] "
Else
curo=0
End If
End Sub
'手动设置Item的数据类型
Public Sub TypeAuto()
mVarType=-1
End Sub
Public Sub TypeNum()
mVarType=2
End Sub
Public Sub TypeStr()
mVarType=8
End Sub
Public Sub TypeDate()
mVarType=7
End Sub
Public Sub TypeBool()
mVarType=11
End Sub
'对“字段”进行赋值的默认属性，用在 INSERT,UPDATE,DELETE 方法之后，基本用法：
'obj("字段名")=值或变量
'关于“值或变量”变量类型的说明:
'这里对变量类型的识别比较敏感，所以使用时尽量使用类型转换函数，例如：
'obj("num")=CLng(Request.Form("num"))
'“字符串型”，无须先替换单引号，本属性设置时会自动处理单引号
'如果当前操作为UPDATE，而又想做类似于 i=i+1 这样的语句时，请使用(例如)：
'obj("hits")=Array("hits+1")
Public Property Let Item(ByVal n,v)
Dim t
If mVarType=-1 Then
t=VarType(v)
Else
t=mVarType
End If
Select Case curo
Case 2 'UPDATE
If IsArray(v) Then
If sp1.IsInit Then
sp1()=" SET [" : sp1()=n : sp1()="] = " : sp1()=v(0) : sp1()= " "
Else
sp1()=" , [" : sp1()=n : sp1()="] = " : sp1()=v(0) : sp1()= " "
End If
Else
Select Case t
Case 1,2,3,4,5,6 '数值类型
If sp1.IsInit Then
sp1()=" SET [" : sp1()=n : sp1()="] = " : sp1()=v : sp1()= " "
Else
sp1()=" , [" : sp1()=n : sp1()="] = " : sp1()=v : sp1()= " "
End If
Case 7 '日期型
If sp1.IsInit Then
sp1()=" SET [" : sp1()=n : sp1()="] = #" : sp1()=Trim(v) : sp1()= "# "
Else
sp1()=" , [" : sp1()=n : sp1()="] = #" : sp1()=Trim(v) : sp1()= "# "
End If
Case 8 '字符串型
If sp1.IsInit Then
sp1()=" SET [" : sp1()=n : sp1()="] = '" : sp1()=Trim( Replace(v,"'","''") ) : sp1()= "' "
Else
sp1()=" , [" : sp1()=n : sp1()="] = '" : sp1()=Trim( Replace(v,"'","''") ) : sp1()= "' "
End If
Case 11 '布尔型
If sp1.IsInit Then
sp1()=" SET [" : sp1()=n : sp1()="] = "
If v Then
sp1()=TrueVal
Else
sp1()="0"
End If
sp1()=" "
Else
sp1()=" , [" : sp1()=n : sp1()="] = "
If v Then
sp1()=TrueVal
Else
sp1()="0"
End If
sp1()=" "
End If
End Select
End If
Case 1 'INSERT
Select Case t
Case 1,2,3,4,5,6 '数值类型
If sp1.IsInit Then
sp1()=" [" : sp1()=n : sp1()="] "
sp2()=" " : sp2()=v : sp2()=" "
Else
sp1()=" , [" : sp1()=n : sp1()="] "
sp2()=" , " : sp2()=v : sp2()=" "
End If
Case 7 '日期型
If sp1.IsInit Then
sp1()=" [" : sp1()=n : sp1()="] "
sp2()=" #" : sp2()=Trim(v) : sp2()="# "
Else
sp1()=" , [" : sp1()=n : sp1()="] "
sp2()=" , #" : sp2()=Trim(v) : sp2()="# "
End If
Case 8 '字符串型
If sp1.IsInit Then
sp1()=" [" : sp1()=n : sp1()="] "
sp2()=" '" : sp2()=Trim( Replace(v,"'","''") ) : sp2()="' "
Else
sp1()=" , [" : sp1()=n : sp1()="] "
sp2()=" , '" : sp2()=Trim( Replace(v,"'","''") ) : sp2()="' "
End If
Case 11 '布尔型
If sp1.IsInit Then
sp1()=" [" : sp1()=n : sp1()="] "
sp2()=" "
If v Then
sp2()=TrueVal
Else
sp2()="0"
End If
sp2()=" "
Else
sp1()=" , [" : sp1()=n : sp1()="] "
sp2()=" , "
If v Then
sp2()=TrueVal
Else
sp2()="0"
End If
sp2()=" "
End If
End Select
End Select
End Property
'返回生成后的SQL语句的字符串
'参数n为补充的SQL语句，通常是“WHERE ...”子句，记得要含有“WHERE”！
'在UPDATE,DELETE操作中一般都要带有“WHERE ...”子句
'用法示例：
'obj.INSERT "user"
'obj("Name")=Request.Form("Name")
'obj("Password")=MD5(Request.Form("Password"))
'conn.Execute obj("")
'
'obj.UPDATE "user"
'obj("Password")=MD5(Request.Form("Password"))
'conn.Execute obj("WHERE "&obj.SN("id","=",Request.Form("id"))
Public Default Property Get Item(n)
Select Case curo
Case 1
sall()=" ( "
sall()=sp1()
sall()=" ) VALUES ( "
sall()=sp2()
sall()=" ) "
sall()=n
Item=sall()
Case 2
sall()=sp1()
sall()=" "
sall()=n
Item=sall()
Case 3
sall()=" "
sall()=n
Item=sall()
End Select
End Property
'这个是上面属性的直接执行过程
Public Sub Exec(n)
On Error Resume Next
mConn.Execute(Item(n))
If Err Then
AddErr Err.Description
Err.Clear
End If
On Error GoTo 0
End Sub
'设置一个比较表达式(字符串型)
Public Function ES(ByVal n,o,v)
On Error Resume Next
If Len(o)=0 Then o="="
ES=" ["&n&"] "&o&" '"&Replace(v,"'","''")&"' "
On Error GoTo 0
End Function
'设置一个比较表达式(数值型)
Public Function EN(ByVal n,o,v)
On Error Resume Next
If Len(o)=0 Then o="="
If IsNumeric(v) Then
EN=" ["&n&"] "&o&" "&v&" "
Else
EN=" ["&n&"] "&o&" 0 "
AddErr "非法的数值类型"
End If
On Error GoTo 0
End Function
'返回上一个插入记录的“自动编号”字段的值
Public Function LastID()
On Error Resume Next
LastID=mConn.Execute("SELECT @@IDENTITY")(0)
If Err Then
AddErr Err.Description
Err.Clear
LastID=Empty
End If
On Error GoTo 0
End Function
Public Function Read(SQLz)
On Error Resume Next
mRS.Open SQLz,mConn,1,1
If mRS.EOF And mRS.BOF Then
Read=Null
Else
Read=mRS.GetRows
mRS.Close
End If
If Err Then
AddErr Err.Description
Err.Clear
End If
On Error GoTo 0
End Function
'Request的调用方式为：
'
'obj.QN("UserID")="id"
'obj.QS("UserID")="id"
'
'obj.FS("username")="name"
'obj.FS("password")="pass"
'obj.FN("password")="pass"
'字符串型是否可以为空(默认值:不能为空)
Public Sub CanNull()
mNull=True
End Sub
Public Sub NotNull()
mNull=False
End Sub
Public Property Let Max(v)
MaxNum=v
End Property
Public Property Let Min(v)
MinNum=v
End Property
Public Property Let QN(rsf,ref)
Dim v,l,f
f=False
On Error Resume Next
If Len(rsf)=0 Then rsf=ref
v=Request.QueryString(ref)
l=Len(v)
If Not (mNull And l=0) Then
If IsNumeric(v) And l>0 Then
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=False Then Execute"f=(" & v & ">=" & MinNum & ") And (" & v &"<=" & MaxNum & ")"
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=True Then Execute "f=(" & v & ">=" & MinNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=False Then Execute "f=(" & v & "<=" & MaxNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=True Then f=True
If f Then
mVarType=2
Item(rsf)=v
Else
AddErr "［" & ref & "］参数的数值范围不正确(最大值:" & MaxNum & " 最小值:" & MinNum & ")"
End If
Else
AddErr "［" & ref & "］参数的数据类型不是数值型"
End If
Else
If mNull Then
mVarType=1
If curo<>1 Then Item(rsf)="NULL"
End If
End If
On Error GoTo 0
End Property
Public Property Let QS(rsf,ref)
Dim v,l,f
f=False
On Error Resume Next
If Len(rsf)=0 Then rsf=ref
v=Request.QueryString(ref)
If AscLen Then l=StrLen(v) Else l=Len(v)
If mNull Then
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=False Then Execute"f=(" & l & ">=" & MinNum & ") And (" & l &"<=" & MaxNum & ")"
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=True Then Execute "f=(" & l & ">=" & MinNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=False Then Execute "f=(" & l & "<=" & MaxNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=True Then f=True
If f Then
If l=0 Then
mVarType=1 : v="NULL"
If curo<>1 Then Item(rsf)=v
Else
mVarType=8
Item(rsf)=v
End If
Else
AddErr "［" & ref & "］参数的长度范围不正确(最大值:" & MaxNum & " 最小值:" & MinNum & ")"
End If
Else
If l>0 Then
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=False Then Execute"f=(" & l & ">=" & MinNum & ") And (" & l &"<=" & MaxNum & ")"
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=True Then Execute "f=(" & l & ">=" & MinNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=False Then Execute "f=(" & l & "<=" & MaxNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=True Then f=True
If f Then
mVarType=8
Item(rsf)=v
Else
AddErr "［" & ref & "］参数的长度范围不正确(最大值:" & MaxNum & " 最小值:" & MinNum & ")"
End If
Else
AddErr "［" & ref & "］参数的字符串不能为空"
End If
End If
On Error GoTo 0
End Property
Public Property Let FN(rsf,ref)
Dim v,l,f
f=False
On Error Resume Next
If Len(rsf)=0 Then rsf=ref
v=Request.Form(ref)
l=Len(v)
If Not (mNull And l=0) Then
If IsNumeric(v) And l>0 Then
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=False Then Execute"f=(" & v & ">=" & MinNum & ") And (" & v &"<=" & MaxNum & ")"
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=True Then Execute "f=(" & v & ">=" & MinNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=False Then Execute "f=(" & v & "<=" & MaxNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=True Then f=True
If f Then
mVarType=2
Item(rsf)=v
Else
AddErr "［" & ref & "］参数的数值范围不正确(最大值:" & MaxNum & " 最小值:" & MinNum & ")"
End If
Else
AddErr "［" & ref & "］参数的数据类型不是数值型"
End If
Else
If mNull Then
mVarType=1
If curo<>1 Then Item(rsf)="NULL"
End If
End If
On Error GoTo 0
End Property
Public Property Let FS(rsf,ref)
Dim v,l,f
f=False
On Error Resume Next
If Len(rsf)=0 Then rsf=ref
v=Request.Form(ref)
If AscLen Then l=StrLen(v) Else l=Len(v)
If mNull Then
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=False Then Execute"f=(" & l & ">=" & MinNum & ") And (" & l &"<=" & MaxNum & ")"
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=True Then Execute "f=(" & l & ">=" & MinNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=False Then Execute "f=(" & l & "<=" & MaxNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=True Then f=True
If f Then
If l=0 Then
mVarType=1 : v="NULL"
If curo<>1 Then Item(rsf)=v
Else
mVarType=8
Item(rsf)=v
End If
Else
AddErr "［" & ref & "］参数的长度范围不正确(最大值:" & MaxNum & " 最小值:" & MinNum & ")"
End If
Else
If l>0 Then
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=False Then Execute"f=(" & l & ">=" & MinNum & ") And (" & l &"<=" & MaxNum & ")"
If IsEmpty(MinNum)=False And IsEmpty(MaxNum)=True Then Execute "f=(" & l & ">=" & MinNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=False Then Execute "f=(" & l & "<=" & MaxNum & ")"
If IsEmpty(MinNum)=True And IsEmpty(MaxNum)=True Then f=True
If f Then
mVarType=8
Item(rsf)=v
Else
AddErr "［" & ref & "］参数的长度范围不正确(最大值:" & MaxNum & " 最小值:" & MinNum & ")"
End If
Else
AddErr "［" & ref & "］参数的字符串不能为空"
End If
End If
On Error GoTo 0
End Property
End Class
'字符串拼接类本模块私用版
Class cls_StrCat_S__
Private aFStrings()
Private iFSPos,iFSLen,iFSIncr
Private Sub Class_Initialize()
On Error Resume Next
iFSIncr = STRCATBUF
If Err Then iFSIncr = 200 : Err.Clear
Reset
On Error GoTo 0
End Sub
Private Sub Class_Terminate()
Erase aFStrings
End Sub
Public Property Let Item(ByRef sData)
If iFSPos > iFSLen Then
iFSLen = iFSPos + iFSIncr
ReDim Preserve aFStrings(iFSLen)
End If
aFStrings(iFSPos) = sData
iFSPos = iFSPos + 1
End Property
Public Default Property Get Item()
Item = Join(aFStrings, "")
End Property
Public Property Get IsInit()
If iFSPos=0 Then IsInit=True Else IsInit=False
End Property
Public Sub Reset()
iFSPos = 0
iFSLen = iFSIncr
ReDim aFStrings(iFSLen)
End Sub
End Class
%>
