三層架構之數據庫訪問層(VB)
2024-07-21 02:20:43
供稿：網友
'########數據庫訪問層##########
'返回一個adocn連接對象
'與c#不同，這里不能關閉連接及釋放內存
public function getcn(sdbpath as string) as adodb.connection
const spro as string = "provider=microsoft.jet.oledb.4.0;data source="
const sdbpwd as string = ";jet oledb:database password=qq:48403849"
dim sdbpath as string
dim m_cn as adodb.connection
set m_cn = new adodb.connection
m_cn.cursorlocation = aduseclient '客戶端游標
if m_cn.state <> adstateclosed then m_cn.close
on error goto conerr
m_cn.open spro & sdbpath & sdbpwd
set getcn = m_cn
exit function
conerr:
set getcn = nothing
msgbox "數據庫連接錯誤", vbcritical
end function
'執行一句sql語句，正確返回 1
public function excutesql(ssql as string) as integer
dim m_cn as new adodb.connection
on error goto err
set m_cn = getcn(sg_dbpath)
m_cn.execute ssql
m_cn.close
set m_cn = nothing
excutesql = 1
exit function
err:
excutesql = 0
set m_cn = nothing
end function
'執行一組sql語句，正確返回1
public function excutesqlex(ssql() as string) as integer
'調用事務處理
dim m_cn as new adodb.connection, i as integer
if ubound(ssql) < 0 then exit function
on error goto err
set m_cn = getcn(sg_dbpath)
m_cn.begintrans
for i = 0 to ubound(ssql) - 1
m_cn.execute ssql(i)
next i
m_cn.committrans
m_cn.close
set m_cn = nothing
excutesqlex = 1
exit function
err:
excutesqlex = 0
m_cn.rollbacktrans
m_cn = nothing
end function
'未完，待補充
