VERSION 1.0 CLASS
BEGIN
  MultiUse = -1  'True
  Persistable = 0  'NotPersistable
  DataBindingBehavior = 0  'vbNone
  DataSourceBehavior  = 0  'vbNone
  MTSTransactionMode  = 0  'NotAnMTSObject
END
Attribute VB_Name = "clsDbOpt"
Attribute VB_GlobalNameSpace = False
Attribute VB_Creatable = True
Attribute VB_PredeclaredId = False
Attribute VB_Exposed = False

Private conn As ADODB.Connection
Private strSql As String

Public Function ConnectToDb(ByVal strConnStr) As Boolean
    On Error GoTo errHandler
    If conn Is Nothing Then
        Set conn = New ADODB.Connection
        conn.Open strConnStr
    ElseIf conn.State = 0 Then
        conn.Open strConnStr
    End If
    ConnectToDb = True
    Exit Function
errHandler:
    ConnectToDb = False

End Function

'***************************************************
'通過指定表名、字段名來進行表的操作
Public Function getRecord(ByVal strTableName As String, ByVal StrColName As String, Optional ByVal strCondition = "", Optional ByVal cursorType = 1, Optional ByVal lockType = 1) As ADODB.Recordset
    Dim rsTmp As New ADODB.Recordset
    On Error GoTo errHandler
    
    strSql = "select " & StrColName & " from " & strTableName
    
    If Trim(strCondition) <> "" Then
        strSql = strSql & " where " & strCondition
    End If
    
    rsTmp.Open strSql, conn, cursorType, lockType
    
    Set getRecord = rsTmp
    Exit Function
errHandler:
    Set getRecord = Nothing
    
End Function

Public Function AddRecord(ByVal strTableName As String, ByVal StrColNames As String, ByVal strValues As String) As Boolean
    
    On Error GoTo errHandler
    strSql = "insert into " & strTableName & " ( " & StrColNames & " )" & " values (" & strValues & " )"
    conn.Execute strSql
    AddRecord = True
    Exit Function
errHandler:
    AddRecord = False

End Function

Public Function ModiRecord(ByVal strTableName As String, ByVal StrColName As String, ByVal vValue As Variant, Optional strCondition = "") As Boolean
    
    On Error GoTo errHandler
    
    strSql = "update " & strTableName & " set " & StrColName & "=" & CStr(vValue)
    If Trim(strCondition) <> "" Then
        strSql = strSql & " where " & strCondition
    End If
    conn.Execute strSql
    ModiRecord = True
    Exit Function
errHandler:
    ModiRecord = False
End Function

Public Function DelRecord(ByVal strTableName As String, Optional ByVal strCondition = "") As Boolean
    On Error GoTo errHandler
    strSql = " delete " & strTableName
    If Trim(strCondition) <> "" Then
        strSql = strSql & " where " & strCondition
    End If
    conn.Execute strSql
    DelRecord = True
    Exit Function
errHandler:
    DelRecord = False

End Function

'***************************************************
'通過指定Sql語句來進行表的操作
Public Function getRecords(ByVal strSql As String, Optional ByVal cursorType = 1, Optional ByVal lockType = 1) As ADODB.Recordset
    Dim rsTmp As New ADODB.Recordset
    On Error GoTo errHandler
    rsTmp.Open strSql, conn, cursorType, lockType
    Set getRecords = rsTmp
    Exit Function
errHandler:
    Set getRecords = Nothing
    
End Function

Public Function AddRecords(ByVal strSql As String) As Boolean
    
    On Error GoTo errHandler
    conn.Execute strSql
    AddRecords = True
    Exit Function
errHandler:
    AddRecords = False

End Function

Public Function ModiRecords(ByVal strSql As String) As Boolean
    
    On Error GoTo errHandler
    conn.Execute strSql
    ModiRecords = True
    Exit Function
errHandler:
    ModiRecords = False
End Function

Public Function DelRecords(ByVal strSql As String) As Boolean
    On Error GoTo errHandler
    conn.Execute strSql
    DelRecords = True
    Exit Function
errHandler:
    DelRecords = False
End Function
'************************************************
'輔助操作函數
Public Function IsRecordExist(ByVal strTable As String, Optional ByVal strCondition = "") As Boolean
'判斷表中是否存在符合條件的記錄
    Dim rsTmp As ADODB.Recordset
    On Error GoTo errHandler
    strSql = "select top 1 * from " & strTable
    If Trim(strCondition) <> "" Then
        strSql = strSql & " where " & strCondition
    End If
    Set rsTmp = getRecords(strSql)
    
    IsRecordExist = True
    If rsTmp.EOF And rsTmp.BOF Then
        IsRecordExist = False
    End If
    Exit Function
errHandler:
    IsRecordExist = False
    
End Function