VB6 PRO DOCS

English

中文

English

中文

Appearance

Advanced Features ​

This document introduces the advanced features and best practices of the cDataBase class library, helping you make better use of the database class.

Table of Contents ​

Asynchronous Execution ​

Async Property ​

The Async property enables asynchronous SQL execution without blocking the main thread.

Syntax ​

vb
Property Get Async() As cDataBase

Example ​

vb
' Asynchronous INSERT execution
db.Sql("INSERT INTO logs (message) VALUES ('test')").Async.Exec

' Listen for completion event
Private Sub db_AsyncExecuteComplete(ByVal RecordsAffected As Long, _
                                    ByVal pError As ADODB.Error, _
                                    adStatus As ADODB.EventStatusEnum, _
                                    ByVal pCommand As ADODB.Command, _
                                    ByVal pRecordset As ADODB.Recordset, _
                                    ByVal pConnection As ADODB.Connection)
    If pError Is Nothing Then
        Debug.Print "Async execution successful, rows affected: " & RecordsAffected
    Else
        Debug.Print "Async execution failed: " & pError.Description
    End If
End Sub

Asynchronous Execution Scenarios ​

vb
' Scenario 1: Logging (non-blocking main flow)
Sub LogMessage(sMessage As String)
    db.Sql("INSERT INTO logs (message, created_at) VALUES (?, ?)") _
        .Param("message", sMessage, VBMAN.adVarWChar) _
        .Param("created_at", Now, VBMAN.adDate) _
        .Async.ExecParam
    ' Don't wait for execution to complete, continue with subsequent code
End Sub

' Scenario 2: Batch data processing
Sub ProcessLargeDataset()
    ' Main thread continues processing
    ProcessData

    ' Save results asynchronously
    db.Sql("INSERT INTO results SELECT * FROM temp_table").Async.Exec
End Sub

Connection Pool Management ​

Multiple Database Connections ​

Use connection pool to manage multiple database connections.

vb
Dim db As New VBMAN.cDataBase

' Main database
db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "maindb"

' Log database
Dim dbLog As VBMAN.cDataBase
Set dbLog = db.ConnInst("log", False)
dbLog.Connect VBMAN.enumDbType_Mysql, "192.168.1.100:3306", "loguser", "pwd", "logdb"

' Cache database
Dim dbCache As VBMAN.cDataBase
Set dbCache = db.ConnInst("cache", False)
dbCache.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "cachedb"

' Use different databases
db.Sql("SELECT * FROM users").Query
dbLog.Sql("INSERT INTO logs (msg) VALUES ('test')").Exec
dbCache.Sql("SELECT * FROM cache_data").Query

Dynamic Connection Management ​

vb
' Create connections dynamically based on configuration
Function GetDatabase(sConfigName As String) As VBMAN.cDataBase
    Dim dbInst As VBMAN.cDataBase
    Set dbInst = db.ConnInst(sConfigName, False)

    ' Connect to different databases based on configuration
    Select Case sConfigName
    Case "main"
        dbInst.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "maindb"
    Case "backup"
        dbInst.Connect VBMAN.enumDbType_Mysql, "192.168.1.100:3306", "user", "pwd", "backupdb"
    Case "readonly"
        dbInst.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "readonly", "pwd", "maindb"
    End Select

    Set GetDatabase = dbInst
End Function

' Usage
Dim dbMain As VBMAN.cDataBase
Set dbMain = GetDatabase("main")
dbMain.Sql("SELECT * FROM users").Query

Connection Cleanup ​

vb
' Remove specified connection
db.ConnInstRemove "log"

' Remove all connections
db.ConnInstRemove ""

Batch Operation Optimization ​

Batch Insert Optimization ​

vb
' Method 1: Using BatchInsert (recommended)
Sub BatchInsertUsers(colUsers As Collection)
    If db.BatchInsert("users", colUsers) Then
        Debug.Print "Batch insert successful"
    End If
End Sub

' Method 2: Using transaction + loop
Sub BatchInsertUsers2(colUsers As Collection)
    db.TransBegin

    Dim i As Long
    For i = 1 To colUsers.Count
        Dim dictUser As Dictionary
        Set dictUser = colUsers(i)

        Dim sSql As String
        sSql = "INSERT INTO users (name, age) VALUES ('" & _
               db.Escape(dictUser("name")) & "', " & dictUser("age") & ")"

        If Not db.Sql(sSql).Exec Then
            db.TransRollback
            Exit Sub
        End If
    Next

    db.TransCommit
End Sub

' Method 3: Using VALUES clause (SQL Server/MySQL)
Sub BatchInsertUsers3(colUsers As Collection)
    Dim sSql As String
    sSql = "INSERT INTO users (name, age) VALUES "

    Dim i As Long
    For i = 1 To colUsers.Count
        Dim dictUser As Dictionary
        Set dictUser = colUsers(i)

        If i > 1 Then sSql = sSql & ", "
        sSql = sSql & "('" & db.Escape(dictUser("name")) & "', " & dictUser("age") & ")"
    Next

    db.Sql(sSql).Exec
End Sub

Batch Update Optimization ​

vb
' Use transaction for batch update
Sub BatchUpdateUsers(colUpdates As Collection)
    db.TransBegin

    Dim i As Long
    For i = 1 To colUpdates.Count
        Dim dictUpdate As Dictionary
        Set dictUpdate = colUpdates(i)

        If Not db.Sql("UPDATE users SET name = ?, age = ? WHERE id = ?") _
            .Param("name", dictUpdate("name"), VBMAN.adVarWChar) _
            .Param("age", dictUpdate("age"), VBMAN.adInteger) _
            .Param("id", dictUpdate("id"), VBMAN.adInteger) _
            .ExecParam Then

            db.TransRollback
            Exit Sub
        End If
    Next

    db.TransCommit
End Sub

Performance Optimization Tips ​

1. Use Indexed Fields ​

vb
' Recommended: Use indexed fields as conditions
db.Sql("SELECT * FROM users WHERE id = 1").Query

' Not recommended: Use non-indexed fields
db.Sql("SELECT * FROM users WHERE name = 'John'").Query

2. Only Query Needed Fields ​

vb
' Recommended: Only query needed fields
db.Sql("SELECT id, name FROM users").Query

' Not recommended: Query all fields
db.Sql("SELECT * FROM users").Query

3. Use Appropriate Cursor Types ​

vb
' Recommended: Use ForwardOnly for read-only queries
db.Sql("SELECT * FROM users").Query adOpenForwardOnly, adLockReadOnly

' Not recommended: Use default cursor (may be slower)
db.Sql("SELECT * FROM users").Query

4. Limit Result Set Size ​

vb
' Recommended: Use TOP/LIMIT to limit results
db.Sql("SELECT TOP 100 * FROM users").Query

' Not recommended: Query all data
db.Sql("SELECT * FROM users").Query

5. Use Pagination ​

vb
' Recommended: Use pagination
db.Sql("SELECT * FROM users").Page(1, 20).Query

' Not recommended: Query all data at once
db.Sql("SELECT * FROM users").Query

6. Cache Query Results ​

vb
' Cache query results
Private m_colCachedUsers As Collection
Private m_dtCacheTime As Date

Function GetUsers() As Collection
    ' Cache for 5 minutes
    If DateDiff("s", m_dtCacheTime, Now) > 300 Or m_colCachedUsers Is Nothing Then
        If db.Sql("SELECT * FROM users").Fetch Then
            Set m_colCachedUsers = db.Rows
            m_dtCacheTime = Now
        End If
    End If

    Set GetUsers = m_colCachedUsers
End Function

Error Handling Strategy ​

Unified Error Handling ​

vb
' Unified error handling function
Function ExecuteSQL(sSql As String) As Boolean
    On Error GoTo ErrHandler

    If db.Sql(sSql).Exec Then
        ExecuteSQL = True
    Else
        LogError "SQL execution failed", db.LastErr
        ExecuteSQL = False
    End If

    Exit Function

ErrHandler:
    LogError "Exception occurred", Err.Description
    ExecuteSQL = False
End Function

' Error logging
Sub LogError(sOperation As String, sError As String)
    ' Log to file or database
    Debug.Print Now & " - " & sOperation & ": " & sError
End Sub

Retry Mechanism ​

vb
' Query with retry
Function QueryWithRetry(sSql As String, Optional lMaxRetries As Long = 3) As Boolean
    Dim lRetry As Long
    For lRetry = 1 To lMaxRetries
        If db.Sql(sSql).Query Then
            QueryWithRetry = True
            Exit Function
        End If

        ' Check connection
        If Not db.CheckConnection Then
            db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"
        End If

        ' Wait and retry
        Sleep 1000
    Next

    QueryWithRetry = False
End Function

Design Pattern Applications ​

Singleton Pattern ​

vb
' Database singleton
Private m_DB As VBMAN.cDataBase

Function GetDatabase() As VBMAN.cDataBase
    If m_DB Is Nothing Then
        Set m_DB = New VBMAN.cDataBase
        m_DB.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"
    End If

    Set GetDatabase = m_DB
End Function

Factory Pattern ​

vb
' Database factory
Function CreateDatabase(sType As String) As VBMAN.cDataBase
    Dim db As New VBMAN.cDataBase

    Select Case sType
    Case "main"
        db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "maindb"
    Case "log"
        db.Connect VBMAN.enumDbType_Mysql, "192.168.1.100:3306", "loguser", "pwd", "logdb"
    Case "cache"
        db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "cachedb"
    End Select

    Set CreateDatabase = db
End Function

Repository Pattern ​

vb
' User repository
Class cUserRepository
    Private m_DB As VBMAN.cDataBase

    Private Sub Class_Initialize()
        Set m_DB = New VBMAN.cDataBase
        m_DB.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"
    End Sub

    Function GetById(lId As Long) As Dictionary
        If m_DB.Sql("SELECT * FROM users WHERE id = ?") _
            .Param("id", lId, VBMAN.adInteger) _
            .QueryParam Then

            If m_DB.Rows.Count > 0 Then
                Set GetById = m_DB.Row
            End If
        End If
    End Function

    Function GetAll() As Collection
        If m_DB.Sql("SELECT * FROM users").Fetch Then
            Set GetAll = m_DB.Rows
        End If
    End Function

    Function Save(dictUser As Dictionary) As Boolean
        If m_DB.Sql("INSERT INTO users (name, age) VALUES (?, ?)") _
            .Param("name", dictUser("name"), VBMAN.adVarWChar) _
            .Param("age", dictUser("age"), VBMAN.adInteger) _
            .ExecParam Then

            Save = True
        Else
            Save = False
        End If
    End Function
End Class

Common Problem Solutions ​

Q1: Connection Timeout ​

Problem: Connection to database times out.

Solution:

vb
' Set connection timeout
db.Conn.ConnectionTimeout = 30  ' 30 seconds
db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"

Q2: Query Timeout ​

Problem: Query execution takes too long.

Solution:

vb
' Set command timeout
db.Conn.CommandTimeout = 60  ' 60 seconds
db.Sql("SELECT * FROM large_table").Query

Q3: Out of Memory ​

Problem: Querying large amounts of data causes memory exhaustion.

Solution:

vb
' Use pagination query
db.Sql("SELECT * FROM large_table").Page(1, 1000).Query

' Or use streaming
If db.Sql("SELECT * FROM large_table").Query Then
    Do Until db.Rs.EOF
        ' Process single record
        ProcessRecord db.Rs
        db.Rs.MoveNext
    Loop
End If

Q4: Concurrency Conflicts ​

Problem: Multiple operations execute simultaneously causing conflicts.

Solution:

vb
' Use transaction and locking
db.TransBegin
db.Sql("SELECT * FROM users WHERE id = 1").Query adOpenKeyset, adLockPessimistic
' Process data
db.Sql("UPDATE users SET ...").Exec
db.TransCommit

Best Practices Summary ​

1. Connection Management ​

  • Connect when needed, disconnect when done
  • For frequent operations, keep the connection alive
  • Use connection pool to manage multiple connections

2. Query Optimization ​

  • Use indexed fields as conditions
  • Only query needed fields
  • Use pagination to limit result sets
  • Use appropriate cursor types

3. Security Protection ​

  • Always use parameterized queries
  • Validate user input
  • Use transactions to ensure consistency

4. Error Handling ​

  • Always check return values
  • Log errors
  • Implement retry mechanism

5. Code Organization ​

  • Use design patterns
  • Encapsulate common operations
  • Keep code clean

Last Updated: 2026-01-21

VB6 and LOGO copyright of Microsoft Corporation

VBMAN copyright © 2019-2026 woeoio