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