<% '========================================================= '類名: 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 = 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("<script language='javascript'>alert('Database connection error.');</script>")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) &"<br>"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 %>

?

總結

以上是生活随笔為你收集整理的ASP操作MSQL类的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。