ASP操作MSQL类

<%
'=========================================================
'類名: Ms SQL 操作類
'作者: Athrun
'版本: V 0.1
'開發日期:2012-4-14
'修改日期:2012-4-14
'Email: athrunsoft@gmail.com
'Dim Db,ProcName,Prams,Rs,OutPut,Sql
'Set Db = New DbHelperSQL
'ProcName = "SP_Name"
'Db.ParameterClear()
'Db.ParameterAdd "@EID",3,2,4,EID
'Db.ProcExecute(ProcName)
'=========================================================
'Const dbIP = "127.0.0.1\SQLEXPRESS"
'Const dbname="HandyMan"
'Const dbuid="sa"
'Const dbpwd="sasa"
'Const AdoConnectionTimeout = 15
'Const AdoCommandTimeout = 30

Class DbHelperSQL
    Private Conn,Rs,Cmd,Pram,Prams,RecordsAffected
    Private AdoConnectionTimeout_,AdoCommandTimeout_
    
    Public Property Let LetCmdTimeout(ByVal Value)
        AdoCommandTimeout_ = Value
    End Property
    
    Public Property Get GetCmdTimeout()
        GetCmdTimeout = AdoCommandTimeout_
    End Property
    
    Public Property Get GetRecordsAffected()
        GetRecordsAffected = RecordsAffected
    End Property
    
    Private Sub Class_Initialize()
        AdoConnectionTimeout_ = AdoConnectionTimeout
        AdoCommandTimeout_ = AdoCommandTimeout
        Prams = Array()
        Call ReSetRecordsAffected()
        Call DbOpen()
        Set Rs = Server.CreateObject("ADODB.Recordset")    
        Set Cmd = Server.CreateObject("ADODB.Command")    
    End Sub
    
    Private Sub Class_Terminate()
        Call DbClose()
        Call ParameterClear()
    End Sub
    
    Private Sub DbOpen()
        On Error Resume Next
        Application.Lock
        Dim connString
        Set Conn = Server.CreateObject("ADODB.Connection")
        Conn.ConnectionTimeout = AdoConnectionTimeout_
        Conn.CommandTimeout = AdoCommandTimeout_
        ''sql2000
        ''connString = "Driver={sql server};uid=" + dbuid + ";pwd=" + dbpwd + ";database=" + dbname + ";server=" + dbIP + ";"
        ''sql2008
        connString = "Driver={SQL Server Native Client 10.0};uid=" + dbuid + ";pwd=" + dbpwd + ";database=" + dbname + ";server=" + dbIP + ";"
        Conn.Open connString
        If Err Then
            Application.UnLock()
            Response.Clear()
            Response.Write("<script language='javascript'>alert('Database connection error.');</script>")
            Response.End()
        End If
        Application.UnLock
    End Sub
    
    Private Sub DbClose()
        ''If Conn.State = 1 Then Conn.Close()
    End Sub
    
    Private Sub ReSetRecordsAffected()
        RecordsAffected = -1
    End Sub
        
    Public Sub ParameterAdd(Name_, Type_, Direction_, Size_, Value_)
        On Error Resume Next
        Dim arrLength
        arrLength=Ubound(Prams)+1
        ReDim Preserve Prams(arrLength)
        Set Prams(arrLength)=Cmd.CreateParameter(Name_, Type_, Direction_, Size_, Value_)
        If Err Then Response.Write(Err.Description)
    End Sub
    
    Public Sub ParameterClear()
        Erase Prams
        ReDim Preserve Prams(-1)
    End Sub
    
    Public Function SqlExecute(Sql)
        On Error Resume Next
        Call ReSetRecordsAffected()
        Dim Result
        Result = True
        Conn.Execute Sql,RecordsAffected
        If Err Then Result = False
        SqlExecute = Result
    End Function
    
    Public Function SqlExecuteScaler(Sql)
        Call ReSetRecordsAffected()
        Dim Result
        Result = Null
        Set Rs = Conn.Execute(Sql)
        If Not Rs.Eof Then Result = Rs(0)
        SqlExecuteScaler = Result
    End Function
    
    Public Function SqlExecuteReader(Sql)
        Call ReSetRecordsAffected()
        Set SqlExecuteReader = Conn.Execute(Sql)
    End Function
    
    Public Function SqlExecutePageReader(Sql)
        Call ReSetRecordsAffected()
        Rs.Open Sql,Conn,3
        Set SqlExecutePageReader = Rs
    End Function
    
    Public Function PramExecute(Sql)
        On Error Resume Next
        Call ReSetRecordsAffected()
        Dim Result
        Result = True
        Call CommandPropertySet(Sql,1)
        Call PramAppend()
        Cmd.Execute RecordsAffected
        If Err Then Result = False
        PramExecute = Result
    End Function
    
    Public Function PramExecuteScaler(Sql)
        Call ReSetRecordsAffected()
        Dim Result
        Result = Null
        Call CommandPropertySet(Sql,1)
        Call PramAppend()
        Set Rs=Cmd.Execute()
        If Not Rs.Eof Then Result = Rs(0)
        PramExecuteScaler = Result
    End Function
    
    Public Function PramExecuteReader(Sql)
        Call ReSetRecordsAffected()
        Call CommandPropertySet(Sql,1)
        Call PramAppend()
        Set PramExecuteReader=Cmd.Execute()
    End Function
    
    Public Function PramExecutePageReader(Sql)
        Call ReSetRecordsAffected()
        Call CommandPropertySet(Sql,1)
        Call PramAppend()
        Call RecordsetPagePropertySet()
        Set PramExecutePageReader=Rs
    End Function
    
    Public Function ProcExecute(ProcName)
        Call ReSetRecordsAffected()
        Call CommandPropertySet(ProcName,4)
        Cmd.Parameters.append Cmd.CreateParameter("@return",3,4)
        Call PramAppend()
        Cmd.Execute()
        ProcExecute = Cmd(0)
    End Function
    
    Public Function ProcExecuteScaler(ProcName)
        Call ReSetRecordsAffected()
        Dim Result
        Result = Null
        Call CommandPropertySet(ProcName,4)
        Call PramAppend()
        Set Rs=Cmd.Execute()
        If Not Rs.Eof Then Result = Rs(0)
        ProcExecuteScaler = Result
    End Function
    
    Public Function ProcExecuteReader(ProcName)
        Call ReSetRecordsAffected()
        Call CommandPropertySet(ProcName,4)
        Call PramAppend()
        Set ProcExecuteReader=Cmd.Execute()
    End Function
    
    Public Function ProcExecutePageReader(ProcName)
        Call ReSetRecordsAffected()
        Call CommandPropertySet(ProcName,4)
        Call PramAppend()
        Call RecordsetPagePropertySet()
        Set ProcExecutePageReader=Rs
    End Function
    
    Private Sub RecordsetPagePropertySet()
        ''返回一個帶Cursor的記錄集
        With Rs
            .CursorLocation=3'adUseClient
            .CursorType=0'0=adOpenForwardOnly
            .LockType=1'1=adLockReadOnly
            .Open Cmd
            .ActiveConnection = Nothing
        End With
    End Sub
    
    Private Sub CommandPropertySet(CommandText_,CommandType_)
        With Cmd
            .CommandTimeout   = AdoCommandTimeout_
            .ActiveConnection = Conn
            .CommandText      = CommandText_
            .CommandType      = CommandType_
            .Prepared         = true
        End With
    End Sub
    
    Private Sub PramAppend()
        For Each Pram in Prams
            Cmd.Parameters.append Pram
        Next
    End Sub
    
    Public Function GetOutPut(HaveRecordset)
        Dim OutPut,arrLength
        OutPut = Array()
        If HaveRecordset Then Rs.Close()
        For Each Pram in Cmd.Parameters
            ''Response.Write Pram.name &"="& trim(Pram) &"<br>"
            If Pram.Direction=2 Or Pram.Direction=3 Then
                arrLength=Ubound(OutPut)+1
                ReDim Preserve OutPut(arrLength)
                OutPut(arrLength)=Pram
            End If
        Next
        If HaveRecordset Then Rs.Open()
        GetOutPut = OutPut
    End Function
    
End Class
%>