������
���ĵ����� 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 = '����'").Query2. ֻ��ѯ��Ҫ���ֶ�
vb
' ? �Ƽ���ֻ��ѯ��Ҫ���ֶ�
db.Sql("SELECT id, name FROM users").Query
' ? ���Ƽ�����ѯ�����ֶ�
db.Sql("SELECT * FROM users").Query3. ʹ�ú��ʵ��α�����
vb
' ? �Ƽ���ֻ����ѯʹ�� ForwardOnly
db.Sql("SELECT * FROM users").Query adOpenForwardOnly, adLockReadOnly
' ? ���Ƽ���ʹ��Ĭ���α꣨���ܽ�����
db.Sql("SELECT * FROM users").Query4. ���ƽ������С
vb
' ? �Ƽ���ʹ�� TOP/LIMIT ���ƽ��
db.Sql("SELECT TOP 100 * FROM users").Query
' ? ���Ƽ�����ѯ��������
db.Sql("SELECT * FROM users").Query5. ʹ�÷�ҳ
vb
' ? �Ƽ���ʹ�÷�ҳ
db.Sql("SELECT * FROM users").Page(1, 20).Query
' ? ���Ƽ���һ���Բ�ѯ��������
db.Sql("SELECT * FROM users").Query6. �����ѯ���
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").QueryQ3: �ڴ治��
����: ��ѯ�������ݵ����ڴ治�㡣
���:
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 IfQ4: ������ͻ
����: �������ͬʱִ�е��³�ͻ��
���:
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