ASP操作MSQL类

<%
'=========================================================
'類名: Ms SQL 操作類
'作者: Athrun
'版本: V 0.1
'開發日期:2012-4-14
'修改日期:2012-4-14
'Email: [email protected]
'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.1SQLEXPRESS"
'Const dbname="HandyMan"
'Const dbuid="sa"
'Const dbpwd="sasa"
'Const AdoConnectionTimeout = 15
'Const AdoCommandTimeout = 30Class DbHelperSQLPrivate Conn,Rs,Cmd,Pram,Prams,RecordsAffectedPrivate AdoConnectionTimeout_,AdoCommandTimeout_Public Property Let LetCmdTimeout(ByVal Value)AdoCommandTimeout_ = ValueEnd PropertyPublic Property Get GetCmdTimeout()GetCmdTimeout = AdoCommandTimeout_End PropertyPublic Property Get GetRecordsAffected()GetRecordsAffected = RecordsAffectedEnd PropertyPrivate Sub Class_Initialize()AdoConnectionTimeout_ = AdoConnectionTimeoutAdoCommandTimeout_ = AdoCommandTimeoutPrams = Array()Call ReSetRecordsAffected()Call DbOpen()Set Rs = Server.CreateObject("ADODB.Recordset")    Set Cmd = Server.CreateObject("ADODB.Command")    End SubPrivate Sub Class_Terminate()Call DbClose()Call ParameterClear()End SubPrivate Sub DbOpen()On Error Resume NextApplication.LockDim connStringSet Conn = Server.CreateObject("ADODB.Connection")Conn.ConnectionTimeout = AdoConnectionTimeout_Conn.CommandTimeout = AdoCommandTimeout_''sql2000''connString = "Driver={sql server};uid=" + dbuid + ";pwd=" + dbpwd + ";database=" + dbname + ";server=" + dbIP + ";"''sql2008connString = "Driver={SQL Server Native Client 10.0};uid=" + dbuid + ";pwd=" + dbpwd + ";database=" + dbname + ";server=" + dbIP + ";"Conn.Open connStringIf Err ThenApplication.UnLock()Response.Clear()Response.Write("")Response.End()End IfApplication.UnLockEnd SubPrivate Sub DbClose()''If Conn.State = 1 Then Conn.Close()End SubPrivate Sub ReSetRecordsAffected()RecordsAffected = -1End SubPublic Sub ParameterAdd(Name_, Type_, Direction_, Size_, Value_)On Error Resume NextDim arrLengtharrLength=Ubound(Prams)+1ReDim Preserve Prams(arrLength)Set Prams(arrLength)=Cmd.CreateParameter(Name_, Type_, Direction_, Size_, Value_)If Err Then Response.Write(Err.Description)End SubPublic Sub ParameterClear()Erase PramsReDim Preserve Prams(-1)End SubPublic Function SqlExecute(Sql)On Error Resume NextCall ReSetRecordsAffected()Dim ResultResult = TrueConn.Execute Sql,RecordsAffectedIf Err Then Result = FalseSqlExecute = ResultEnd FunctionPublic Function SqlExecuteScaler(Sql)Call ReSetRecordsAffected()Dim ResultResult = NullSet Rs = Conn.Execute(Sql)If Not Rs.Eof Then Result = Rs(0)SqlExecuteScaler = ResultEnd FunctionPublic Function SqlExecuteReader(Sql)Call ReSetRecordsAffected()Set SqlExecuteReader = Conn.Execute(Sql)End FunctionPublic Function SqlExecutePageReader(Sql)Call ReSetRecordsAffected()Rs.Open Sql,Conn,3Set SqlExecutePageReader = RsEnd FunctionPublic Function PramExecute(Sql)On Error Resume NextCall ReSetRecordsAffected()Dim ResultResult = TrueCall CommandPropertySet(Sql,1)Call PramAppend()Cmd.Execute RecordsAffectedIf Err Then Result = FalsePramExecute = ResultEnd FunctionPublic Function PramExecuteScaler(Sql)Call ReSetRecordsAffected()Dim ResultResult = NullCall CommandPropertySet(Sql,1)Call PramAppend()Set Rs=Cmd.Execute()If Not Rs.Eof Then Result = Rs(0)PramExecuteScaler = ResultEnd FunctionPublic Function PramExecuteReader(Sql)Call ReSetRecordsAffected()Call CommandPropertySet(Sql,1)Call PramAppend()Set PramExecuteReader=Cmd.Execute()End FunctionPublic Function PramExecutePageReader(Sql)Call ReSetRecordsAffected()Call CommandPropertySet(Sql,1)Call PramAppend()Call RecordsetPagePropertySet()Set PramExecutePageReader=RsEnd FunctionPublic 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 FunctionPublic Function ProcExecuteScaler(ProcName)Call ReSetRecordsAffected()Dim ResultResult = NullCall CommandPropertySet(ProcName,4)Call PramAppend()Set Rs=Cmd.Execute()If Not Rs.Eof Then Result = Rs(0)ProcExecuteScaler = ResultEnd FunctionPublic Function ProcExecuteReader(ProcName)Call ReSetRecordsAffected()Call CommandPropertySet(ProcName,4)Call PramAppend()Set ProcExecuteReader=Cmd.Execute()End FunctionPublic Function ProcExecutePageReader(ProcName)Call ReSetRecordsAffected()Call CommandPropertySet(ProcName,4)Call PramAppend()Call RecordsetPagePropertySet()Set ProcExecutePageReader=RsEnd FunctionPrivate Sub RecordsetPagePropertySet()''返回一個帶Cursor的記錄集With Rs.CursorLocation=3'adUseClient.CursorType=0'0=adOpenForwardOnly.LockType=1'1=adLockReadOnly
            .Open Cmd.ActiveConnection = NothingEnd WithEnd SubPrivate Sub CommandPropertySet(CommandText_,CommandType_)With Cmd.CommandTimeout   = AdoCommandTimeout_.ActiveConnection = Conn.CommandText      = CommandText_.CommandType      = CommandType_.Prepared         = trueEnd WithEnd SubPrivate Sub PramAppend()For Each Pram in PramsCmd.Parameters.append PramNextEnd SubPublic Function GetOutPut(HaveRecordset)Dim OutPut,arrLengthOutPut = Array()If HaveRecordset Then Rs.Close()For Each Pram in Cmd.Parameters''Response.Write Pram.name &"="& trim(Pram) &"
"If Pram.Direction=2 Or Pram.Direction=3 ThenarrLength=Ubound(OutPut)+1ReDim Preserve OutPut(arrLength)OutPut(arrLength)=PramEnd IfNextIf HaveRecordset Then Rs.Open()GetOutPut = OutPutEnd FunctionEnd Class
%>