最新下载
热门教程
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9
- 10
asp 数据库连接 类
时间:2022-06-30 10:09:01 编辑:袖梨 来源:一聚教程网
考虑到大多数人的使用习惯,在数据库连接上使用了公共过程,所以需要大家在代码里自行修改,如果你已经建立了数据库连接,把这几行注释掉就行了。代码中内置了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 subfunction 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 functionsub co(obj)
on error resume next
set obj = nothing
end subfunction 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 propertypublic 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 functionpublic 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 functionpublic 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 functionpublic 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 functionpublic 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 functionpublic 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 functionpublic 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 functionpublic 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 functionpublic 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 functionpublic 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 functionprivate 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语句