asp 数据库连接 类

asp教程 数据库教程连接 类

考虑到大多数人的使用习惯，在数据库连接上使用了公共过程，所以需要大家在代码里自行修改，如果你已经建立了数据库连接，把这几行注释掉就行了。代码中内置了mssql,access,mysql教程,oracle4种数据库的连接方式，当然你也可以自行在源代码中增加或删除。修改例如：

dim a : a = creatconn(0, "testdata", "localhost", "username", "userpassword")
dim b : b = creatconn(1, "data/%testdb%.mdb", "", "", "")

说明一下，第1个参数可以是字符串。如果是使用access，则第2个参数输入相对路径和绝对路径都是可以的，如有密码也可以在第5个参数中输入，如：

dim c : c = creatconn("access", "e:mywebdata%testdb%.mdb", "", "", "mdbpassword")

<%
'==========================================================================
'文件名称：clsdbctrl.asp
'功　　能：数据库操作类
'作　　者：coldstone (coldstone[在]qq.com)
'程序版本：v1.0.5
'完成时间：2005.09.23
'修改时间：2007.10.30
'版权声明：可以在任意作品中使用本程序代码，但请保留此版权信息。
'          如果你修改了程序中的代码并得到更好的应用，请发送一份给我，谢谢。
'==========================================================================

dim a : a = creatconn(0, "master", "localhost", "sa", "") 'mssql数据库
'dim a : a = creatconn(1, "data/%testdb%.mdb", "", "", "") 'access数据库
'dim a : a = creatconn(1, "e:mywebdata%testdb%.mdb", "", "", "mdbpassword")
dim conn
'openconn() '在加载时就建立的默认连接对象conn,默认使用数据库a
sub openconn : set conn = oc(a) : end sub
sub closeconn : co(conn) : end sub

function oc(byval connstr)
 on error resume next
 dim objconn
 set objconn = server.createobject("adodb.connection")
 objconn.open connstr
 if err.number <> 0 then
  response.write("

数据库服务器端连接错误，请与网站管理员联系。

")
  'response.write("错误信息：" & err.description)
  objconn.close
  set objconn = nothing
  response.end
 end if
 set oc = objconn
end function

sub co(obj)
 on error resume next
 set obj = nothing
end sub

function creatconn(byval dbtype, byval strdb, byval strserver, byval struid, byval strpwd)
 dim temps教程tr
 select case dbtype
  case "0","mssql"
   tempstr = "driver={sql server};server="&strserver&";uid="&struid&";pwd="&strpwd&";database="&strdb
  case "1","access"
   dim tdb : if instr(strdb,":")>0 then : tdb = strdb : else : tdb = server.mappath(strdb) : end if
   tempstr = "provider=microsoft.jet.oledb.4.0;data source="&tdb&";jet oledb:database password="&strpwd&";"
  case "3","mysql"
   tempstr = "driver={mysql};server="&strserver&";port=3306;option=131072;stmt=; database="&strdb&";uid="&struid&";pwd="&strpwd&";"
  case "4","oracle"
   tempstr = "driver={microsoft odbc for oracle};server="&strserver&";uid="&struid&";pwd="&strpwd&";"
 end select
 creatconn = tempstr
end function

class dbctrl
 private debug
 private idbconn
 private idberr
 
 private sub class_initialize()
  debug = true     '调试模式是否开启
  idberr = "出现错误："
  if isobject(conn) then
   set idbconn = conn
  end if
 end sub
 
 private sub class_terminate()
  set idbconn = nothing
  if debug and idberr<>"出现错误：" then response.write(idberr)
 end sub
 
 public property let dbconn(pdbconn)
  if isobject(pdbconn) then
   set idbconn = pdbconn
  else
   set idbconn = conn
  end if
 end property
 
 public property get dberr()
  dberr = idberr
 end property
 
 public property get version
  version = "asp database ctrl v1.0 by coldstone"
 end property

 public function autoid(byval tablename)
  on error resume next
  dim m_no,sql, m_firtempno
  set m_no=server.createobject("adodb.recordset")
  sql="select * from ["&tablename&"]"
  m_no.open sql,idbconn,3,3
  if m_no.eof then
   autoid=1
  else
   do while not m_no.eof
    m_firtempno=m_no.fields(0).value
    m_no.movenext
      if m_no.eof then
      autoid=m_firtempno+1
      end if
   loop
  end if
  if err.number <> 0 then
   idberr = idberr & "无效的查询条件！
"
   if debug then idberr = idberr & "错误信息："& err.description
   response.end()
   exit function
  end if
  m_no.close
  set m_no = nothing
 end function

 public function getrecord(byval tablename,byval fieldslist,byval condition,byval orderfield,byval shown)
  on error resume next
  dim rstrecordlist
  set rstrecordlist=server.createobject("adodb.recordset")
   with rstrecordlist
   .activeconnection = idbconn
   .cursortype = 3
   .locktype = 3
   .source = wgetrecord(tablename,fieldslist,condition,orderfield,shown)
   .open
   if err.number <> 0 then
    idberr = idberr & "无效的查询条件！
"
    if debug then idberr = idberr & "错误信息："& err.description
    .close
    set rstrecordlist = nothing
    response.end()
    exit function
   end if
  end with
  set getrecord=rstrecordlist
 end function
 
 public function wgetrecord(byval tablename,byval fieldslist,byval condition,byval orderfield,byval shown)
  dim strselect
  strselect="select "
  if shown > 0 then
   strselect = strselect & " top " & shown & " "
  end if
  if fieldslist<>"" then
   strselect = strselect & fieldslist
  else
   strselect = strselect & " * "
  end if
  strselect = strselect & " from [" & tablename & "]"
  if condition <> "" then
   strselect = strselect & " where " & valuetosql(tablename,condition,1)
  end if
  if orderfield <> "" then
   strselect = strselect & " order by " & orderfield
  end if
  wgetrecord = strselect
 end function

 public function getrecordbysql(byval strselect)
  on error resume next
  dim rstrecordlist
  set rstrecordlist=server.createobject("adodb.recordset")
   with rstrecordlist
   .activeconnection =idbconn
   .cursortype = 3
   .locktype = 3
   .source = strselect
   .open
   if err.number <> 0 then
    idberr = idberr & "无效的查询条件！
"
    if debug then idberr = idberr & "错误信息："& err.description
    .close
    set rstrecordlist = nothing
    response.end()
    exit function
   end if
  end with
  set getrecordbysql = rstrecordlist
 end function

 public function getrecorddetail(byval tablename,byval condition)
  on error resume next
  dim rstrecorddetail, strselect
  set rstrecorddetail=server.createobject("adodb.recordset")
  with rstrecorddetail
   .activeconnection =idbconn
   strselect = "select * from [" & tablename & "] where " & valuetosql(tablename,condition,1)
   .cursortype = 3
   .locktype = 3
   .source = strselect
   .open
   if err.number <> 0 then
    idberr = idberr & "无效的查询条件！
"
    if debug then idberr = idberr & "错误信息："& err.description
    .close
    set rstrecorddetail = nothing
    response.end()
    exit function
   end if
  end with
  set getrecorddetail=rstrecorddetail
 end function

 public function addrecord(byval tablename, byval valuelist)
  on error resume next
  doexecute(waddrecord(tablename,valuelist))
  if err.number <> 0 then
   idberr = idberr & "写入数据库出错！
"
   if debug then idberr = idberr & "错误信息："& err.description
   'doexecute "rollback tran tran_insert" '如果存在添加事务（事务滚回）
   addrecord = 0
   exit function
  end if
  addrecord = autoid(tablename)-1
 end function
 
 public function waddrecord(byval tablename, byval valuelist)
  dim tempsql, tempfiled, tempvalue
  tempfiled = valuetosql(tablename,valuelist,2)
  tempvalue = valuetosql(tablename,valuelist,3)
  tempsql = "insert into [" & tablename & "] (" & tempfiled & ") values (" & tempvalue & ")"
  waddrecord = tempsql
 end function

 public function updaterecord(byval tablename,byval condition,byval valuelist)
  on error resume next
  doexecute(wupdaterecord(tablename,condition,valuelist))
  if err.number <> 0 then
   idberr = idberr & "更新数据库出错！
"
   if debug then idberr = idberr & "错误信息："& err.description
   'doexecute "rollback tran tran_update" '如果存在添加事务（事务滚回）
   updaterecord = 0
   exit function
  end if
  updaterecord = 1
 end function

 public function wupdaterecord(byval tablename,byval condition,byval valuelist)
  dim tmpsql
  tmpsql = "update ["&tablename&"] set "
  tmpsql = tmpsql & valuetosql(tablename,valuelist,0)
  tmpsql = tmpsql & " where " & valuetosql(tablename,condition,1)
  wupdaterecord = tmpsql
 end function

 public function deleterecord(byval tablename,byval idfieldname,byval idvalues)
  on error resume next
  dim sql
  sql = "delete from ["&tablename&"] where ["&idfieldname&"] in ("
  if isarray(idvalues) then
   sql = sql & "select ["&idfieldname&"] from ["&tablename&"] where " & valuetosql(tablename,idvalues,1)
  else
   sql = sql & idvalues
  end if
  sql = sql & ")"
  doexecute(sql)
  if err.number <> 0 then
   idberr = idberr & "删除数据出错！
"
   if debug then idberr = idberr & "错误信息："& err.description
   'doexecute "rollback tran tran_delete" '如果存在添加事务（事务滚回）
   deleterecord = 0
   exit function
  end if
  deleterecord = 1
 end function
 
 public function wdeleterecord(byval tablename,byval idfieldname,byval idvalues)
  on error resume next
  dim sql
  sql = "delete from ["&tablename&"] where ["&idfieldname&"] in ("
  if isarray(idvalues) then
   sql = sql & "select ["&idfieldname&"] from ["&tablename&"] where " & valuetosql(tablename,idvalues,1)
  else
   sql = sql & idvalues
  end if
  sql = sql & ")"
  wdeleterecord = sql
 end function

 public function readtable(byval tablename,byval condition,byval getfieldnames)
  on error resume next
  dim rstgetvalue,sql,basecondition,arrtemp,arrstr,tempstr,i
  tempstr = "" : arrstr = ""
  '给出sql条件语句
  basecondition = valuetosql(tablename,condition,1)
  '读取数据
  set rstgetvalue = server.createobject("adodb.recordset")
  sql = "select "&getfieldnames&" from ["&tablename&"] where "&basecondition
  rstgetvalue.open sql,idbconn,3,3
  if rstgetvalue.recordcount > 0 then
   if instr(getfieldnames,",")>0 then
    arrtemp = split(getfieldnames,",")
    for i = 0 to ubound(arrtemp)
     if i<>0 then arrstr = arrstr &chr(112)&chr(112)&chr(113)
     arrstr = arrstr & rstgetvalue.fields(i).value
    next
    tempstr = split(arrstr,chr(112)&chr(112)&chr(113))
   else
    tempstr = rstgetvalue.fields(0).value
   end if
  end if
  if err.number <> 0 then
   idberr = idberr & "获取数据出错！
"
   if debug then idberr = idberr & "错误信息："& err.description
   rstgetvalue.close()
   set rstgetvalue = nothing
   exit function
  end if
  rstgetvalue.close()
  set rstgetvalue = nothing
  readtable = tempstr
 end function

 public function c(byval objrs)
  objrs.close()
  set objrs = nothing
 end function
 
 private function valuetosql(byval tablename, byval valuelist, byval stype)
  dim strtemp
  strtemp = valuelist
  if isarray(valuelist) then
   strtemp = ""
   dim rstemp, currentfield, currentvalue, i
   set rstemp = server.createobject("adodb.recordset")
   with rstemp
    .activeconnection = idbconn
    .cursortype = 3
    .locktype = 3
    .source ="select * from [" & tablename & "] where 1 = -1"
    .open
    for i = 0 to ubound(valuelist)
     currentfield = left(valuelist(i),instr(valuelist(i),":")-1)
     currentvalue = mid(valuelist(i),instr(valuelist(i),":")+1)
     if i <> 0 then
      select case stype
       case 1
        strtemp = strtemp & " and "
       case else
        strtemp = strtemp & ", "
      end select
     end if
     if stype = 2 then
      strtemp = strtemp & "[" & currentfield & "]"
     else
      select case .fields(currentfield).type
       case 7,133,134,135,8,129,200,201,202,203
        if stype = 3 then
         strtemp = strtemp & "'"¤tvalue&"'"
        else
         strtemp = strtemp & "[" & currentfield & "] = '"¤tvalue&"'"
        end if
       case 11
        if ucase(cstr(trim(currentvalue)))="true" then
         if stype = 3 then
          strtemp = strtemp & "1"
         else
          strtemp = strtemp & "[" & currentfield & "] = 1"
         end if
        else
         if stype = 3 then
          strtemp = strtemp & "0"
         else
          strtemp = strtemp & "[" & currentfield & "] = 0"
         end if
        end if
       case else
        if stype = 3 then
         strtemp = strtemp & currentvalue
        else
         strtemp = strtemp & "[" & currentfield & "] = " & currentvalue
        end if
      end select
     end if
    next
   end with
   if err.number <> 0 then
    idberr = idberr & "生成sql语句出错！
"
    if debug then idberr = idberr & "错误信息："& err.description
    rstemp.close()
    set rstemp = nothing
    exit function
   end if
   rstemp.close()
   set rstemp = nothing
  end if
  valuetosql = strtemp
 end function

 private function doexecute(byval sql)
  dim executecmd
  set executecmd = server.createobject("adodb.command")
  with executecmd
   .activeconnection = idbconn
   .commandtext = sql
   .execute
  end with
  set executecmd = nothing
 end function
end class
%>

使用如下代码应用此类：
一个数据库连接：

<%
openconn() '打开数据库连接
dim db : set db = new dbctrl '建立对像
'your code here......
co(db) : closeconn() '释放对象，关闭数据库连接
%>

或者(一个或者多个数据库连接)：

<%
dim db1 : set db1 = new dbctrl : db1.dbconn = oc(a)
dim db2 : set db2 = new dbctrl : db2.dbconn = oc(b)
'your code here......
co(db1) : co(db2)
%>

4.具体操作的例子可以参考各函数说明内的代码示例。

方法和属性概览（详细用法及例子在下面）：

引用：
creatconn 生成数据库连接字符串
oc 建立数据库连接
co 释放对像
openconn 打开默认数据库连接
closeconn 关闭默认数据库连接
dbctrl.dbconn 属性，获取要操作的数据库连接，默认值为conn
dbctrl.dberr 属性，只读，输出捕获的错误信息
dbctrl.version 属性，只读，程序版本信息
dbctrl.autoid 方法，自动获取唯一序列号
dbctrl.getrecord 方法，取得符合条件的纪录集
dbctrl.getrecordbysql 方法，根据sql语句取得纪录集
dbctrl.getrecorddetail 方法，根据某一条指定纪录的详细数据
dbctrl.addrecord 方法，添加一个新的纪录
dbctrl.updaterecord 方法，根据指定条件更新纪录
dbctrl.deleterecord 方法，删除符合条件的纪录
dbctrl.readtable 方法，根据指定条件获取某条纪录中的其他字段的内容
dbctrl.c 方法，关闭纪录集对像
dbctrl.wgetrecord,
dbctrl.waddrecord,
dbctrl.wupdaterecord,
dbctrl.wdeleterecord 这4个方法是取得相应的操作(前面加w)的sql语句