| 网站首页 | 业界新闻 | 小组 | 威客 | 人才 | 下载频道 | 博客 | 代码贴 | 在线编程 | 编程论坛
欢迎加入我们,一同切磋技术
用户名:   
 
密 码:  
共有 523 人关注过本帖
标题:ASP操作数据库的类 - 简易SQL操作类 v1.2
取消只看楼主 加入收藏
tml327
Rank: 1
等 级:新手上路
帖 子:510
专家分:0
注 册:2007-10-30
收藏
 问题点数:0 回复次数:0 
ASP操作数据库的类 - 简易SQL操作类 v1.2
简易SQL操作类 v1.2

QUOTE:
<%
'转发或修改时,请务必保留注释说明.
'            ____       __      
'    ___     \   \     / /      
'   |   |     \   \   / /      
'   |   |      \   \ / /         
'   |   |       \  // /        
'   |   | _     / //  \         
'   |   || |   / / \   \      
'   |   || |  / /   \   \        
'   |   || | /_/     \___\     
'   |   || |___                  
'   |___||_____| [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
%>
搜索更多相关主题的帖子: SQL操作 数据库 ASP QUOTE 注释 
2007-11-26 16:33
快速回复:ASP操作数据库的类 - 简易SQL操作类 v1.2
数据加载中...
 
   



关于我们 | 广告合作 | 编程中国 | 清除Cookies | TOP | 手机版

编程中国 版权所有,并保留所有权利。
Powered by Discuz, Processed in 0.036063 second(s), 8 queries.
Copyright©2004-2024, BCCN.NET, All Rights Reserved