ASP操作MSQL类代码,ASP代码库,德仔网

代码语言
代码分类
【ASP】 ASP操作MSQL类
作者:Athrun / 发布于2012/8/1/ 1047
ASP操作MSQL类
<div><%
'=========================================================
'類名: Ms SQL 操作類
'作者: Athrun
'版本: V 0.1
'開發日期:2012-4-14
'修改日期:2012-4-14
'Email: <a href="mailto:athrunsoft@gmail.com">athrunsoft@gmail.com</a>
'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</div>
<div>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) &""
 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
%></div>
试试其它关键字
　操作MSQL类　
同语言下
可能有用的
Athrun贡献的其它代码(6)