Skip to content

�߼�����

���ĵ����� cDataBase ��ĸ߼����ܺ����ʵ�������������õ�ʹ�����ݿ���⡣


? Ŀ¼


�첽ִ��

Async ����

ʹ�� Async ���Կ����첽ִ�� SQL ��䣬���������̡߳�

�﷨

vb
Property Get Async() As cDataBase

ʾ��

vb
' �첽ִ�� INSERT
db.Sql("INSERT INTO logs (message) VALUES ('test')").Async.Exec

' ��������¼�
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 "�첽ִ�гɹ���Ӱ������: " & RecordsAffected
    Else
        Debug.Print "�첽ִ��ʧ��: " & pError.Description
    End If
End Sub

�첽ִ�г���

vb
' ���� 1����־��¼�������������̣�
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
    ' ���ȴ�ִ����ɣ�����ִ�к�������
End Sub

' ���� 2���������ݴ���
Sub ProcessLargeDataset()
    ' ���̼߳�������
    ProcessData
    
    ' �첽������
    db.Sql("INSERT INTO results SELECT * FROM temp_table").Async.Exec
End Sub

���ӳع���

�����ݿ�����

ʹ�����ӳع���������ݿ����ӡ�

vb
Dim db As New VBMAN.cDataBase

' �����ݿ�
db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "maindb"

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

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

' ʹ�ò�ͬ�����ݿ�
db.Sql("SELECT * FROM users").Query
dbLog.Sql("INSERT INTO logs (msg) VALUES ('test')").Exec
dbCache.Sql("SELECT * FROM cache_data").Query

��̬���ӹ���

vb
' �������ö�̬��������
Function GetDatabase(sConfigName As String) As VBMAN.cDataBase
    Dim dbInst As VBMAN.cDataBase
    Set dbInst = db.ConnInst(sConfigName, False)
    
    ' �����������Ӳ�ͬ�����ݿ�
    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

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

��������

vb
' �Ƴ�ָ������
db.ConnInstRemove "log"

' �Ƴ���������
db.ConnInstRemove ""

����������

����������

vb
' ���� 1��ʹ�� BatchInsert���Ƽ���
Sub BatchInsertUsers(colUsers As Collection)
    If db.BatchInsert("users", colUsers) Then
        Debug.Print "��������ɹ�"
    End If
End Sub

' ���� 2��ʹ������ + ѭ��
Sub BatchInsertUsers2(colUsers As Collection)
    db.TransBegin
    
    Dim i As Long
    For i = 1 To colUsers.Count
        Dim dictUser As Scripting.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

' ���� 3��ʹ�� VALUES �Ӿ䣨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 Scripting.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

����������

vb
' ʹ��������������
Sub BatchUpdateUsers(colUpdates As Collection)
    db.TransBegin
    
    Dim i As Long
    For i = 1 To colUpdates.Count
        Dim dictUpdate As Scripting.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

����������

1. ʹ�������ֶ�

vb
' ? �Ƽ���ʹ�������ֶ���Ϊ����
db.Sql("SELECT * FROM users WHERE id = 1").Query

' ? ���Ƽ���ʹ�÷������ֶ�
db.Sql("SELECT * FROM users WHERE name = '����'").Query

2. ֻ��ѯ��Ҫ���ֶ�

vb
' ? �Ƽ���ֻ��ѯ��Ҫ���ֶ�
db.Sql("SELECT id, name FROM users").Query

' ? ���Ƽ�����ѯ�����ֶ�
db.Sql("SELECT * FROM users").Query

3. ʹ�ú��ʵ��α�����

vb
' ? �Ƽ���ֻ����ѯʹ�� ForwardOnly
db.Sql("SELECT * FROM users").Query adOpenForwardOnly, adLockReadOnly

' ? ���Ƽ���ʹ��Ĭ���α꣨���ܽ�����
db.Sql("SELECT * FROM users").Query

4. ���ƽ������С

vb
' ? �Ƽ���ʹ�� TOP/LIMIT ���ƽ��
db.Sql("SELECT TOP 100 * FROM users").Query

' ? ���Ƽ�����ѯ��������
db.Sql("SELECT * FROM users").Query

5. ʹ�÷�ҳ

vb
' ? �Ƽ���ʹ�÷�ҳ
db.Sql("SELECT * FROM users").Page(1, 20).Query

' ? ���Ƽ���һ���Բ�ѯ��������
db.Sql("SELECT * FROM users").Query

6. �����ѯ���

vb
' �����ѯ���
Private m_colCachedUsers As Collection
Private m_dtCacheTime As Date

Function GetUsers() As Collection
    ' ���� 5 ����
    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

����������

ͳһ������

vb
' ͳһ����������
Function ExecuteSQL(sSql As String) As Boolean
    On Error GoTo ErrHandler
    
    If db.Sql(sSql).Exec Then
        ExecuteSQL = True
    Else
        LogError "SQLִ��ʧ��", db.LastErr
        ExecuteSQL = False
    End If
    
    Exit Function
    
ErrHandler:
    LogError "�����쳣", Err.Description
    ExecuteSQL = False
End Function

' ������־��¼
Sub LogError(sOperation As String, sError As String)
    ' ��¼���ļ������ݿ�
    Debug.Print Now & " - " & sOperation & ": " & sError
End Sub

���Ի���

vb
' �����ԵIJ�ѯ
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
        
        ' �������
        If Not db.CheckConnection Then
            db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"
        End If
        
        ' �ȴ�������
        Sleep 1000
    Next
    
    QueryWithRetry = False
End Function

���ģʽӦ��

����ģʽ

vb
' ���ݿⵥ��
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

����ģʽ

vb
' ���ݿ⹤��
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

�ִ�ģʽ

vb
' �û��ִ�
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 Scripting.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 Scripting.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

����������

Q1: ���ӳ�ʱ

����: �������ݿ�ʱ��ʱ��

���:

vb
' �������ӳ�ʱ
db.Conn.ConnectionTimeout = 30  ' 30 ��
db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"

Q2: ��ѯ��ʱ

����: ��ѯִ��ʱ�������

���:

vb
' �������ʱ
db.Conn.CommandTimeout = 60  ' 60 ��
db.Sql("SELECT * FROM large_table").Query

Q3: �ڴ治��

����: ��ѯ�������ݵ����ڴ治�㡣

���:

vb
' ʹ�÷�ҳ��ѯ
db.Sql("SELECT * FROM large_table").Page(1, 1000).Query

' ��ʹ����ʽ����
If db.Sql("SELECT * FROM large_table").Query Then
    Do Until db.Rs.EOF
        ' ����������¼
        ProcessRecord db.Rs
        db.Rs.MoveNext
    Loop
End If

Q4: ������ͻ

����: �������ͬʱִ�е��³�ͻ��

���:

vb
' ʹ�����������
db.TransBegin
db.Sql("SELECT * FROM users WHERE id = 1").Query adOpenKeyset, adLockPessimistic
' ��������
db.Sql("UPDATE users SET ...").Exec
db.TransCommit

���ʵ���ܽ�

1. ���ӹ���

  • ? ����Ҫʱ���ӣ�ʹ����Ϻ�Ͽ�
  • ? ����Ƶ����������������
  • ? ʹ�����ӳع����������

2. ��ѯ�Ż�

  • ? ʹ�������ֶ���Ϊ����
  • ? ֻ��ѯ��Ҫ���ֶ�
  • ? ʹ�÷�ҳ���ƽ����
  • ? ʹ�ú��ʵ��α�����

3. ��ȫ����

  • ? ʼ��ʹ�ò�������ѯ
  • ? ��֤�û�����
  • ? ʹ������֤һ����

4. ������

  • ? ʼ�ռ�鷵��ֵ
  • ? ��¼������־
  • ? ʵ�����Ի���

5. ������֯

  • ? ʹ�����ģʽ
  • ? ��װ���ò���
  • ? ���ִ�������

������: 2026-01-21

VB6及其LOGO版权为微软公司所有