<%
'----------------------------------------------
'{by}D.n.sky
'{website}www.desetang.com
'{public function}
' query(sql) 执行sql
' selectRecord(table,colunm,condition,exp,value,extra) 删选数据
' deleteRecord(table,condition,exp,value,extra) 删除指定数据
' insertRecord(table,colunm,value) 插入数据
' updateRecord(table,data,condition,exp,value,extra) 更新数据
' gettotal(table,colunm,condition,exp,value,extra) 获取指定记录的数量
' ispageshow() 是否分页显示
' showPage() 显示分页样式
'-----------------------------------------------
' 张波
Class MySql
Private conn,connstr
Private rs,sql
Private dbpath,dbuser,dbpass
Private isPage,currentPage,pSize,thisPageCount,thisRecordCount
'初始化参数
Private Sub Class_Initialize
pstart = 0
dbpath = "db/coding.mdb"
call selectconnstr("Access OLE DB")
call Initialize
End Sub
'--------------------------
' 执行sql语句
'-------------------------------------------
Public Function query(sql)
conn.Execute(sql)
If err.Number <> 0 Then query = -1 Else query = 0 End If
End Function
'----------------------------------------------
' 是否启用分页
'---------------------------------------------
Public function ispageshow(size)
isPage = 1
pSize = int(size)
currentPage = request.QueryString("page")
if currentPage = "" then currentPage = 1 else currentPage = int(currentPage)
End Function
' 功能：取得指定id的记录
' 参数table所查询数据表
' 返回：成功返回数组，失败返回-2
'-------------------------------------------
Public Function selectRecord(table,colunm,condition,exp,value,extra)
set rs = Server.CreateObject("adodb.recordset")
table = "`"&table&"`"
sql = "select "&colunm&" from "&table&" where "&condition&exp&value&" "
rs.open sql,conn,1,1
thisRecordCount = rs.RecordCount
dim b,c,u
b = thisRecordCount -1
If b > -1 then
c = clng(rs.Fields.Count - 1)
'-----------------------------
' 添加分页输出数据
'-----------------------------
If isPage = 1 then
thisPageCount = modint(thisRecordCount,pSize)
if currentPage = 0 then thisPageCount = 1
if currentPage > thisPageCount then currentPage = thisPageCount
rs.pagesize = pSize
rs.absolutepage = currentPage
if currentPage = thisPageCount and (thisRecordCount mod pSize)<>0 then
b = (thisRecordCount mod pSize) - 1
else
b = pSize - 1
end if
End If
ReDim v(b,c)
For j = 0 to rs.pagesize-1
if rs.bof or rs.eof then exit for
For i=0 to rs.Fields.Count - 1
v(j,i) = rs.Fields(i)
Next
rs.movenext
Next
selectRecord = v
Else
selectRecord = -2
End IF
call closethis()
End Function
'-------------------------------------------
' 功能：删除数据
' 返回：失败返回-1，无该数据返回-2，成功返回0
'-------------------------------------------
Public Function deleteRecord(table,condition,exp,value,extra)
If 0 = gettotal(table,"id",condition,exp,value,extra) then
deleteRecord = -2
Else
sql="DELETE FROM "&table&" WHERE "&condition&exp&value&" "&extra
deleteRecord = query(sql)
End IF
End Function
'-----------------------------------------
' 插入数据
' 参数：array(array("bigclass"),array(request.Form("data"))),ext其他条件
'------------------------------------------
Public Function insertRecord(table,data,ext)
table = "`"&table&"`"
sql = "SELECT * FROM "&table&" "&ext
set rs = Server.CreateObject("adodb.recordset")
rs.open sql,conn,1,3
rs.addnew
For q = Lbound(data(0)) to Ubound(data(0))
rs(data(0)(q)) = data(1)(q)
Next
rs.update
call closethis()
If err.Number <> 0 Then insertRecord = -1 Else insertRecord = 0 End If
End Function
'-----------------------------------------
' 更新数据
' 参数：array(array("bigclass"),array(request.Form("data")))
' 返回：成功0，不存在该数据-2，失败-1
'------------------------------------------
Public Function updateRecord(table,data,condition,exp,value,extra)
if 0 = gettotal(table,"id",condition,exp,value,extra) Then
updateRecord = -2
Else
set rs = Server.CreateObject("adodb.recordset")
sql = "SELECT * FROM "&table&" WHERE "&condition&exp&value&" "&extra
rs.open sql,conn,1,3
For q = Lbound(data(0)) to Ubound(data(0))
rs(data(0)(q)) = data(1)(q)
Next
rs.update
call closethis()
If err.Number <> 0 Then updateRecord = -1 Else updateRecord = 0 End If
End If
End Function
'------------------------------------------
' 功能：数组转换为字符串
' 返回：指定格式字符串
' 参数：arr数组，exp分割符，exper附加符号
'---------------------------------------------
Private Function strsql(arr,exp,exper)
Dim col
If isArray(arr) Then
For l = Lbound(arr) to Ubound(arr)
if l = Ubound(arr) then
col = col & exper&arr(l)&exper
else
col = col & exper&arr(l)&exper&exp
end if
Next
strsql = col
Else
If arr<>"" Then col = exper&arr&exper
strsql = col
End if
End Function
'-------------------------------------------
' 功能：获取符合条件的记录的总条数
' 返回：如果有记录则返回符合条件的记录条数，如果没有则返回0
'-------------------------------------------
Public Function gettotal(table,colunm,condition,exp,value,extra)
set rs = Server.CreateObject("adodb.recordset")
table = "`"&table&"`"
colunm = "Count("&colunm&")"
sql = "SELECT "&colunm&" as count_all_total FROM "&table&" WHERE "&condition&exp&value&" "&extra
rs.open sql,conn,1,1
gettotal = rs("count_all_total")
call closethis()
End Function
'-------------------------------------------
' 操作函数
'-------------------------------------------
Private Function Initialize
'on error resume next
set conn=server.CreateObject("adodb.connection")
conn.mode = adModeReadWrite '设置provider 的访问权限
conn.ConnectionString = connstr
conn.connectiontimeout = 30 '设置等待时间
conn.open
End Function
'-------------------------------------------
' 数据库连接类型
'-----------------------------------------
Private Function selectconnstr(ty)
select case ty
case "DSN-less"
connstr = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ="&Server.MapPath(dbpath)
case "SQL server"
connstr = "{SQL Server};Server=(Local);UID=;PWD=;"&"database="&dbpath&";"
case "Access OLE DB"
connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(dbpath) &";Persist Security Info=False;"
end select
End Function
'-------------------------------------------
' 释放资源
'--------------------------------------------
Private Function closethis
rs.close
set rs=nothing
End Function
'-------------------------------
' 析构函数
'---------------------------------
Private Sub class_terminate
conn.close
set conn = nothing
End Sub
'---------------------------
' 进位运算
'-----------------------------
Public Function modint(number,num)
if (number mod num) = 0 then modint = int(number/num) else modint = int(number/num) + 1 end if
End Function
'----------------------------
' 功能：输出分页样式数字分页
' 参数：
'----------------------------
Public Function showPage()
Dim t_,per,nex
if currentPage > 1 and currentPage < thisPageCount then
per = "<a href="""&echoPath(currentPage-1)&""" >上一页</a>"
nex = "<a href="""&echoPath(currentPage+1)&""" >下一页</a>"
end if
if currentPage > 1 and currentPage = thisPageCount then
per = "<a href="""&echoPath(currentPage-1)&""" >上一页</a>"
end if
if currentPage = 1 and currentPage < thisPageCount then
nex = "<a href="""&echoPath(currentPage+1)&""" >下一页</a>"
end if
t_ = per
For m = 1 to thisPageCount
if currentPage = m then cur = "cur" else cur = "" end if
t_ = t_ & "<a href="""&echoPath(m)&""" calss="""&cur&""">"&m&"</a>"
Next
t_ = t_ & "totla:" & thisPageCount &",current:"& currentPage & thisRecordCount
t_ = t_ & nex
showPage = t_
End Function
'--------------------------
' 输出路径
'--------------------------
Private Function echoPath(y)
Dim url
url = Request.ServerVariables("Query_String")
if url="" then
echoPath = "?page="&y
else
if instr(url,"page=") = 0 then
echoPath = "?"&url&"&page="&y
else
dim p,ist
p = split(url,"&")
For f = Lbound(p) to Ubound(p)
if instr(p(f),"page=")<>0 then ist = p(f)
Next
if ist<>"" then echoPath = "?"&replace(url,ist,"page="&y) else echoPath = "?"&url&"&page="&y end if
end if
end if
End Function
End Class
%>
使用方法实例：
<!--#include file="class-mysql.asp"-->
<ul>
<%
set g = new MySql
g.ispageshow(4)
m = g.selectRecord("bigclass","*",1,"<",2, "")
For x = Lbound(m) to Ubound(m)
%>
<li><%=m(x,0)%>,<%=m(x,1)%></li>
<%
Next
%>
<li><%=g.showPage()%></li>
<%
set g = nothing
%>
</ul>
参考：https://blog.csdn.net/weixin_34237596/article/details/91940350
