前几天做了个asp实用类库.
--AspLib '放在根目录
|--System
|--adovbs.inc
|--Util
|--Configuration.asp
|--DataAccess.asp
|--Validator.asp
|--......
|--Control
|--DropDownList.asp
|--DataList.asp
|--DataGrid.asp
|--Script
|--DataGrid.js
|--Images
|--DataGrid
|--First.gif
|--Next.gif
|--....
首先先看一下数据库操作类(DataAccess):
<!--#Include File="../System/adovbs.inc"-->
<%
'<class>
'<name>DataAccess</name>
'<description><![CDATA[数据库访问类(2.0版);ExecuteReader方法返回断开的记录集对象,而不是自定义的DataTable对象.每个方法都保证最
少消耗数据库资源。数据库连接对象使用客户端游标，使记录集可使用RecordCount等属性。增加debug功能。和类configuration组合,减少
strConnectionString参数(以前方法为ExecuteReader(strConn,strCommadnText)),可是增加了耦合度!作者：圣诞菠萝包]]></description>
'<attributes>
'</attributes>
'<methods>
' <method name="ExecuteReader(ByVal strCommandText)" return="Recordset" comment="执行数据库查询操作,返回断开的记录集对
象"/>
' <method name="ExecuteNonQuery(ByVal strCommandText)" return="boolean" comment="执行数据库更新/删除操作,返回受影响行数
"/>
' <method name="ExecuteScalar(ByVal strCommandText)" return="variant" comment="执行查询，并返回查询所返回的结果集中第一
行的第一列。忽略额外的列或行;"/>
' <method name="CreateParameter(ByVal strName,ByVal intDataType,ByVal intDirection,ByVal intSize,ByVal varValue)"
comment="向类内的参数集合添加参数对象"/>
' <method name="GetParameterValue(ByVal strParamName)" return="variant" comment="返回指定参数名的值"/>
' <method name="ClearParameter()" return="variant" comment="清空参数集合"/>
'</methods>
'</class>
Class DataAccess
'数据库连接对象
Private connection_
'数据库命令对象
Private command_
'连接字符串
Private connectionString_
'Debug
Private debug_
'执行数据库查询操作,返回断开的记录集对象
Public Function ExecuteReader(ByVal strCommandText)
If debug_=False Then
On Error Resume Next
End If
'打开数据库连接
Call OpenConnection()
'设置command
Call SetCommand(strCommandText)
Dim objRS,objOutRS
Set objRS=command_.Execute()
Set objOutRS=Server.CreateObject("ADODB.RecordSet")
objOutRS.Open objRS
'返回断开的记录集
Set ExecuteReader=objOutRS
If Err.Number<>0 Then
Err.Clear()
Response.Write("数据库读取操作错误")
Response.End()
End If
objRS.Close()
Set objRS=Nothing
Call CloseConnection()
End Function
'数据库更新，删除操作
Public Function ExecuteNonQuery(ByVal strCommandText)
If debug_=False Then
On Error Resume Next
End If
'打开数据库连接
Call OpenConnection()
'设置command
Call SetCommand(strCommandText)
Dim intRecordsAffected
command_.Execute intRecordsAffected, , adExecuteNoRecords
ExecuteNonQuery=intRecordsAffected
If Err.Number<>0 Then
Err.Clear()
Response.Write("数据库更新操作错误")
Response.End()
End If
Call CloseConnection()
End Function
'执行查询，并返回查询所返回的结果集中第一行的第一列。忽略额外的列或行
Public Function ExecuteScalar(ByVal strCommandText)
If debug_=False Then
On Error Resume Next
End If
'打开数据库连接
Call OpenConnection()
'设置command
Call SetCommand(strCommandText)
Dim objRS
Set objRS=command_.Execute()
If Not objRS.EOF Then
ExecuteScalar=objRS(0)
End If
If Err.Number<>0 Then
Err.Clear()
Response.Write("数据库读取单值操作错误")
Response.End()
End If
objRs.Close()
Set objRS=Nothing
Call CloseConnection()
End Function
'创建参数
Public Function CreateParameter(ByVal strName,ByVal intDataType,ByVal intDirection,ByVal intSize,ByVal varValue)
If debug_=False Then
On Error Resume Next
End If
Dim objParam
If intDirection=4 Then
Set objParam=command_.CreateParameter(strName,intDataType,intDirection)
ElseIF intDirection=2 Then
Set objParam=command_.CreateParameter(strName,intDataType,intDirection,intSize)
Else
Set objParam=command_.CreateParameter(strName,intDataType,intDirection,intSize,varValue)
End If
command_.Parameters.Append(objParam)
If Err.Number<>0 Then
Err.Clear()
Response.Write("创建参数失败")
Response.End()
End If
End Function
'取得指定参数名的参数值
Public Function GetParameterValue(ByVal strParamName)
If debug_=False Then
On Error Resume Next
End If
GetParameterValue=command_.Parameters(strParamName).Value
If Err.Number<>0 Then
Err.Clear()
GetParameterValue=""
End If
End Function
'清除参数
Public Function ClearParameter()
Dim i
For i=0 To command_.Parameters.Count - 1
command_.Parameters.Delete(0)
Next
End Function
'初始化数据库连接对象
Private Sub OpenConnection()
If debug_=False Then
On Error Resume Next
End If
If Not IsNull(connection_) Then
Set connection_=Server.CreateObject("ADODB.Connection")
connection_.CursorLocation=adUseClient
connection_.Open(connectionString_)
If Err.Number<>0 Then
Err.Clear()
CloseConnection(connection_)
Response.Write("数据库连接错误")
Response.End()
End If
End If
End Sub
'关闭数据库连接对象
Private Sub CloseConnection()
If Not connection_ Is Nothing Then
connection_.Close()
Set connection_=Nothing
End If
End Sub
'初始化数据库命令对象
Private Sub SetCommand(ByVal strCommandText)
command_.ActiveConnection=connection_
If command_.parameters.Count>0 Then
command_.CommandType=4
Else
command_.CommandType=1
End If
command_.Prepared=True
command_.CommandText=strCommandText
End Sub
'关闭数据库命令对象
Private Sub CloseCommand()
If Not command_ Is Nothing Then
Set command_=Nothing
End If
End Sub
'初始化类
Private Sub Class_Initialize()
On Error Resume Next
connectionString_=new Configuration.GetAppSetting("ConnectionString")
Set command_=Server.CreateObject("ADODB.Command")
If new Configuration.GetSystemSetting("Debug")="True" Then
debug_=True
Else
debug_=False
End If
End Sub
'销毁类
Private Sub Class_Terminate()
Call CloseCommand()
Call CloseConnection()
End Sub
End Class
%>
在前一个版本,方法都带有strConnectionString参数,如ExecuteReader(strConnectionString,strCommandText);
我还是比较推荐前一个版本的DataAccess.(因为copy就可以，不用理配置文件)
但我是个懒人，所以增加了个配置文件(Asp_Web.Config),减少strConnectionString参数,同时还增加了Debug功能;
Asp_Web.Config如下:
<?xml version="1.0" encoding="utf-8"?>
<configuration>
<appSettings>
<add key="ConnectionString" value="Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Pwd=XXXX;Initial Catalog=XXXXX;Data Source=localhost"/>
<add key="Admin_Ticket" value="CHCW_ADMIN"/>
<add key="UploadImage_MaxSize" value="102400"/>
</appSettings>
<system>
<Debug value="True"/>
</system>
</configuration>
在实际测试中,用ExecuteReader方法取数据比Rs.Open sql,conn取数据慢一点;但比较大的减少数据库的资源消耗;因为ExecuteReader返回的是一个断开的记录集;
好，看看例子:
'先包含
<!--#Include Virtual="/AspLib/Util/Configuration.asp"-->
<!--#Include Virtual="/AspLib/Util/DataAccess.asp"-->
---ExecuteReader方法---
<%
Dim objRS
Set objRS=new DataAccess.ExecuteReader("Select Top 20 NewsID,NewsTitle,UpdateTime From News Where=" & NewsCategoryID)
While Not objRS.EOF
Response.Write(objRS(0) & objRS(2) & "<br />")
objRS.MoveNext
Wend
objRS.Close()
Set objRS=Nothing
%>
<%
'存储过程
Dim objRS,objDBA
Set objDBA=new DataAccess
objDBA.CreateParameter "@NewsCategoryID",adInteger,adParamInput,4,1
Set objRS=new DataAccess.ExecuteReader("GetNewsByCategoryID")
While Not objRS.EOF
Response.Write(objRS(0) & objRS(2) & "<br />")
objRS.MoveNext
Wend
objRS.Close()
Set objRS=Nothing
%>
----ExecuteNonQuery方法-----
<%
Dim intRecordsAffected
intRecordsAffected=new DataAccess.ExecuteNonQuery("Update News Set NewsTitle='新标题' Where NewsID=10")
Response.Write("更新行数:" & intRecordsAffected)
%>
<%
'存储过程
Dim objDBA,intRecordsAffected
Set objDBA=new DataAccess
objDBA.CreateParameter "@intNewsID",adInteger,adParamInput,4,10
objDBA.CreateParameter "@strNewsTitle",adVarchar,adParamInput,50,"新标题"
intRecordsAffected=objDBA.ExecuteNonQuery("News_UpdateTitle")
If intRecordsAffected=0 Then
Response.Write("更新失败!")
Else
Response.Write("更新成功!")
End If
Set objDBA=Nothing
%>
-----ExecuteScalar方法----
<%
Dim intReturn
intReturn=new DataAccess.ExecuteScalar("Select Count(*) From News")
Response.Write("总记录数:" & intReturn)
%>