發一個ASP的ADODB類代碼
反正我現在用這個做了很多站，自己覺得還是滿有用的，所以拿出來和大家分享一下。支持三種數據庫連接：mssql2000，mssql2005，access
三種方法：
select_table(sql)
表查詢，返回true或false
當sql語句出錯，或空記錄時返回false，否則true
update_table(sql)
表更新，包括update,delete
成功執行返回true，否則返回false，updated為執行后影響記錄行數。
insert_table(sql,table,id_column)
table為表名，id_column為表中自動編號，自增字段。
當成功執行返回true，否則返回false，指定table，id_column后，將返回最后添加記錄所產生的自增id。
select_table()相關方法select_page(page,psize)
分頁處理，page為當前頁，psize為每頁記錄行數。
所有操作時，自動檢測數據庫鏈接和rs是否打開，執行后將自動關閉數據庫鏈接。
示例：
set db = new adodb_class
if db.select_table("select * from news order by id desc") then
page = request("page")
select_page(page,20)'每頁20條
for i=1 to 20
response.write db.rs("title")'類內置rs，不可變
db.rs.movenext
if db.rs.eof then exit for
next
end if
db.rspage = 總頁數，db.nowpage= 經過處理后當前頁，db.rscounts數總記錄數量。
if db.update_table("delete from news where ispass=1") then'update同樣
response.write "共刪除"&db.updated&"行"
end if
call db.insert_table("insert into news (title,content) values ('"&title&"','"&content&"')","news","id")
response.write "最后添加id為"&db.insertd
在頁面最尾可輸出db.readcounts 為查詢數據庫次數。
--------------------------------------------
本類好處就是你不必擔心忘記關閉數據庫鏈接，不用頻繁set rs = server.recordset("adodb.recordset"),也不用set rs = nothing
缺點就是翻頁用的傳統方式。rs.absolutepage = rs.pagesize
----------------------------------------------------------
<%
'/******kshop******/
' adodb_class.asp 數據庫操作類
' version 1.0
' copyright [email]simple_1982@hotmail.com[/email]
' e-mail [email]xsg2005@163.com[/email]
'/*****************/
class adodb_class
dim conn,connstr,rs
dim dbclass'數據庫類型access,sql2000,sql2005三個值之一
dim sqldbname,sqluser,sqlpass,sqlserver
dim sqlaccess
dim selectd,insertd,updated
dim rscounts,rspage,readcounts,nowpage '記錄集 總數/頁數 查詢次數
private sub class_initialize()
sqldbname = ""
sqluser = ""
sqlpass = ""
sqlserver = ""
sqlaccess = "/simple_date/simple_xiehui.mdb"
rscounts = 0:rspage = 1:readcounts = 0:nowpage = 1
call openconn("access")
selectd = 0
insertd = 0
updated = 0
end sub
'********打開數據庫鏈接******************
private sub accessconn()
connstr="provider=microsoft.jet.oledb.4.0;data source=" & server.mappath(sqlaccess) &";persist security info=false"
end sub
private sub sql2kconn()
connstr = "driver={sql server};server="&sqlserver&";uid="&sqluser&";pwd="&sqlpass&";database="&sqldbname
end sub
private sub sql2k05conn()
connstr="provider=sqlncli.1;password="&sqlpass&";user id="&sqluser&";initial catalog="&sqldbname&";data source="&sqlserver
end sub
private sub openconn(db_class)
dbclass = db_class
select case db_class
case "access":call accessconn()
case "sql2000":call sql2kconn()
case "sql2005":call sql2k05conn()
end select
on error resume next
set conn = server.createobject("adodb.connection")
conn.open connstr
if err then
response.write "數據庫鏈接失敗
sqlstring = " + connstr
response.end()
err.clear
end if
end sub
'**********結束/查詢構造*************
public function select_table(sql)
if not not isempty(conn) or isnull(conn) then
call openconn(dbclass)
elseif conn.state = 0 then
call openconn(dbclass)
end if
on error resume next
set rs = server.createobject("adodb.recordset")
rs.open sql,conn,1,1
if err then
select_table = false
rs.close
exit function
err.clear
end if
if rs.eof and rs.bof then
rs.close
select_table = false
else
select_table = true
end if
readcounts = readcounts + 1
end function
'分頁處理
public function select_page(page,psize)
if isnull(page) or page = "" then page = 1
if page < 1 then page = 1
if rs.state = 1 then
if not rs.eof then
rs.pagesize = psize
rspage = rs.pagecount
rscounts = rs.recordcount
if int(page) > int(rspage) then page = rspage
rs.absolutepage = page:nowpage = page
end if
end if
end function
'更新記錄
public function update_table(sql)
if not isempty(conn) or isnull(conn) then
call openconn(dbclass)
elseif conn.state = 0 then
call openconn(dbclass)
end if
on error resume next
if sql <> "" then
conn.execute sql,updated
if err then
update_table = false
err.clear
else
update_table = true
end if
else
update_table = false
end if
conn.close
set conn = nothing
end function
'增加
'輸入：insert sql語句，表名，自增字段
public function insert_table(sql,table,id_column)
if not isempty(conn) or isnull(conn) then
call openconn(dbclass)
elseif conn.state = 0 then
call openconn(dbclass)
end if
on error resume next
if sql <> "" then
conn.execute(sql)
if err then
insert_table = false:err.clear
else
insert_table = true
end if
'獲得最后增加id
if table <> "" and id_column <> "" then
set ds = conn.execute("select "&id_column&" from "&table&" order by "&id_column&" desc")
end if
if err then
insertd = 0:err.clear
else insertd = ds(0)
end if
set ds = nothing
closed()
else
insert_table = false
end if
end function
'關閉數據庫鏈接
public function closed()
if not isempty(rs) and not isnull(rs) then
if rs.state = 1 then
rs.close
end if
end if
rscounts = 0:rspage = 1:nowpage = 1
end function
'**********釋放類************
private sub class_terminate()
readcounts = 0:rscounts = 0:rspage = 0
if not isempty(conn) and not isnull(conn) then
if conn.state = 1 then
conn.close
end if
set conn = nothing
end if
if not isempty(rs) then
if not isnull(rs) then
if rs.state=1 then
rs.close
end if
set rs=nothing
end if
end if
end sub
end class
%>
三種方法：
select_table(sql)
表查詢，返回true或false
當sql語句出錯，或空記錄時返回false，否則true
update_table(sql)
表更新，包括update,delete
成功執行返回true，否則返回false，updated為執行后影響記錄行數。
insert_table(sql,table,id_column)
table為表名，id_column為表中自動編號，自增字段。
當成功執行返回true，否則返回false，指定table，id_column后，將返回最后添加記錄所產生的自增id。
select_table()相關方法select_page(page,psize)
分頁處理，page為當前頁，psize為每頁記錄行數。
所有操作時，自動檢測數據庫鏈接和rs是否打開，執行后將自動關閉數據庫鏈接。
示例：
set db = new adodb_class
if db.select_table("select * from news order by id desc") then
page = request("page")
select_page(page,20)'每頁20條
for i=1 to 20
response.write db.rs("title")'類內置rs，不可變
db.rs.movenext
if db.rs.eof then exit for
next
end if
db.rspage = 總頁數，db.nowpage= 經過處理后當前頁，db.rscounts數總記錄數量。
if db.update_table("delete from news where ispass=1") then'update同樣
response.write "共刪除"&db.updated&"行"
end if
call db.insert_table("insert into news (title,content) values ('"&title&"','"&content&"')","news","id")
response.write "最后添加id為"&db.insertd
在頁面最尾可輸出db.readcounts 為查詢數據庫次數。
--------------------------------------------
本類好處就是你不必擔心忘記關閉數據庫鏈接，不用頻繁set rs = server.recordset("adodb.recordset"),也不用set rs = nothing
缺點就是翻頁用的傳統方式。rs.absolutepage = rs.pagesize
----------------------------------------------------------
<%
'/******kshop******/
' adodb_class.asp 數據庫操作類
' version 1.0
' copyright [email]simple_1982@hotmail.com[/email]
' e-mail [email]xsg2005@163.com[/email]
'/*****************/
class adodb_class
dim conn,connstr,rs
dim dbclass'數據庫類型access,sql2000,sql2005三個值之一
dim sqldbname,sqluser,sqlpass,sqlserver
dim sqlaccess
dim selectd,insertd,updated
dim rscounts,rspage,readcounts,nowpage '記錄集 總數/頁數 查詢次數
private sub class_initialize()
sqldbname = ""
sqluser = ""
sqlpass = ""
sqlserver = ""
sqlaccess = "/simple_date/simple_xiehui.mdb"
rscounts = 0:rspage = 1:readcounts = 0:nowpage = 1
call openconn("access")
selectd = 0
insertd = 0
updated = 0
end sub
'********打開數據庫鏈接******************
private sub accessconn()
connstr="provider=microsoft.jet.oledb.4.0;data source=" & server.mappath(sqlaccess) &";persist security info=false"
end sub
private sub sql2kconn()
connstr = "driver={sql server};server="&sqlserver&";uid="&sqluser&";pwd="&sqlpass&";database="&sqldbname
end sub
private sub sql2k05conn()
connstr="provider=sqlncli.1;password="&sqlpass&";user id="&sqluser&";initial catalog="&sqldbname&";data source="&sqlserver
end sub
private sub openconn(db_class)
dbclass = db_class
select case db_class
case "access":call accessconn()
case "sql2000":call sql2kconn()
case "sql2005":call sql2k05conn()
end select
on error resume next
set conn = server.createobject("adodb.connection")
conn.open connstr
if err then
response.write "數據庫鏈接失敗
sqlstring = " + connstr
response.end()
err.clear
end if
end sub
'**********結束/查詢構造*************
public function select_table(sql)
if not not isempty(conn) or isnull(conn) then
call openconn(dbclass)
elseif conn.state = 0 then
call openconn(dbclass)
end if
on error resume next
set rs = server.createobject("adodb.recordset")
rs.open sql,conn,1,1
if err then
select_table = false
rs.close
exit function
err.clear
end if
if rs.eof and rs.bof then
rs.close
select_table = false
else
select_table = true
end if
readcounts = readcounts + 1
end function
'分頁處理
public function select_page(page,psize)
if isnull(page) or page = "" then page = 1
if page < 1 then page = 1
if rs.state = 1 then
if not rs.eof then
rs.pagesize = psize
rspage = rs.pagecount
rscounts = rs.recordcount
if int(page) > int(rspage) then page = rspage
rs.absolutepage = page:nowpage = page
end if
end if
end function
'更新記錄
public function update_table(sql)
if not isempty(conn) or isnull(conn) then
call openconn(dbclass)
elseif conn.state = 0 then
call openconn(dbclass)
end if
on error resume next
if sql <> "" then
conn.execute sql,updated
if err then
update_table = false
err.clear
else
update_table = true
end if
else
update_table = false
end if
conn.close
set conn = nothing
end function
'增加
'輸入：insert sql語句，表名，自增字段
public function insert_table(sql,table,id_column)
if not isempty(conn) or isnull(conn) then
call openconn(dbclass)
elseif conn.state = 0 then
call openconn(dbclass)
end if
on error resume next
if sql <> "" then
conn.execute(sql)
if err then
insert_table = false:err.clear
else
insert_table = true
end if
'獲得最后增加id
if table <> "" and id_column <> "" then
set ds = conn.execute("select "&id_column&" from "&table&" order by "&id_column&" desc")
end if
if err then
insertd = 0:err.clear
else insertd = ds(0)
end if
set ds = nothing
closed()
else
insert_table = false
end if
end function
'關閉數據庫鏈接
public function closed()
if not isempty(rs) and not isnull(rs) then
if rs.state = 1 then
rs.close
end if
end if
rscounts = 0:rspage = 1:nowpage = 1
end function
'**********釋放類************
private sub class_terminate()
readcounts = 0:rscounts = 0:rspage = 0
if not isempty(conn) and not isnull(conn) then
if conn.state = 1 then
conn.close
end if
set conn = nothing
end if
if not isempty(rs) then
if not isnull(rs) then
if rs.state=1 then
rs.close
end if
set rs=nothing
end if
end if
end sub
end class
%>
ASP技巧