ASP操作MSQL类

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