��ѯ����
���ĵ���ϸ���� cDataBase ��IJ�ѯ���ܣ�����������ѯ������������ȡ�
? Ŀ¼
������ѯ
Sql ����
Sql ������������ SQL ��ѯ��䡣
�
vb
Function Sql(ByVal RawSqlString As String) As cDataBaseʾ��
vb
' ���� SQL ���
db.Sql("SELECT * FROM users WHERE age > 18")Query ����
Query ����ִ�в�ѯ�����ز���ֵ����ѯ����洢�� db.Rs �����У���������Ϊ����ֵ��
�
vb
Function Query(Optional CurType As CursorTypeEnum = adOpenKeyset, _
Optional LockType As LockTypeEnum = adLockOptimistic, _
Optional Options As Long = -1) As Boolean����˵��
| ���� | ���� | ˵�� |
|---|---|---|
CurType | CursorTypeEnum | �α����ͣ���ѡ��Ĭ�� adOpenKeyset�� |
LockType | LockTypeEnum | �������ͣ���ѡ��Ĭ�� adLockOptimistic�� |
Options | Long | ��ѯѡ���ѡ��Ĭ�� -1�� |
����ֵ
True- ��ѯ�ɹ�������洢��db.Rs��False- ��ѯʧ�ܣ���ͨ��LastErr�鿴������Ϣ��
��Ҫ��Query �������ص��Dz���ֵ������ Recordset����ѯ�����Ҫͨ�� db.Rs ���Է��ʡ�
ʾ��
vb
' ������ѯ
If db.Sql("SELECT * FROM users").Query Then
' ��ѯ�ɹ���ʹ�� db.Rs ���� Recordset�����Ƿ���ֵ��
Do Until db.Rs.EOF
Debug.Print db.Rs("name")
db.Rs.MoveNext
Loop
' ʹ����Ϻ�ر� Recordset
db.Rs.Close
End If
' ? ����ʾ������Ҫ�� Query �ķ���ֵ���� Recordset ����
' Dim Rs As ADODB.Recordset
' Set Rs = db.Query("SELECT * FROM users") ' ����Query ���ز���ֵ
' ? ��ȷʾ����ʹ�� db.Rs ���ʽ��
If db.Sql("SELECT * FROM users").Query Then
' ʹ�� db.Rs ���ʽ��
Do Until db.Rs.EOF
Debug.Print db.Rs("name")
db.Rs.MoveNext
Loop
db.Rs.Close
End IfFetch ����
Fetch ����ִ�в�ѯ���Զ�ת��Ϊ Dictionary ���ϡ���Ҫ���������� JSON ��������ݽ������ر��ʺ��� cHttpServer ��������ʹ�á�
�
vb
Function Fetch(Optional CurType As CursorTypeEnum = adOpenKeyset, _
Optional LockType As LockTypeEnum = adLockOptimistic, _
Optional Options As Long = -1) As Boolean����
- ִ�в�ѯ
- �Զ��� Recordset ת��Ϊ Collection���洢��
Rows�����У� - �Զ����õ�һ�е�
Row���� - ��ֱ������ JSON ���л������
cJson��cHttpServerResponseʹ��
ʾ��
vb
' ��ѯ���Զ�ת��
If db.Sql("SELECT * FROM users WHERE age > 18").Fetch Then
' ���ʵ�һ��
Debug.Print db.Row("name")
Debug.Print db.Row("age")
' ����������
Dim i As Long
For i = 1 To db.Rows.Count
Debug.Print db.Rows(i)("name")
Next
End IfJSON �����������Ҫ��;��
Fetch ��������Ҫ�����Ƿ��㽫��ѯ���ת��Ϊ JSON �ַ����������ⲿ���ݽ�����
vb
' �� HttpServer ·����ʹ��
Private Sub Server_OnRoute(ctx As VBMAN.cHttpServerContext)
' ��ѯ����
If ctx.Db.Sql("SELECT * FROM users").Fetch Then
' ֱ����� JSON��һ�仰��ɣ�
ctx.Response.Json ctx.Db.Rows
End If
End Sub
' ����ʹ�� cJson ����
Dim json As New VBMAN.cJson
If db.Sql("SELECT * FROM users").Fetch Then
Dim sJson As String
sJson = json.Encode(db.Rows) ' ת��Ϊ JSON �ַ���
Debug.Print sJson
End Ifע����cHttpServerResponse.Json �������Զ�ʶ�� Recordset ��ת������ʹ�� Fetch ��ֱ�Ӵ��� db.Rows��Collection������Ч����Ϊ�Ѿ����ת����
��ѯ�����Ա�
| ���� | �������� | �Զ�ת�� | ʹ�ó��� |
|---|---|---|---|
Query | Recordset | �� | ��Ҫֱ�Ӳ��� Recordset����Ҫ Recordset �ĸ����� |
Fetch | Collection | �� | ��Ҫ���� JSON ��������ݽ�������� cHttpServer��cJson ʹ�� |
���������
Rs ����
Rs �����ṩ�� ADO Recordset ��ֱ�ӷ��ʡ�
vb
' ʹ�� Recordset
If db.Sql("SELECT * FROM users").Query Then
Do Until db.Rs.EOF
Debug.Print db.Rs("name")
Debug.Print db.Rs("age")
db.Rs.MoveNext
Loop
' ��ȡ��¼��
Debug.Print "�ܼ�¼��: " & db.Rs.RecordCount
End IfRows ����
Rows ������ת����� Collection�����������е� Dictionary������ֱ������ JSON ���л���
vb
' ʹ�� Collection
If db.Sql("SELECT * FROM users").Fetch Then
Dim i As Long
For i = 1 To db.Rows.Count
Debug.Print db.Rows(i)("name")
Debug.Print db.Rows(i)("age")
Next
End IfJSON ���ʾ��
vb
' ���� 1���� HttpServer ��ֱ����� JSON
Private Sub Server_OnRoute(ctx As VBMAN.cHttpServerContext)
If ctx.Db.Sql("SELECT * FROM users").Fetch Then
' ֱ�����Ϊ JSON���Ƽ���ʽ��
ctx.Response.Json ctx.Db.Rows
End If
End Sub
' ���� 2��ʹ�� cJson ����ת��Ϊ JSON �ַ���
Dim json As New VBMAN.cJson
If db.Sql("SELECT * FROM users").Fetch Then
Dim sJson As String
sJson = json.Encode(db.Rows)
' ���: [{"id":1,"name":"����","age":25},{"id":2,"name":"����","age":30}]
End If
' ���� 3������ҳ�� JSON ���
If db.Sql("SELECT * FROM users").Page(1, 10).Fetch Then
Dim lTotal As Long
lTotal = db.Count("users")
' ����������ͷ�ҳ��Ϣ�� JSON
ctx.Response.Json db.Rows, 200, "�ɹ�", lTotal
End IfRow ����
Row �����ǵ�ǰ��һ�е� Dictionary��
vb
' ���ʵ�һ��
If db.Sql("SELECT * FROM users WHERE id = 1").Fetch Then
Debug.Print db.Row("name")
Debug.Print db.Row("age")
End If������ṹ
Rows (Collection)
������ Rows(1) (Dictionary)
�� ������ "id" => 1
�� ������ "name" => "����"
�� ������ "age" => 25
������ Rows(2) (Dictionary)
�� ������ "id" => 2
�� ������ "name" => "����"
�� ������ "age" => 30
������ ...��ѯѡ��
����� (CursorTypeEnum)
| ���� | ֵ | ˵�� |
|---|---|---|
adOpenForwardOnly | 0 | ����ǰ�α꣨��죩 |
adOpenKeyset | 1 | �����α꣨Ĭ�ϣ� |
adOpenDynamic | 2 | ��̬�α� |
adOpenStatic | 3 | ��̬�α� |
vb
' ʹ�ý���ǰ�α꣨������ѣ�
db.Sql("SELECT * FROM users").Query adOpenForwardOnly
' ʹ�þ�̬�α֧꣨�� RecordCount��
db.Sql("SELECT * FROM users").Query adOpenStatic�������� (LockTypeEnum)
| ���� | ֵ | ˵�� |
|---|---|---|
adLockReadOnly | 1 | ֻ����Ĭ�ϲ�ѯ�� |
adLockPessimistic | 2 | �������� |
adLockOptimistic | 3 | �ֹ����� |
adLockBatchOptimistic | 4 | �����ֹ����� |
vb
' ֻ����ѯ��������ѣ�
db.Sql("SELECT * FROM users").Query adOpenKeyset, adLockReadOnly������ѯ����
���� 1��������¼��ѯ
vb
' ��ѯ������¼
If db.Sql("SELECT * FROM users WHERE id = 1").Fetch Then
If db.Rows.Count > 0 Then
Debug.Print "�û���: " & db.Row("name")
Debug.Print "����: " & db.Row("age")
Else
Debug.Print "δ�ҵ���¼"
End If
End If���� 2��������ѯ
vb
' ��������ѯ
Dim sSql As String
sSql = "SELECT * FROM users WHERE age > 18 AND status = 'active'"
If db.Sql(sSql).Fetch Then
Dim i As Long
For i = 1 To db.Rows.Count
Debug.Print db.Rows(i)("name")
Next
End If���� 3�������ѯ
vb
' �����併������
If db.Sql("SELECT * FROM users ORDER BY age DESC").Fetch Then
Dim i As Long
For i = 1 To db.Rows.Count
Debug.Print db.Rows(i)("name") & " - " & db.Rows(i)("age")
Next
End If���� 4���ۺϲ�ѯ
vb
' ͳ�Ʋ�ѯ
If db.Sql("SELECT COUNT(*) AS cnt, AVG(age) AS avg_age FROM users").Fetch Then
If db.Rows.Count > 0 Then
Debug.Print "���û���: " & db.Row("cnt")
Debug.Print "ƽ������: " & db.Row("avg_age")
End If
End If���� 5�������ѯ
vb
' ����ͳ��
If db.Sql("SELECT status, COUNT(*) AS cnt FROM users GROUP BY status").Fetch Then
Dim i As Long
For i = 1 To db.Rows.Count
Debug.Print db.Rows(i)("status") & ": " & db.Rows(i)("cnt")
Next
End If���� 6��������ѯ
vb
' �����Ӳ�ѯ
Dim sSql As String
sSql = "SELECT u.name, p.title " & _
"FROM users u " & _
"INNER JOIN posts p ON u.id = p.user_id"
If db.Sql(sSql).Fetch Then
Dim i As Long
For i = 1 To db.Rows.Count
Debug.Print db.Rows(i)("name") & " - " & db.Rows(i)("title")
Next
End If���� 7���Ӳ�ѯ
vb
' �Ӳ�ѯ
Dim sSql As String
sSql = "SELECT * FROM users " & _
"WHERE id IN (SELECT user_id FROM orders WHERE amount > 1000)"
If db.Sql(sSql).Fetch Then
' �������
End If���� 8��ģ����ѯ
vb
' LIKE ��ѯ
If db.Sql("SELECT * FROM users WHERE name LIKE '%��%'").Fetch Then
Dim i As Long
For i = 1 To db.Rows.Count
Debug.Print db.Rows(i)("name")
Next
End If���� 9����ҳ��ѯ
vb
' ʹ�� Page ��������� pagination.md��
If db.Sql("SELECT * FROM users").Page(1, 10).Fetch Then
Dim i As Long
For i = 1 To db.Rows.Count
Debug.Print db.Rows(i)("name")
Next
End If���� 10����������ѯ
vb
' ʹ�ò�������ѯ����� parameterized.md��
If db.Sql("SELECT * FROM users WHERE name = ? AND age > ?") _
.Param("name", "����", VBMAN.adVarWChar) _
.Param("age", 18, VBMAN.adInteger) _
.QueryParam Then
Dim i As Long
For i = 1 To db.Rows.Count
Debug.Print db.Rows(i)("name")
Next
End If���� 11��JSON �����Fetch ����Ҫ������
vb
' �� HttpServer ·������� JSON
Private Sub Server_OnRoute(ctx As VBMAN.cHttpServerContext)
' ��ѯ���ݲ�ת��Ϊ Collection
If ctx.Db.Sql("SELECT * FROM users WHERE status = 'active'").Fetch Then
' ֱ����� JSON��һ�仰��ɣ�
ctx.Response.Json ctx.Db.Rows
End If
End Sub
' ����ҳ�� JSON API
Private Sub Server_OnRoute(ctx As VBMAN.cHttpServerContext)
Dim lPage As Long
Dim lPageSize As Long
lPage = CLng(ctx.Request.Query("page"))
lPageSize = CLng(ctx.Request.Query("pageSize"))
' ��ѯ��ҳ����
If ctx.Db.Sql("SELECT * FROM users ORDER BY id").Page(lPage, lPageSize).Fetch Then
Dim lTotal As Long
lTotal = ctx.Db.Count("users")
' ��� JSON���������ݡ���������Ϣ
ctx.Response.Json ctx.Db.Rows, 200, "�ɹ�", lTotal
End If
End Sub
' ʹ�� cJson ����ת��Ϊ JSON �ַ���
Dim json As New VBMAN.cJson
If db.Sql("SELECT * FROM users").Fetch Then
Dim sJson As String
sJson = json.Encode(db.Rows)
' ���������ļ����桢���紫���
Debug.Print sJson
End If������
1. ֻ��ѯ��Ҫ���ֶ�
vb
' ? ���Ƽ�����ѯ�����ֶ�
db.Sql("SELECT * FROM users").Query
' ? �Ƽ���ֻ��ѯ��Ҫ���ֶ�
db.Sql("SELECT id, name FROM users").Query2. ʹ�������ֶ�
vb
' ? �Ƽ���ʹ�������ֶ���Ϊ����
db.Sql("SELECT * FROM users WHERE id = 1").Query3. ���ƽ������С
vb
' ? �Ƽ���ʹ�� TOP ���ƽ��
db.Sql("SELECT TOP 100 * FROM users").Query4. ʹ�ú��ʵ��α�����
vb
' ? �Ƽ���ֻ����ѯʹ�� ForwardOnly
db.Sql("SELECT * FROM users").Query adOpenForwardOnly, adLockReadOnly������
vb
' ��ѯ������
If Not db.Sql("SELECT * FROM users").Query Then
Debug.Print "��ѯʧ��"
Debug.Print "�������: " & db.LastErrNumber
Debug.Print "��������: " & db.LastErrDescription
Debug.Print "������Ϣ: " & db.LastErr
Exit Sub
End If���ʵ��
1. ʼ�ռ�鷵��ֵ
vb
' ? �Ƽ�
If db.Sql("SELECT * FROM users").Query Then
' �������
Else
' ��������
End If2. ʹ�� Fetch ���� JSON ���
vb
' ? �Ƽ���ʹ�� Fetch ���� JSON �������Ҫ������
If db.Sql("SELECT * FROM users").Fetch Then
' �� HttpServer ��ֱ�����
ctx.Response.Json db.Rows
' ֻ���һ�м�¼��
ctx.Response.Json db.Row
' ��ʹ�� cJson ����
Dim json As New VBMAN.cJson
Dim sJson As String
sJson = json.Encode(db.Rows)
End If3. ��ʱ�ͷ���Դ
vb
' ? �Ƽ�����ѯ��ɺ�ر� Recordset
If db.Sql("SELECT * FROM users").Query Then
' �������
If db.Rs.State <> adStateClosed Then
db.Rs.Close
End If
End If������: 2026-01-21