1
<%
2Class dbClass
3'-------------------------------------------------------------------------
4 '变量说明
5 'conn-----------connection对象
6 'strsql---------执行查询的语句
7 'vTbName--------查询分页的表名
8 'vPKey----------查询分页的表的主键
9 'vPgFields------查询分页要显示的字段
10 'vPgSize--------查询分页每页显示的记录数
11 'vCurrPg--------查询分页显示的当前页
12 'vConditions----查询分页的条件
13 'vOrderBy-------查询分页的排序
14'-------------------------------------------------------------------------
15 private conn,strsql,vTbName,vPKey,vPgFields,vPgSize,vCurrPg,vConditions,vOrderBy
16 '类的初始化
17 private Sub Class_Initialize()
18 '当是MS Sql数据库时设置以下两个变量
19 'dim dbServer '数据库服务器的名称或ip地址
20 'dim dbname '数据库的名字
21 dim dbPath '若是Access数据库，此处设置其路径
22 dim dbUser '数据库的登录用户名
23 dim dbPass '数据库的登录密码
24 dim connstr
25 dbPath = "/testasp/data/data.mdb" '设置数据库路径
26 dbUser = "admin"
27 dbPass = "123456"
28 '若是access,并且有密码
29 connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(dbPath) &_
30 ";User ID=" & dbUser & ";Password=;Jet OLEDB:Database Password=" & dbPass
31 '若是access，并且没有密码
32 'connstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(dbPath)
33 '若是ms-sql数据库
34 'connstr = "Provider = Sqloledb; User ID = " & dbUser & "; Password = " & dbPass &_
35 ' "; Initial Catalog = " & dbname & "; Data Source = " & dbServer
36 on error resume next
37 set conn=server.CreateObject("adodb.connection")
38 conn.open connstr
39 errMsg "连接数据库"
40 End Sub
41 '类结束
42 Private Sub Class_terminate()
43 conn.close
44 set conn=nothing
45 End Sub
46'-------------------------------------------------------------------------
47'给类的变量设置值
48'-------------------------------------------------------------------------
49 '设置sql语句
50 Public Property Let sqlStr(Byval Values)
51 strsql=Values
52 End Property
53 '设置查询分页的表名
54 public property let tbName(Byval Values)
55 vTbName=Values
56 end property
57 '--------------------------------------------------------
58 '设置查询分页的表的主键
59 public property let pKey(ByVal Values)
60 vPKey=Values
61 end property
62 '--------------------------------------------------------
63 '设置显示的字段
64 public property let pgFields(ByVal Values)
65 vPgFields=Values
66 end property
67 '--------------------------------------------------------
68 '设置每页显示的记录数
69 public property let pgSize(ByVal Values)
70 vPgSize=Values
71 end property
72 '---------------------------------------------------------
73 '设置当前显示的页数
74 public property let currPg(ByVal Values)
75 vCurrPg=Values
76 end property
77 '--------------------------------------------------------
78 '设置查询的条件
79 public property let conditions(ByVal Values)
80 if Len(Values)>0 then
81 vConditions=" where "&Values
82 else
83 vConditions=" where 1=1 "
84 end if
85 end property
86 '-------------------------------------------------------
87 '设置查询的排序
88 public property let orderBy(ByVal Values)
89 if Len(Values)>0 then
90 vOrderBy=" order by "&Values
91 else
92 vOrderBy=Values
93 end if
94 end property
95 '-------------------------------------------------------------
96 '得到记录总数
97 public property get vRsCount()
98 if vCurrPg=1 then
99 sqlc="select count("&vPKey&") as Idcount from "&vTbName&" "&vConditions
100 set rsc=server.CreateObject("adodb.recordset")
101 rsc.open sqlc,conn,0,1
102 RsNum=rsc("IdCount")
103 rsc.close
104 set rsc=nothing
105 if RsNum>0 then
106 response.Cookies("iRecord")=RsNum
107 vRsCount=RsNum
108 else
109 vRsCount=0
110 end if
111 else
112 vRsCount=request.Cookies("iRecord")
113 end if
114 end property
115 '得到总页数
116 public property get vPgCount()
117 iRsCount2=vRsCount()
118 if iRsCount2 mod vPgSize =0 then
119 vPgCount=int(iRsCount2/vPgSize)
120 else
121 vPgCount=int(iRsCount2/vPgSize)+1
122 end if
123 end property
124'查询数据库
125 Public Function rsDB()
126 on error resume next
127 '简单的查询出结果
128' set rsDB = Server.CreateObject("ADODB.RecordSet")
129' rsDB.Open strsql,conn,1,3
130 Set rsDB=conn.Execute(strsql)
131 errMsg "查询数据库"
132 End Function
133'添加，更新，删除数据库记录
134 public Function upDB()
135 on error resume next
136 conn.execute(strsql)
137 errMsg "编辑数据库记录"
138 end Function
139'-------------------------------------------------------------------------
140'用来实现分页的记录集函数
141 public function pageRs()
142 on error resume next
143 dim startRs
144 startRs=(vCurrPg-1)*vPgSize
145 '-------------------------------------------------------------------------------------------
146 '使用此语句的话要根据参数修改代码，具体的是若排序为asc则<改为>,min改为max
147' if startRs=0 then
148' strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" "&vOrderBy
149' else
150' strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" and "&vPKey&" < "
151' strsql=strsql&"(select min("&vPKey&") from (select top "&startRs&" "&vPKey&" from "
152' strsql=strsql&vTbName&" "&vConditions&" "&vOrderBy&") as idTable) "&vOrderBy
153' end if
154 '---------------------------------------------------------------
155 if startRs=0 then
156 strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" "&vOrderBy
157 else
158 strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" and "&vPKey&" not "
159 strsql=strsql&"in (select top "&startRs&" "&vPKey&" from "&vTbName&" "&vConditions&" "&vOrderBy
160 strsql=strsql&") "&vOrderBy
161 end if
162 '-------------------------------------------------------------------
163 set pageRs=server.CreateObject("adodb.recordset")
164 pageRs.open strsql,conn,0,1
165 errMsg "记录分页"
166 end function
167'------sql用存储过程分页------------------------------------------------------
168public function sqlPage()
169on error resume next
170Set sqlPage=server.CreateObject("Adodb.RecordSet")
171Set Cm=Server.CreateObject("Adodb.Command")
172Cm.CommandType = 4
173Cm.ActiveConnection = conn
174Cm.CommandText="sp_Util_Page"
175Cm.parameters(1) = vPgFields
176Cm.parameters(2) = vTbName
177Cm.parameters(3) = vConditions
178Cm.parameters(4) = vOrderBy
179Cm.parameters(5) = vPKey
180Cm.parameters(6) = vCurrPg
181Cm.parameters(7) = vPgSize
182Cm.parameters(8) = vRsCount()
183Cm.parameters(9) = ""
184sqlPage.CursorLocation = 3
185sqlPage.LockType = 1
186sqlPage.Open Cm
187errMsg "记录分页"
188end function
189'----------------------------------------------------------------------------
190'关闭记录集objRs
191'----------------------------------------------------------------------------
192 Public Function cRs(ByVal ObjRs)
193 ObjRs.close()
194 Set ObjRs = Nothing
195 End Function
196'----------------------分页的页码导航---------------------------------------
197public function pageNav()
198iRsCount=vRsCount()'总记录数
199mypage=vCurrPg'当前页数
200PgCount=vPgCount()'总页数
201prePage=mypage-1
202if prePage<1 then
203prePage=1
204end if
205nextPage=mypage+1
206if nextPage>PgCount then
207nextPage=PgCount
208end if
209pagestr="<div id=""fy""><span id=""rpc"">总共有"&iRsCount&"条记录 "&mypage&"/"&PgCount&"</span>"
210pagestr=pagestr&"<a href='?currpage=1' class='aW'>首页</a><a href='?currpage="&prePage&"' class='aW'>前一页</a>"
211if (mypage-1) mod 4=0 then
212firstPage=mypage
213elseif int((mypage-1)/4)=0 then
214firstPage=1
215else
216firstPage=int((mypage-1)/4)*4+1
217end if
218endPage=firstPage+4
219astr=""
220for i=firstPage to endPage
221astr=astr&"<a href='?currpage="&i&"'"
222if Cstr(mypage)=Cstr(i) then
223astr=astr&" id='currP'"
224end if
225astr=astr&">"&i&"</a>"
226if i>PgCount-1 then exit for
227next
228astr=astr&"<a href='?currpage="&nextPage&"' class='aW'>后一页</a><a href='?currpage="&PgCount&"' class='aW'>尾页</a></div>"
229pagestr=pagestr&astr
230pageNav=pagestr
231end function
232'输出带分页功能的table
233Function showTb(ByVal TbTil)
234 set rsTb=pageRs()'若是存储过程就调用sqlPage()
235 tbRs= rsTb.getrows()
236 cRs(rsTb)
237 iTblRow=Ubound(tbRs,2)
238 iTblCol=Ubound(TbTil)
239 tbStr="<table border='0' cellspacing='0' cellpadding='0'><tbody>"
240 for r1=0 to iTblCol
241 tr1=tr1&"<td width='"&split(TbTil(r1),"|")(1)&"'>"&split(TbTil(r1),"|")(0)&"</td>"
242 next
243 tr1="<tr>"&tr1&"</tr>"
244 for ri=0 to iTblRow
245 for ci=0 to iTblCol
246 td=td&"<td width='"&split(TbTil(ci),"|")(1)&"'>"&tbRs(ci,ri)&"</td>"
247 next
248 tr=tr&"<tr>"&td&"</tr>"
249 td=null
250 next
251 TbTil=null
252 tbRs=null
253 response.Write(tbStr&tr1&tr&"<tr><td colspan='"&iTblCol+1&"'>"&pageNav()&"</td></tr></tbody></table>")
254 tbStr=null
255 tr1=null
256 tr=null
257End Function
258'打印sql语句，以便语句有错误时检查
259 Public Sub prnSql()
260 response.Write(strsql)
261 End Sub
262'-------------------------------------------------------------------------
263'容错函数
264'-------------------------------------------------------------------------
265 Private Function errMsg(errMsg)
266 If Err.number<>0 Then
267 '出现问题可利用此处代码打印出描述信息，方便调试。可注释掉
268 response.Write(Cstr(Err.description)&"<br>")
269 Err.Clear
270 Response.Write "<font color='#FF0000'>"&errMsg&"出错</font>" '注释
271 Response.End()
272 End If
273 End Function
274'-------------------------------------------------------------------------
275'容错函数结束
276'-------------------------------------------------------------------------
277End Class
278%>
<%2
Class dbClass3
'-------------------------------------------------------------------------4
'变量说明5
'conn-----------connection对象6
'strsql---------执行查询的语句7
'vTbName--------查询分页的表名8
'vPKey----------查询分页的表的主键9
'vPgFields------查询分页要显示的字段10
'vPgSize--------查询分页每页显示的记录数11
'vCurrPg--------查询分页显示的当前页12
'vConditions----查询分页的条件13
'vOrderBy-------查询分页的排序14
'-------------------------------------------------------------------------15
private conn,strsql,vTbName,vPKey,vPgFields,vPgSize,vCurrPg,vConditions,vOrderBy16
'类的初始化17
private Sub Class_Initialize()18
'当是MS Sql数据库时设置以下两个变量 19
'dim dbServer '数据库服务器的名称或ip地址20
'dim dbname '数据库的名字21
dim dbPath '若是Access数据库，此处设置其路径22
dim dbUser '数据库的登录用户名23
dim dbPass '数据库的登录密码24
dim connstr25
dbPath = "/testasp/data/data.mdb" '设置数据库路径26
dbUser = "admin" 27
dbPass = "123456"28
'若是access,并且有密码29
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(dbPath) &_30
";User ID=" & dbUser & ";Password=;Jet OLEDB:Database Password=" & dbPass31
'若是access，并且没有密码32
'connstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(dbPath)33
'若是ms-sql数据库34
'connstr = "Provider = Sqloledb; User ID = " & dbUser & "; Password = " & dbPass &_35
' "; Initial Catalog = " & dbname & "; Data Source = " & dbServer 36
on error resume next37
set conn=server.CreateObject("adodb.connection")38
conn.open connstr39
errMsg "连接数据库"40
End Sub41
'类结束42
Private Sub Class_terminate()43
conn.close44
set conn=nothing45
End Sub46
'-------------------------------------------------------------------------47
'给类的变量设置值48
'------------------------------------------------------------------------- 49
'设置sql语句50
Public Property Let sqlStr(Byval Values)51
strsql=Values52
End Property53
'设置查询分页的表名54
public property let tbName(Byval Values) 55
vTbName=Values56
end property57
'--------------------------------------------------------58
'设置查询分页的表的主键59
public property let pKey(ByVal Values)60
vPKey=Values61
end property62
'--------------------------------------------------------63
'设置显示的字段64
public property let pgFields(ByVal Values)65
vPgFields=Values66
end property67
'--------------------------------------------------------68
'设置每页显示的记录数69
public property let pgSize(ByVal Values)70
vPgSize=Values71
end property72
'---------------------------------------------------------73
'设置当前显示的页数74
public property let currPg(ByVal Values) 75
vCurrPg=Values76
end property77
'--------------------------------------------------------78
'设置查询的条件79
public property let conditions(ByVal Values)80
if Len(Values)>0 then81
vConditions=" where "&Values82
else83
vConditions=" where 1=1 "84
end if85
end property86
'-------------------------------------------------------87
'设置查询的排序88
public property let orderBy(ByVal Values)89
if Len(Values)>0 then90
vOrderBy=" order by "&Values91
else92
vOrderBy=Values93
end if94
end property 95
'------------------------------------------------------------- 96
'得到记录总数97
public property get vRsCount()98
if vCurrPg=1 then99
sqlc="select count("&vPKey&") as Idcount from "&vTbName&" "&vConditions100
set rsc=server.CreateObject("adodb.recordset")101
rsc.open sqlc,conn,0,1102
RsNum=rsc("IdCount")103
rsc.close104
set rsc=nothing105
if RsNum>0 then106
response.Cookies("iRecord")=RsNum107
vRsCount=RsNum108
else109
vRsCount=0110
end if111
else112
vRsCount=request.Cookies("iRecord")113
end if114
end property115
'得到总页数116
public property get vPgCount() 117
iRsCount2=vRsCount()118
if iRsCount2 mod vPgSize =0 then119
vPgCount=int(iRsCount2/vPgSize)120
else121
vPgCount=int(iRsCount2/vPgSize)+1122
end if123
end property124
'查询数据库125
Public Function rsDB()126
on error resume next127
'简单的查询出结果128
' set rsDB = Server.CreateObject("ADODB.RecordSet")129
' rsDB.Open strsql,conn,1,3 130
Set rsDB=conn.Execute(strsql)131
errMsg "查询数据库"132
End Function133
'添加，更新，删除数据库记录134
public Function upDB()135
on error resume next136
conn.execute(strsql)137
errMsg "编辑数据库记录"138
end Function139
'------------------------------------------------------------------------- 140
'用来实现分页的记录集函数141
public function pageRs()142
on error resume next143
dim startRs144
startRs=(vCurrPg-1)*vPgSize145
'-------------------------------------------------------------------------------------------146
'使用此语句的话要根据参数修改代码，具体的是若排序为asc则<改为>,min改为max147
' if startRs=0 then148
' strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" "&vOrderBy149
' else150
' strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" and "&vPKey&" < "151
' strsql=strsql&"(select min("&vPKey&") from (select top "&startRs&" "&vPKey&" from " 152
' strsql=strsql&vTbName&" "&vConditions&" "&vOrderBy&") as idTable) "&vOrderBy153
' end if154
'---------------------------------------------------------------155
if startRs=0 then156
strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" "&vOrderBy157
else158
strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" and "&vPKey&" not "159
strsql=strsql&"in (select top "&startRs&" "&vPKey&" from "&vTbName&" "&vConditions&" "&vOrderBy160
strsql=strsql&") "&vOrderBy 161
end if162
'-------------------------------------------------------------------163
set pageRs=server.CreateObject("adodb.recordset")164
pageRs.open strsql,conn,0,1165
errMsg "记录分页"166
end function167
'------sql用存储过程分页------------------------------------------------------168
public function sqlPage()169
on error resume next170
Set sqlPage=server.CreateObject("Adodb.RecordSet")171
Set Cm=Server.CreateObject("Adodb.Command")172
Cm.CommandType = 4173
Cm.ActiveConnection = conn174
Cm.CommandText="sp_Util_Page"175
Cm.parameters(1) = vPgFields176
Cm.parameters(2) = vTbName177
Cm.parameters(3) = vConditions178
Cm.parameters(4) = vOrderBy179
Cm.parameters(5) = vPKey180
Cm.parameters(6) = vCurrPg181
Cm.parameters(7) = vPgSize182
Cm.parameters(8) = vRsCount()183
Cm.parameters(9) = ""184
sqlPage.CursorLocation = 3 185
sqlPage.LockType = 1186
sqlPage.Open Cm187
errMsg "记录分页"188
end function189
'----------------------------------------------------------------------------190
'关闭记录集objRs191
'----------------------------------------------------------------------------192
Public Function cRs(ByVal ObjRs)193
ObjRs.close()194
Set ObjRs = Nothing195
End Function196
'----------------------分页的页码导航---------------------------------------197
public function pageNav()198
iRsCount=vRsCount()'总记录数199
mypage=vCurrPg'当前页数200
PgCount=vPgCount()'总页数201
prePage=mypage-1202
if prePage<1 then203
prePage=1204
end if205
nextPage=mypage+1206
if nextPage>PgCount then207
nextPage=PgCount208
end if209
pagestr="<div id=""fy""><span id=""rpc"">总共有"&iRsCount&"条记录 "&mypage&"/"&PgCount&"</span>" 210
pagestr=pagestr&"<a href='?currpage=1' class='aW'>首页</a><a href='?currpage="&prePage&"' class='aW'>前一页</a>"211
if (mypage-1) mod 4=0 then212
firstPage=mypage213
elseif int((mypage-1)/4)=0 then214
firstPage=1215
else216
firstPage=int((mypage-1)/4)*4+1217
end if218
endPage=firstPage+4219
astr=""220
for i=firstPage to endPage221
astr=astr&"<a href='?currpage="&i&"'"222
if Cstr(mypage)=Cstr(i) then223
astr=astr&" id='currP'"224
end if225
astr=astr&">"&i&"</a>"226
if i>PgCount-1 then exit for227
next228
astr=astr&"<a href='?currpage="&nextPage&"' class='aW'>后一页</a><a href='?currpage="&PgCount&"' class='aW'>尾页</a></div>"229
pagestr=pagestr&astr230
pageNav=pagestr231
end function232
'输出带分页功能的table233
Function showTb(ByVal TbTil) 234
set rsTb=pageRs()'若是存储过程就调用sqlPage()235
tbRs= rsTb.getrows()236
cRs(rsTb)237
iTblRow=Ubound(tbRs,2)238
iTblCol=Ubound(TbTil)239
tbStr="<table border='0' cellspacing='0' cellpadding='0'><tbody>"240
for r1=0 to iTblCol241
tr1=tr1&"<td width='"&split(TbTil(r1),"|")(1)&"'>"&split(TbTil(r1),"|")(0)&"</td>"242
next243
tr1="<tr>"&tr1&"</tr>"244
for ri=0 to iTblRow245
for ci=0 to iTblCol246
td=td&"<td width='"&split(TbTil(ci),"|")(1)&"'>"&tbRs(ci,ri)&"</td>"247
next 248
tr=tr&"<tr>"&td&"</tr>"249
td=null250
next251
TbTil=null252
tbRs=null253
response.Write(tbStr&tr1&tr&"<tr><td colspan='"&iTblCol+1&"'>"&pageNav()&"</td></tr></tbody></table>")254
tbStr=null255
tr1=null256
tr=null257
End Function258
'打印sql语句，以便语句有错误时检查259
Public Sub prnSql()260
response.Write(strsql)261
End Sub262
'------------------------------------------------------------------------- 263
'容错函数264
'-------------------------------------------------------------------------265
Private Function errMsg(errMsg) 266
If Err.number<>0 Then267
'出现问题可利用此处代码打印出描述信息，方便调试。可注释掉268
response.Write(Cstr(Err.description)&"<br>")269
Err.Clear270
Response.Write "<font color='#FF0000'>"&errMsg&"出错</font>" '注释271
Response.End()272
End If273
End Function274
'-------------------------------------------------------------------------275
'容错函数结束276
'-------------------------------------------------------------------------277
End Class278
%>
我的想法是既然是数据库操作类，所以类一实例化的时候就建立数据库连接，所以在使用的时候要先修改 Class_Initialize()中的代码，设置正确的数据库连接数据，设置好了就可以使用了。
1、一般的操作
set myDb=new dbClass
myDb.sqlStr="select * from ..." '这里设置数据库操作语句
set rs=myDb.rsDB()
'这里返回rs,接着就按平时的写法写表现数据的代码
'.......
myDb.cRs(rs) '关闭释放rs
set myDb=nothing
myDb.sqlStr="select * from ..." '这里设置数据库操作语句
set rs=myDb.rsDB()
'这里返回rs,接着就按平时的写法写表现数据的代码
'.......
myDb.cRs(rs) '关闭释放rs
set myDb=nothing
若myDb.sqlStr设置的是更新、删除或添加的sql语句，那么就要用myDb.upDB()来执行，即
set myDb=new dbClass
myDb.sqlStr="update ..."
myDb.upDB()
set myDb=nothing
myDb.sqlStr="update ..."
myDb.upDB()
set myDb=nothing
