VBMAN.Db - 数据库操作对象

概述

VBMAN.Db 提供了数据库操作功能，封装了 ADO 数据库访问，支持参数化查询、事务处理、结果集操作等。

核心特性

• 参数化查询: 防止 SQL 注入攻击
• 链式调用: 流畅的 SQL 构建 API
• 自动连接管理: 连接池和自动开关连接
• 事务支持: 完整的事务处理功能
• 结果集操作: 便捷的数据访问方式

主要方法

连接管理

Connect

连接数据库

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

参数:

• DbType - 数据库类型 (Access=1, Mysql=2, MsSql=3, Csv=4, Custom=5)
• DbAddress - 服务器地址和端口
• username - 用户名
• Password - 密码
• DefaultDataBase - 默认数据库

示例:

' 连接 SQL Server
If VBMAN.Db.Connect(MsSql, "192.168.1.100,1433", "sa", "password", "mydb") Then
    MsgBox "连接成功"
Else
    MsgBox "连接失败: " & VBMAN.Db.LastErr
End If

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

DisConnect

断开连接

Public Function DisConnect() As Boolean

SQL 执行

Sql

设置 SQL 语句（链式调用起点）

Public Function Sql(sqlText As String) As cDataBase

示例:

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

Param

添加参数

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

示例:

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

Fetch / FetchParam

执行查询

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

示例:

' 简单查询
VBMAN.Db.Sql("SELECT * FROM users WHERE id=1").Fetch
Debug.Print VBMAN.Db.Row("username")

' 参数化查询
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

执行非查询语句

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

示例:

' 插入数据
VBMAN.Db.Sql("INSERT INTO users (username, email) VALUES ('admin', 'admin@test.com')").Exec

' 参数化插入
VBMAN.Db.Sql("INSERT INTO users (username, email) VALUES (?, ?)").Param("username", "admin").Param("email", "admin@test.com").ExecParam

' 更新数据
VBMAN.Db.Sql("UPDATE users SET last_login=? WHERE id=?").Param("last_login", Now).Param("id", 1).ExecParam

' 删除数据
VBMAN.Db.Sql("DELETE FROM users WHERE id=?").Param("id", 1).ExecParam

事务处理

' 开始事务
VBMAN.Db.TransBegin

On Error GoTo Rollback

' 执行多个操作
VBMAN.Db.Sql("INSERT INTO orders (user_id, total) VALUES (?, ?)").Param("user_id", 1).Param("total", 100).ExecParam

' 提交事务
VBMAN.Db.TransCommit
Exit Sub

Rollback:
    VBMAN.Db.TransRollback
    MsgBox "操作失败: " & Err.Description

结果集访问

Row

访问当前行数据（Scripting.Dictionary）

' 访问字段
Dim username As String
username = VBMAN.Db.Row("username")

' 检查字段是否存在
If VBMAN.Db.Row.Exists("email") Then
    Debug.Print VBMAN.Db.Row("email")
End If

Rs

原始 ADODB.Recordset 对象

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

分页查询

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

综合示例

示例1: 用户 CRUD 操作

' 创建用户
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 "用户创建成功！"
End Sub

' 读取用户
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 "用户不存在！"
    End If
End Sub

' 更新用户
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 "更新成功"
End Sub

' 删除用户
Private Sub DeleteUser(userId As Long)
    VBMAN.Db.Sql("DELETE FROM users WHERE id=?").Param("id", userId).ExecParam
    MsgBox "删除成功"
End Sub

示例2: 分页查询

Private Sub LoadUserList(page As Integer, pageSize As Integer)
    ' 查询数据
    VBMAN.Db.Sql("SELECT * FROM users ORDER BY id DESC"). _
        Page(page, pageSize). _
        Fetch
    
    ' 绑定到列表
    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

最佳实践

1. 使用参数化查询: 始终使用 Param 方法，防止 SQL 注入
2. 及时断开连接: 操作完成后调用 DisConnect
3. 错误处理: 添加 On Error 处理数据库异常
4. 事务使用: 多表操作使用事务保证数据一致性
5. 连接字符串安全: 不要在代码中硬编码密码