Three-tier Architecture database access Layer (VB)

Access | schema | data | database ' ####### #数据库访问层 ##########


' Returns a ADOCN connection object

' Unlike C #, you can't turn off connections and free memory here.
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 ' Client cursor

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 "Database connection error", vbcritical

End Function

' Execute a SQL statement, correctly return 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
' Execute a set of SQL statements that correctly returns 1
Public Function Excutesqlex (sSQL () as String) as Integer
' Invoke transaction processing
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


' Not finished, to be added