为ASP封装的数据库操作类

<%

'site: http://blog.youkuaiyun.com/yagas

'email: yagas60@21cn.com

'author: yagas

'游标类型
Const adOpenForwardOnly     = 0
Const adOpenKeyset          = 1
Const adOpenDynamic         = 2
Const adOpenStatic          = 3
'锁类型
Const adLockReadOnly        = 1
Const adLockPessimistic     = 2
Const adLockOptimistic      = 3
Const adLockBatchOptimistic = 4

Class Database
 Private conn, dbStr
 
 '**
 ' 构造函数
 Private Sub Class_Initialize()
  Set conn = Server.CreateObject("ADODB.Connection")
 End Sub
 
 '**
 ' 析构函数
 Private Sub Class_Terminate()
  Set rest = Nothing
  Set conn = Nothing
 End Sub

 '**
 ' 设置数据库路径
 Public Sub connStr(FilePath)
  dbStr = "provider=microsoft.jet.oledb.4.0;data source=" & server.mappath(FilePath)
 End Sub
 
 '**
 ' 链接数据库
 Public Sub open()
  conn.open dbStr
 End Sub
 
 '**
 ' 获取单条数据
 Public Function find(table, conditions, order, fields)
  Dim SQL, rest
  Set rest = Server.CreateObject("ADODB.Recordset")
  
  If fields="" Then fields = "*" End If

  SQL = "SELECT TOP 1 " & fields & " FROM [" & table & "]"
  If IsArray(conditions) Then
   SQL = SQL & " WHERE " & Join(conditions, " AND ")
  ElseIf conditions<>"" Then
   SQL = SQL & " WHERE " & conditions
  End If
  
  If order<>"" Then
   SQL = SQL & " ORDER BY " & order
  End If

  rest.open SQL, conn, adOpenStatic, adLockReadOnly
  
  Set rs = rest
  'rest.Close
  Set find = rs
 End Function

 '**
 ' 分页查询数据
 Public Function findAll(table, conditions, order, page, rows, fields)
  Dim rest, Format, SQL, Where, OutRows, Params(3), Params1(1)  
  Set rest = Server.CreateObject("ADODB.Recordset")
  
  Format    = "SELECT TOP %s %s FROM [%s]"
  Where     = " WHERE %s "

  OutRows = page * rows
  Params(0) = rows
  Params(1) = fields
  Params(2) = table
  '基础查询
  SQL = sprintf(Format, Params)
  '组合查询条件
  If IsArray(conditions) Then
   Params1(0) = Join(conditions, " AND ")
   SQL = SQL & sprintf(Where, Params1)
  ElseIf conditions<>"" Then
   Params1(0) = conditions
   SQL = SQL & sprintf(Where, Params1)
  End If
  '如果不是第一页的内容
  If OutRows>0 Then
   Params(0) = OutRows
   Params(1) = "[id]"
   Params(2) = table
   If IsArray(conditions) Or conditions<>"" Then
    SQL = SQL & " AND [id] NOT IN (" & sprintf(Format, Params) & ") "
   Else
    SQL = SQL & " WHERE [id] NOT IN (" & sprintf(Format, Params) & ") "
   End If
  End If

  '是否进行排序
  If order<>"" Then
   SQL = SQL & " ORDER BY " & order
  End If
  
  rest.open SQL, conn, adOpenStatic, adLockReadOnly
  Set findAll = rest
 End Function

End Class

 

 

 

'**
' 格式化字符串
Function sprintf(Format, params)
 Dim strArr, num, newStr
 strArr = Split(Format, "%s")
 If(UBound(strArr)<>UBound(params)) then
  sprintf = Format
  Exit Function
 End If

 For num = LBound(strArr) To UBound(strArr)
  newStr = newStr & strArr(num) & params(num)
 Next
 sprintf = newStr
End Function
%>

 

---------------------------------------------------------------

'使用方法

<%
Dim db
Dim news

Set db = New Database
db.connStr "#database.mdb"
db.open

Set news = db.find("ieb_articles", "", "", "*")
If news.BOF and news.EOF Then
 Response.Write "没有数据"
Else
 Response.Write news("title") & VBCRLF
End If
news.Close

 

Set news = db.findAll("ieb_articles", "", "", 2, 3, "*")
if news.BOF and news.EOF Then
 Response.Write "没有数据"
 news.Close
Else
 Do While Not news.EOF
  Response.Write news("title") & VBCRLF
  news.MoveNext
 Loop
End If
%>

 

 

未完，待续....