Skip to content

��ѯ����

���ĵ���ϸ���� 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

����˵��

��������˵��
CurTypeCursorTypeEnum�α����ͣ���ѡ��Ĭ�� adOpenKeyset��
LockTypeLockTypeEnum�������ͣ���ѡ��Ĭ�� adLockOptimistic��
OptionsLong��ѯѡ���ѡ��Ĭ�� -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 If

Fetch ����

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 If

JSON �����������Ҫ��;��

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������Ч����Ϊ�Ѿ����ת����


��ѯ�����Ա�

�������������Զ�ת��ʹ�ó���
QueryRecordset����Ҫֱ�Ӳ��� Recordset����Ҫ Recordset �ĸ߼�����
FetchCollection����Ҫ���� 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 If

Rows ����

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 If

JSON ���ʾ��

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 If

Row ����

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)

����ֵ˵��
adOpenForwardOnly0����ǰ�α꣨��죩
adOpenKeyset1�����α꣨Ĭ�ϣ�
adOpenDynamic2��̬�α�
adOpenStatic3��̬�α�
vb
' ʹ�ý���ǰ�α꣨������ѣ�
db.Sql("SELECT * FROM users").Query adOpenForwardOnly

' ʹ�þ�̬�α֧꣨�� RecordCount��
db.Sql("SELECT * FROM users").Query adOpenStatic

�������� (LockTypeEnum)

����ֵ˵��
adLockReadOnly1ֻ����Ĭ�ϲ�ѯ��
adLockPessimistic2��������
adLockOptimistic3�ֹ�����
adLockBatchOptimistic4�����ֹ�����
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").Query

2. ʹ�������ֶ�

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

3. ���ƽ������С

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

4. ʹ�ú��ʵ��α�����

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 If

2. ʹ�� 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 If

3. ��ʱ�ͷ���Դ

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

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