asp数据库操作类
文章类别：
Asp | 发表日期:2010-1-26 13:53:08
示例：
复制内容到剪贴板代码:
<%
Dim DB,rs,i,temp
Set DB = New DB_
'感谢小秦同学的回复,之前竟忘连接数据库了。-_!!
DB.Connection("acsql") = "data.mdb"
'执行sql查询
Set rs = DB("select * from [tableName] where [条件]")
Set rs = Nothing
'分页,默认每页20条记录
DB.pagesize = 30
Set rs = DB("page:select * from [tableName]")
For i = 0 To 29
If rs.Eof Then Exit For
Next
Set rs = Nothing
'输出默认分页样式
Response.Write(DB.pager())
'插入新记录,无需判断数据类型,自动生成sql语句
DB("fieldName") = value
DB("fieldName2") = value
DB("insert:tableName")
'更新记录
DB("fieldName") = value
DB("fieldName2") = value
DB("where:id=1")
DB("update:tableName")
'删除记录
DB("where:id=1")
DB("delete:tableName")
Set DB = Nothing
%>
复制内容到剪贴板代码:
<%
Class DB_
Private conn,rs,i,length,tablePrefixKey,tablePrefixValue,temp
Private sql,sqlSet,sqlItemKey,sqlItemValue,sqlWhere
Public rowsAffected
Public pagesize,pageID,page,pagesCount
Private pageDatasCount,pageCookiesID
Private Sub Class_Initialize()
i = 0
length = 0
tablePrefixKey = ""
tablePrefixValue = "^o^"
temp = ""
sql = ""
sqlSet = ""
sqlItemKey = ""
sqlItemValue = ""
pagesize = 20
pageID = "page"
page = 1
pageCookiesID = ""
End Sub
Private Sub class_terminate()
If IsObject(rs) Then rs.Close : Set rs = Nothing
If IsObject(conn) Then conn.Close : Set conn = Nothing
End Sub
Public Property Let Prefix(Byval k,Byval v)
tablePrefixKey = k
tablePrefixValue = v
End Property
Public Property Let Connection(Byval k,Byval v)
Select Case LCase(k)
Case "acsql","access"
Dim connstr
connstr = "Provider=Microsoft.Jet.OLEdb.4.0;Data Source=" & Server.MapPath(v)
Set conn = Server.CreateObject("Adodb.connection")
conn.Open connstr
Case "mssql"
Dim connstr
temp = Split(v,":")
connstr = "Provider=Sqloledb;Data Source=" & temp(0) & ";Initial Catalog=" & temp(1) & ";User ID=" & temp(2) & ";Password=" & temp(3) & ";"
Set conn = Server.CreateObject("Adodb.Connection")
conn.Open connstr
End Select
End Property
Public Property Let Default(Byval k,Byval v)
temp = VarType(v)
sqlItemKey = sqlItemKey & "," & k
Select Case temp
Case 1,2,3,4,5,6,11
sqlSet = sqlSet & "," & k & "=" & v
sqlItemValue = sqlItemValue & "," & v
Case 7
sqlSet = sqlSet & "," & k & "=#" & v & "#"
sqlItemValue = sqlItemValue & ",#" & v & "#"
Case Else
sqlSet = sqlSet & "," & k & "='" & Replace(v,"'","''") & "'"
sqlItemValue = sqlItemValue & ",'" & Replace(v,"'","''") & "'"
End Select
End Property
Private Function Query(Byval s)
s = Replace(s,tablePrefixKey,tablePrefixValue,1,1,1)
Set Query = conn.Execute(s,rowsAffected,&H0001)
End Function
Private Function Alter(Byval s,Byval type_)
Alter = False
If Trim(s) = "" Then Exit Function
sqlSet = Replace(sqlSet,",","",1,1)
sqlItemKey = Replace(sqlItemKey,",","",1,1)
sqlItemValue = Replace(sqlItemValue,",","",1,1)
If sqlWhere <> "" Then
If LCase(Left(sqlWhere,3)) = "and" Then
sqlWhere = " where " & Replace(sqlWhere,"and","",1,1,1)
Else
sqlWhere = " where " & sqlWhere
End If
End If
Select Case type_
Case "insert" : sql = "INSERT INTO " & s & "(" & sqlItemKey & ") VALUES(" & sqlItemValue & ")"
Case "update" : sql = "UPDATE " & s & " SET " & sqlSet & sqlWhere
Case "delete" : sql = "DELETE * FROM " & s & sqlWhere
End Select
sqlSet = ""
sqlItemKey = ""
sqlItemValue = ""
sqlWhere = ""
Query(sql)
Alter = True
End Function
Public Default Property Get Default(Byval s)
temp = LCase(Left(Trim(s),6))
Select Case True
Case temp = "insert" : Default = Alter(Replace(s,"insert:","",1,1,1),"insert")
Case temp = "update" : Default = Alter(Replace(s,"update:","",1,1,1),"update")
Case temp = "delete" : Default = Alter(Replace(s,"delete:","",1,1,1),"delete")
Case temp = "where:" : sqlWhere = Replace(s,"where:","",1,1,1)
Case InStr(temp,"page:")=1 : Set Default = ExecPage(Replace(s,"page:","",1,1,1))
Case Else : Set Default = Query(s)
End Select
End Property
Private Function ExecPage(Byval s)
If IsObject(rs) Then rs.Close : Set rs = Nothing
pageCookiesID = pageCookiesID & pageID
pageDatasCount = Request.Cookies("O:temp:page")(pageCookiesID)
page = Request.QueryString(pageID)
If Not IsNumeric(page) Or page = "" Then page = 1
page = Int(page)
If Not IsNumeric(pageDatasCount) Or pageDatasCount = "" Then pageDatasCount = 0
Set rs = Server.CreateObject("Adodb.RecordSet")
rs.Open Replace(s,tablePrefixKey,tablePrefixValue,1,1,1),conn,1,1,&H0001
If pageDatasCount = 0 Then
Response.Cookies("O:temp:page")(pageCookiesID) = rs.RecordCount
pageDatasCount = Request.Cookies("O:temp:page")(pageCookiesID)
End If
If Not rs.Eof Then
pagesCount = -Int(-pageDatasCount / pagesize)
If page > pagesCount Then page = pagesCount
rs.AbsolutePosition = Abs((page-1)) * pagesize + 1
If page > pagesCount Then page = pagesCount
If page < 1 Then page = 1
End If
Set ExecPage = rs
End Function
Public Function Pager()
Dim return_,pageStart,pageEnd,pageMarge
pageStart = 1 : pageMarge = 11
If page > pageMarge / 2 Then pageStart = Int(page + 1 - pageMarge / 2)
PageEnd = pageStart + pageMarge - 1
If pageEnd > pagesCount Then pageEnd = pagesCount
return_ = "<div class=""page""><em>" & pageDatasCount & "|Page：" & page & "/" & pagesCount & "</em>"
If page > 1 Then
return_ = return_ & "<a href=""?" & pageID & "=1"">«</a><a href=""?" & pageID & "=" & page - 1 & """><</a>"
End If
For i = pageStart To pageEnd
If i = page Then
return_ = return_ & "<span>" & i & "</span>"
Else
return_ = return_ & "<a href=""?" & pageID & "=" & i & """>" & i & "</a>"
End If
Next
If page < pagesCount Then
return_ = return_ & "<a href=""?" & pageID & "=" & page + 1 & """>></a><a href=""?" & pageID & "=" & pagesCount & """>»</a>"
End If
return_ = return_&"<kbd><input type=""text"" size=""3"" style=""width:40px; text-align:center;"" onkeydown=""if(event.keyCode==13 && !(this.value=='')) {window.location='?" & pageID & "='+this.value; return false;}"" /></kbd></div>"
Pager = return_
End Function
End Class
%>
数据库操作类中分页部分总数据cookies名请自行更改，我最初用的是MD5加密后的sql语句
QQ：2693987339(点击联系)购买教程光盘
:
