Attribute VB_Name = "mdlPublic"
Option Explicit

Public g_Conn As Connection '用于全局的數據連接
Public g_DBPath As String   '如果是Access數據庫，記錄下數據庫的路徑

'ActiveX DLL的啟動程序，為DLL初始化時執行
Public Sub Main()
  g_DBPath = App.Path & "\Database\ManageClient.mdb"
  'MsgBox g_DBPath
  If ConnectToDatabase(DBAccess) = False Then
    Err.Raise vbObjectError + 1, , "連接數據庫出錯！|" + App.Path + "|"
  End If
End Sub

'連接到數據庫
Public Function ConnectToDatabase(DBType As gxcDBType) As Boolean
  On Error GoTo ERR_CONN
  Set g_Conn = New Connection
  
  '設置服務器名稱，數據庫名稱，登錄名（此時假設密碼為空）
  Dim ServerName As String, DBName As String, UserName As String, strPwd As String
  '這些是為連接Sql Server而用
  ServerName = "localhost"
  DBName = "ManageClient"
  UserName = "sa"
  strPwd = ""
  
  '連接到數據庫
  With g_Conn
     .CursorLocation = adUseClient
     .CommandTimeout = 10
     
     If DBType = DBAccess Then
       ' 連接到ACCESS數據庫
        .ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Password='';" & _
        "Data Source=" & g_DBPath
     Else
      ' 連接到SQL Server數據庫
        .ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
        "User ID=" & UserName & ";Initial Catalog=" & DBName & _
        ";Data Source=" & ServerName & ";pwd=" & strPwd
     End If
     .Open
  End With
  ConnectToDatabase = True
  Exit Function
  
ERR_CONN:
  ConnectToDatabase = False
  MsgBox Err.Description
  
End Function

'替換字符串中的單引號
Public Function RealString(strOrigional) As String
  RealString = Replace(strOrigional, "'", "''")
End Function

'得到某個數據表中主鍵的下一個值，即當前主鍵值加1
Public Function NextID(ByVal strTable As String, ByVal strID As String) As Long
  '兩個參數分別是表的名稱與主鍵的名稱
  Dim rs As Recordset
  Set rs = g_Conn.Execute("SELECT MAX(" & strID & ") FROM " & strTable)
  
  If IsNull(rs(0)) Then
    '如果值為NULL，則說明無任何數據記錄，此時ID應為1
    NextID = 1
  Else
    '使新ID為最大ID值+1
    NextID = rs(0).Value + 1
  End If
End Function

'得到某個數據表中主鍵的最大值
Public Function MaxID(ByVal strTable As String, ByVal strID As String) As Long
  '兩個參數分別是表的名稱與主鍵的名稱
  Dim rs As Recordset
  Set rs = g_Conn.Execute("SELECT MAX(" & strID & ") FROM " & strTable)
  
  If IsNull(rs(0)) Then
    '如果值為NULL，則說明無任何數據記錄，此時ID應為1
    MaxID = 0
  Else
    '使新ID為最大ID值+1
    MaxID = rs(0).Value
  End If
End Function


'查看某個數據表中，是否存在某個字段等于某個值的記錄（整型）
Public Function ExistByID(ByVal strTable As String, ByVal strID As String, _
                          ByVal lngID As Long) As Boolean
  '第一個參數為表名，第二個為字段名，第三個為具體的字段值
  Dim rs As Recordset
  Set rs = g_Conn.Execute("Select Count(*) from " & strTable & _
                          " where " & strID & "=" & lngID)
  ExistByID = (rs(0).Value = 1)
  
End Function

'查看某個數據表中，是否存在某個字段等于某個值的記錄（字符型）
Public Function ExistByName(ByVal strTable As String, ByVal strFieldName As String, ByVal strName As String) As Boolean
  '第一個參數為表名，第二個為字段名，第三個為具體的字段值
  Dim rs As Recordset
  Set rs = g_Conn.Execute("Select Count(*) from " & strTable & " where " & strFieldName & "='" & strName & "'")
  ExistByName = (rs(0).Value = 1)
End Function
'以上兩個函數實際上可以合并，本程序中為了說明問題，故而分開

'根據給定的主鍵值，獲取某一指定的字段值
Public Function GetValueByID(ByVal strTable As String, ByVal strID As String, _
                  ByVal lngID As Long, ByVal strValueField As String) As String
  '第一個參數為表名，第二個為主鍵字段名，第三個為主鍵字段值，第四個為要獲取值的字段名
  Dim rs As Recordset
  Set rs = g_Conn.Execute("Select " & strValueField & " from " & strTable & _
                          " where " & strID & "=" & lngID)
  If rs.RecordCount = 1 Then
    GetValueByID = rs(0).Value
  Else
    GetValueByID = ""
  End If
  Set rs = Nothing
  
End Function


'//
'// 執行一條無返回結果的 SQL 語句
'//
Public Function RunSql(strSQL As String, ByRef strErrMsg As String) As Boolean
  
  On Error Resume Next
  
  g_Conn.Execute strSQL '執行SQL語句
  
  '根據是否出錯，返回給調用者相應的信息
  If Err.Number = 0 Then
    RunSql = True
  Else
    strErrMsg = Err.Description
    RunSql = False
  End If

End Function

'//
'// 執行一條有返回結果的 SQL 語句
'//
Public Function GetRecordset(strSQL As String, ByRef strErrMsg As String, ByRef rs As Recordset) As Boolean
  
  On Error Resume Next
  
  Set rs = g_Conn.Execute(strSQL) '執行SQL語句
  
  '根據是否出錯，返回給調用者相應的信息
  If Err.Number = 0 Then
    GetRecordset = True
  Else
    strErrMsg = Err.Description
    GetRecordset = False
  End If

End Function