VBMAN.Db - Database Operation Object

Overview

VBMAN.Db provides database operation functionality, encapsulating ADO database access, supporting parameterized queries, transaction processing, result set operations, etc.

Core Features

• Parameterized Queries: Prevents SQL injection attacks
• Chain Calling: Fluent SQL building API
• Automatic Connection Management: Connection pooling and automatic open/close
• Transaction Support: Complete transaction processing functionality
• Result Set Operations: Convenient data access methods

Main Methods

Connection Management

Connect

Connect to database

Public Function Connect(ByVal DbType As enumDbType, Optional ByVal DbAddress As String = "127.0.0.1,1433", Optional ByVal username As String = "sa", Optional ByVal Password As String = "Sa123456", Optional ByVal DefaultDataBase As String = "master") As Boolean

Parameters:

• DbType - Database type (Access=1, Mysql=2, MsSql=3, Csv=4, Custom=5)
• DbAddress - Server address and port
• username - Username
• Password - Password
• DefaultDataBase - Default database

Example:

' Connect to SQL Server
If VBMAN.Db.Connect(MsSql, "192.168.1.100,1433", "sa", "password", "mydb") Then
    MsgBox "Connection successful"
Else
    MsgBox "Connection failed: " & VBMAN.Db.LastErr
End If

' Connect to Access
VBMAN.Db.Connect Access, "C:\\data.mdb"

DisConnect

Disconnect

Public Function DisConnect() As Boolean

SQL Execution

Sql

Set SQL statement (starting point for chain calling)

Public Function Sql(sqlText As String) As cDataBase

Example:

VBMAN.Db.Sql("SELECT * FROM users WHERE id=1")

Param

Add parameter

Public Function Param(paramName As String, paramValue As Variant) As cDataBase

Example:

VBMAN.Db.Sql("SELECT * FROM users WHERE id=? AND status=?").Param("id", 1).Param("status", "active")

Fetch / FetchParam

Execute query

Public Function Fetch() As cDataBase
Public Function FetchParam() As cDataBase

Example:

' Simple query
VBMAN.Db.Sql("SELECT * FROM users WHERE id=1").Fetch
Debug.Print VBMAN.Db.Row("username")

' Parameterized query
VBMAN.Db.Sql("SELECT * FROM users WHERE id=? AND age>?").Param("id", 1).Param("age", 18).FetchParam
Debug.Print VBMAN.Db.Row("username")

Exec / ExecParam

Execute non-query statements

Public Function Exec() As cDataBase
Public Function ExecParam() As cDataBase

Example:

' Insert data
VBMAN.Db.Sql("INSERT INTO users (username, email) VALUES ('admin', 'admin@test.com')").Exec

' Parameterized insert
VBMAN.Db.Sql("INSERT INTO users (username, email) VALUES (?, ?)").Param("username", "admin").Param("email", "admin@test.com").ExecParam

' Update data
VBMAN.Db.Sql("UPDATE users SET last_login=? WHERE id=?").Param("last_login", Now).Param("id", 1).ExecParam

' Delete data
VBMAN.Db.Sql("DELETE FROM users WHERE id=?").Param("id", 1).ExecParam

Transaction Processing

' Begin transaction
VBMAN.Db.TransBegin

On Error GoTo Rollback

' Execute multiple operations
VBMAN.Db.Sql("INSERT INTO orders (user_id, total) VALUES (?, ?)").Param("user_id", 1).Param("total", 100).ExecParam

' Commit transaction
VBMAN.Db.TransCommit
Exit Sub

Rollback:
    VBMAN.Db.TransRollback
    MsgBox "Operation failed: " & Err.Description

Result Set Access

Row

Access current row data (Scripting.Dictionary)

' Access field
Dim username As String
username = VBMAN.Db.Row("username")

' Check if field exists
If VBMAN.Db.Row.Exists("email") Then
    Debug.Print VBMAN.Db.Row("email")
End If

Rs

Raw ADODB.Recordset object

Do While Not VBMAN.Db.Rs.EOF
    Debug.Print VBMAN.Db.Rs("username")
    VBMAN.Db.Rs.MoveNext
Loop

Paged Query

VBMAN.Db.Sql("SELECT * FROM users").Page(2, 10).Fetch

Comprehensive Examples

Example 1: User CRUD Operations

' Create user
Private Sub CreateUser(username As String, email As String)
    VBMAN.Db.Sql("INSERT INTO users (username, email, created_at) VALUES (?, ?, ?)"). _
        Param("username", username). _
        Param("email", email). _
        Param("created_at", Now). _
        ExecParam
    
    MsgBox "User created successfully!"
End Sub

' Read user
Private Sub LoadUser(userId As Long)
    VBMAN.Db.Sql("SELECT * FROM users WHERE id=?").Param("id", userId).FetchParam
    
    If VBMAN.Db.Row.Count > 0 Then
        TextUsername.Text = VBMAN.Db.Row("username")
        TextEmail.Text = VBMAN.Db.Row("email")
    Else
        MsgBox "User does not exist!"
    End If
End Sub

' Update user
Private Sub UpdateUser(userId As Long, email As String)
    VBMAN.Db.Sql("UPDATE users SET email=?, updated_at=? WHERE id=?"). _
        Param("email", email). _
        Param("updated_at", Now). _
        Param("id", userId). _
        ExecParam
    
    MsgBox "Update successful"
End Sub

' Delete user
Private Sub DeleteUser(userId As Long)
    VBMAN.Db.Sql("DELETE FROM users WHERE id=?").Param("id", userId).ExecParam
    MsgBox "Delete successful"
End Sub

Example 2: Paged Query

Private Sub LoadUserList(page As Integer, pageSize As Integer)
    ' Query data
    VBMAN.Db.Sql("SELECT * FROM users ORDER BY id DESC"). _
        Page(page, pageSize). _
        Fetch
    
    ' Bind to list
    ListView1.ListItems.Clear
    
    Do While Not VBMAN.Db.Rs.EOF
        With ListView1.ListItems.Add(, , VBMAN.Db.Rs("id"))
            .SubItems(1) = VBMAN.Db.Rs("username")
            .SubItems(2) = VBMAN.Db.Rs("email")
        End With
        VBMAN.Db.Rs.MoveNext
    Loop
End Sub

Best Practices

1. Use Parameterized Queries: Always use Param method to prevent SQL injection
2. Disconnect in Time: Call DisConnect after operations complete
3. Error Handling: Add On Error handling for database exceptions
4. Transaction Usage: Use transactions for multi-table operations to ensure data consistency
5. Connection String Security: Don't hardcode passwords in code