IDC资讯中心
asp的数据库类
一、前言
提到asp操作数据库，大多数人会想到：共用的连接字串constr、conn.open constr建立数据库连接、conn.execute sqlcmd方式执行命令、recordset.open sql,conn,1,1取得记录集，的确这种方法被99%的人或公司采用。对于操作数据库过程中产生的错误，恐怕99%的人不会进行处理，要么在程序的开头加入on error resume next“轻松”跳过去、要么让错误信息连同错误代码一同“暴尸”在浏览者面前。对于前一种情况可能会产生让人莫明其妙的怪异结果，后一种情况，可能会在某个时间（比如连接数据库时）暴露您的敏感信息，影响网站安全。当然，还是有个别负责的程序员同志会在容易产生错误的操作后面加入if err.xxxx来处理可能的错误，但这似乎不是一个好办法，一不小心就可能漏掉了。
我至今也没有想明白，为什么在vb和asp.net中都存在的on error goto，偏偏在asp中被取消了。
另外不得不提的是，当您在前面使用on error resume next而在后面不想resume next了，对不起，没办法，您只能把它“贯彻到底”。看看其它语言的异常机制，try..catch..finally随心所欲，真是爽酷了！
说了这么多，并不要为asp引入诸如异常机制等新的内容，毕竟asp语言本身也决定这是不可能实现的（asp.net中实现了），只是想在asp中最普遍的也是最容易出现错误的数据库操作中，找到一种有效的错误处理方式，并把conn、recordset等封装起来，达到最大限度的精简。于是便有了下面的数据库类。
二、数据库类
1、功能
正如前面所说，这个类的目的是把adodb.connection、adodb.recordset等烦琐的操作封装起来并在类里实现错误处理。现在看看类的成员、属性和方法：
1）成员：（没有公有或保护成员）
2）属性：
classname－返回类名
version－返回版本
lasterror－返回最后的错误
ignoreerror－设置/返回是否忽略数据库错误
connection－返回连接对象（adodb.connection）
connectionstring－设置/返回连接字串（本示例为sql server，如为其它请根据实际设定）
fieldcount、pagesize、pagecount、absolutepage、absoluteposition、bof、eof－请参考adodb.recordset相应内容
3）方法：
setup－设置连接数据服务器的帐号、密码、数据库名、主机/ip
connect－连接数据库
close－关闭数据库连接并释放资源
query－执行数据库查询命令并返回数据集
exesql－执行sql命令（不返回数据库）
fieldname－返回指定序号的字段名
fields－返回指定的（序号或字段名）字段的值
data－同上
movenext、moveprevious、movefirst、movelast－请参考adodb.recordset相应内容
2、实现代码（dbsql.inc.asp）
内容太长，点击此处打开/折叠…
<%
=======================================================================
class name: clsdb
design by : 彭国辉
date: 2003-12-18
site: http://kacarton.yeah.net/
email: kacarton@sohu.com
modify:
2004-6-25: 升级后的数据引擎，返回错误代号小于0（也可以是asp对数值的
定义有变），修改错误检测err.number>0 ==> err.number<>0
2004-6-30：修改错误处理，忽略如游标类型改变等非错误性质的提示
=======================================================================
class clsdb
name of this class
var string
@access private
@see property: name
private m_strname
version of this class
var string
@access private
@see property: version
private m_strversion
error object
@var adodb.connection.errors
@access private
@see property: lasterror
private m_lasterror
ingore all connection.errors
var boolean
@access private
@see property: ignoreerror
private m_ignoreerror
connection object
var adodb.connection
@access private
@see property: connection
private m_connection
is connection to database?
var boolean
@private
private m_bisconnect
recordset
var recordset
@access private
private m_recordset
connection string
var string
@access private
@see property: connectionstring
private m_connestr
database server host name or ip
var string
@access private
@see property: host
private m_strhost
database name
var string
@access private
@see property: database
private m_strdatabase
account to connection database
var string
@access private
@see property: username
private m_username
password to connection database
var string
@access private
@see property: password
private m_password
get class name attribute.
usage: otemplate.name
access public
public property get classname()
classname = m_strname
end property
get class version attribute.
usage: otemplate.version
access public
public property get version()
version = m_strversion
end property
get class last error messages.
usage: otemplate.lasterror
@access public
public property get lasterror()
lasterror = m_lasterror
end property
get or set ignore connection.errors
public property get ignoreerror()
ignoreerror = m_ignoreerror
end property
public property let ignoreerror(byval value)
m_ignoreerror = value
end property
get connection
public property get connection()
connection = m_connection
end property
get connection string
public property get connectionstring()
connectionstring = m_connestr
end property
set connection string
public property let connectionstring(byval value)
m_connestr = value
end property
get data fields count
public property get fieldcount()
fieldcount = m_recordset.fields.count
end property
get recordset pagesize
public property get pagesize()
on error resume next
pagesize = m_recordset.pagesize
if err.number<>0 then showerror("can not get pagesize!")
end property
set recordset page size
public property let pagesize(byval value)
on error resume next
m_recordset.pagesize = value
if err.number<>0 then showerror("can not set pagesize to " & value)
end property
get recordset page count
public property get pagecount()
pagecount = m_recordset.pagecount
end property
get recordset record count
public property get recordcount()
on error resume next
recordcount = m_recordset.recordcount
if err.number<>0 then showerror("get recordcount error.")
end property
get recordset absolute page
public property get absolutepage()
on error resume next
absolutepage = m_recordset.absolutepage
if err.number<>0 then showerror("can not get absolutepage!")
end property
set recordset absolute page
public property let absolutepage(byval value)
on error resume next
m_recordset.absolutepage = value
if err.number<>0 then showerror("can not set absolutepage to " & value)
end property
get recordset absolute position
public property get absoluteposition()
on error resume next
absoluteposition = m_recordset.absoluteposition
if err.number<>0 then showerror("can not get absoluteposition!")
end property
set recordset absolute position
public property let absoluteposition(byval value)
on error resume next
m_recordset.absoluteposition = value
if err.number<>0 then showerror("can not set absoluteposition to " & value)
end property
bof
public property get bof()
bof = m_recordset.bof
end property
eof
public property get eof()
eof = m_recordset.eof
end property
setup the databease host name, database name, user name(account), password
public sub setup(account, password, database, host)
m_username = account
m_password = password
if database<>"" then m_strdatabase = database
if host<>"" then m_strhost = host
m_connestr = "driver={sql server};server=" & m_strhost & ";database=" &_
m_strdatabase & ";uid=" & m_username & ";pwd=" & m_password & ";"
end sub
connect to database
public function connect()
on error resume next
m_connection.open m_connestr
if err.number<>0 then showerror("数据库连接错误:(server:" & m_strhost & ", database:" & m_strdatabase & ")")
m_bisconnect = true
connect = true todo://
end function
diconnect database
public function close()
on error resume next
set m_recordset = nothing
set m_connection = nothing
m_bisconnect = false
close = true
if err.number<>0 then showerror("切断数据库连接时出错")
end function
query
public sub query(sqlcommand)
on error resume next
if not m_bisconnect then connect
set m_recordset = server.createobject("adodb.recordset")
set m_recordset = m_connection.execute(sqlcommand)
m_recordset.open sqlcommand, m_connection, 1, 1
if err.number<>0 then showerror(sqlcommand):exit sub
if m_connection.errors.count>0 and m_ignoreerror=false then processerror(sqlcommand)
end sub
exesql command
public sub exesql(sqlcommand)
on error resume next
if not m_bisconnect then connect
m_connection.execute sqlcommand
if err.number<>0 then showerror(sqlcommand):exit sub
if m_connection.errors.count>0 and m_ignoreerror=false then processerror(sqlcommand)
end sub
get fields name
public function fieldname(byval fieldid)
on error resume next
fieldname = m_recordset.fields(fieldid).name
if err.number<>0 then showerror("不能读取字段" & fieldid & "名称!")
end function
get fields data
public function fields(byval fieldid)
on error resume next
fields = m_recordset.fields(fieldid)
if err.number<>0 then showerror("不能读取字段" & fieldid & "数据!")
end function
get fields data
public function data(byval fieldid)
on error resume next
data = m_recordset.fields(fieldid)
if err.number<>0 then showerror("不能读取" & fieldid & "数据!")
if m_connection.errors.count>0 then showerror("不能读取" & fieldid & "数据!")
end function
move to next record
public sub movenext()
on error resume next
if m_bisconnect then m_recordset.movenext
if err.number<>0 then showerror("movenext error")
end sub
move to previous record
public sub moveprevious()
on error resume next
if m_bisconnect then m_recordset.moveprevious
if err.number<>0 then showerror("moveprevious error")
end sub
move to first record
public sub movefirst()
on error resume next
if m_bisconnect then m_recordset.movefirst
if err.number<>0 then showerror("movefirst error")
end sub
move to last record
public sub movelast()
on error resume next
if m_bisconnect then m_recordset.movelast
if err.number<>0 then showerror("movelast error")
end sub
2004-6-30
private sub processerror(byval sqltxt)
for i=0 to m_connection.errors.count-1
if m_connection.errors.item(i).number<>0 then showerror(sqltxt)
next
end sub
this function is called whenever an error occurs and will handle the error
additionally the error message will be saved in m_strlasterror.
@param $msg a string containing an error message
@access private
@return void
private sub showerror(byval sqltxt)
for i=0 to m_connection.errors.count-1
response.write m_connection.errors.item(i) & "(" & m_connection.errors.item(i).number & ")<br>"
next
m_lasterror = err.description
m_connection.errors.clear
response.write "<br>——————————————————<br>" &_
"<font color=red size=4>" & sqltxt & "</font>"
response.write "<br>——————————————————<br>" &_
"<font color=red size=4>" & left(sqltxt, 10) & "…(错误信息已被屏蔽)，请与网站管理员联系！</font>"
response.end
end sub
class constructor, set class default attributes, you can change it
private sub class_initialize
m_strname = "clsdb"
m_strversion = "1.0"
set m_connection = server.createobject("adodb.connection")
请修改此处为你连接数据库的默认值
setup "sa", "password", "northwind", "(local)"
m_bisconnect = false
m_ignoreerror = false
end sub
class destructor, free memory.
private sub class_terminate
set m_recordset = nothing
set m_connection = nothing
end sub
end class
%>
3、使用示例
<!–#include file="dbsql.inc.asp"–>
<%
function htmlencode(str)
if isnull(str) then htmlencode = "(null)" _
else htmlencode = server.htmlencode(str)
end function
dim sql, i
set sql = new clsdb
sql.connect
sql.exesql("update customers set address=中华人民共和国 where id=1")
sql.query("select * from customers")
response.write "<table border=1><tr>"
for i=0 to sql.fieldcount-1
response.write "<td>" & server.htmlencode(sql.fieldname(i)) & "</td>"
next
response.write "</tr>"
while not sql.eof
for i=0 to sql.fieldcount-1
response.write "<td>" & htmlencode(sql.data(i)) & "</td>" 此处可直接用字段名代替i
next
response.write "</tr>"
sql.movenext
wend
response.write "</table>"
sql.close
set sql = nothing
%>
三、小结
这还只是一个比较粗糙的数据库类，还有很多adodb的特性没有添加在内，而且灵活性也不够。本文旨在为大家提供另一种思路，各位在看完本文后觉得还是有一点收获的话，我就很满足了。
此外，我的下一篇文章《将asp查询分页封装起来》中将用到这个类。
