Option Explicit
'--------------------------------------------------------
'[Class Name]: DBHelper
'[Description]: databse access class
'--------------------------------------------------------
'variable
Private mConn As ADODB.Connection
Private mIsOpen As Boolean
Private mIsBeginTran As Boolean
Private Const MAX_TIME_OUT As Integer = 'command timeout(unit:second)
'--------------------------------------------------------
'[Function name]: OpenConnection
'[Description]: DB connection
'[Parameter]: (1)Server name (2)Database name (3)User name (4)Password
'--------------------------------------------------------
Public Sub OpenConnection(ByVal Server As String, ByVal Database As String, _
ByVal Username As String, ByVal Password As String)
Dim connString As String
connString = OleDbConnectionString(Server, Database, Username, Password)
Set mConn = New ADODB.Connection
mConn.CommandTimeout = MAX_TIME_OUT
mConn.Open connString
mIsOpen = True
End Sub
'--------------------------------------------------------
'[Function name]: Dispose
'[Description]: Dispose
'--------------------------------------------------------
Public Sub Dispose()
If mIsOpen Then
mConn.Close
End If
mIsOpen = False
Set mConn = Nothing
End Sub
'--------------------------------------------------------
'[Function name]: ExecuteNoQuery
'[Description]: SQL execute
'[Parameter]: (1)SQL statement
'--------------------------------------------------------
Public Sub ExecuteNoQuery(ByVal strSQL As String)
If mIsOpen Then
mConn.Execute (strSQL)
End If
End Sub
'--------------------------------------------------------
'[Function name]: ExecuteRecordset
'[Description]: SQL excute
'[Parameter]: (1)SQL
'[Return Value]: ADODB.Recordset
'--------------------------------------------------------
Public Function ExecuteRecordset(ByVal strSQL As String) As ADODB.recordSet
Dim rs As New ADODB.recordSet
If mIsOpen Then
rs.CursorLocation = adUseClient
rs.Open strSQL, mConn, adOpenForwardOnly, adLockReadOnly
End If
Set ExecuteRecordset = rs
End Function
'--------------------------------------------------------
'[Function name]: BenginTrans
'[Description]: begin trans
'--------------------------------------------------------
Public Sub BeginTrans()
If mIsOpen Then
mConn.BeginTrans
mIsBeginTran = True
End If
End Sub
'--------------------------------------------------------
'[Function name]: CommitTrans
'[Description]: commint trans
'--------------------------------------------------------
Public Sub CommitTrans()
If mIsOpen And mIsBeginTran Then
mConn.CommitTrans
mIsBeginTran = False
End If
End Sub
'--------------------------------------------------------
'[Function name]: RollbankTrans
'[Description]: rollback trans
'--------------------------------------------------------
Public Sub RollbackTrans()
If mIsOpen And mIsBeginTran Then
mConn.RollbackTrans
mIsBeginTran = False
End If
End Sub
'--------------------------------------------------------
'[Function name]: OleDbConnectionString
'[Description]: create DB conn string
'[Parameter]: (1)server (2)database (3)username (4)password
'[Return Value]: DB conn string
'--------------------------------------------------------
Private Function OleDbConnectionString(ByVal Server As String, ByVal Database As String, _
ByVal Username As String, ByVal Password As String) As String
If Username = "" Then
OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
& ";Initial Catalog=" & Database _
& ";Integrated Security=SSPI;Persist Security Info=False;"
Else
OleDbConnectionString = "Provider=SQLOLEDB.1;Data Source=" & Server _
& ";Initial Catalog=" & Database _
& ";User ID=" & Username & ";Password=" & Password & ";"
End If
End Function
'--------------------------------------------------------
'[Function name]: Class_Initialize
'[Description]: class initialize
'--------------------------------------------------------
Private Sub Class_Initialize()
End Sub
'--------------------------------------------------------
'[function name]: Class_Initialize
'[description]: terminate
'--------------------------------------------------------
Private Sub Class_Terminate()
Call Dispose
End Sub