��������ѯ
���ĵ���ϸ���� cDataBase ��IJ�������ѯ���ܣ����Ƿ�ֹ SQL ע�빥������Ҫ��ȫ���ԡ�
? Ŀ¼
��������ѯ����
ʲô�Dz�������ѯ
��������ѯ�ǽ� SQL ���Ͳ���ֵ�ֿ������ļ���������ֵͨ��ռλ����ͨ���� ?�����ݡ�
Ϊʲôʹ�ò�������ѯ
- ��ֹ SQL ע�� - ����ֵ�ᱻת�壬��ִ�ж��� SQL ����
- �����Ż� - ���ݿ���Ի���ִ�мƻ�
- ���Ͱ�ȫ - �Զ�������������ת��
- �������� - SQL ���Ͳ���ֵ���룬����ά��
SQL ע��ʾ��
vb
' ? Σ�գ�ֱ��ƴ�� SQL������ SQL ע�룩
Dim sName As String
sName = "'; DROP TABLE users; --"
db.Sql("SELECT * FROM users WHERE name = '" & sName & "'").Query
' ʵ��ִ�е� SQL: SELECT * FROM users WHERE name = ''; DROP TABLE users; --'
' �����users ����ɾ����
' ? ��ȫ��ʹ�ò�������ѯ
db.Sql("SELECT * FROM users WHERE name = ?") _
.Param("name", sName, VBMAN.adVarWChar) _
.QueryParam
' ����ֵ�ᱻ��ȫ��������ִ�ж������Param ����
�
vb
Function Param(ByVal ParamName As String, _
ByVal ParamValue As Variant, _
Optional ByVal ParamType As DataTypeEnum = adVarChar) As cDataBase����˵��
| ���� | ���� | ˵�� |
|---|---|---|
ParamName | String | �������ƣ����ڱ�ʶ��ʵ��ʹ�� ? ռλ���� |
ParamValue | Variant | ����ֵ�����裩 |
ParamType | DataTypeEnum | �����������ͣ���ѡ��Ĭ�� adVarChar�� |
����ֵ
���� cDataBase ����֧����ʽ���á�
ʾ��
vb
' ���ӵ�������
db.Sql("SELECT * FROM users WHERE id = ?") _
.Param("id", 1, VBMAN.adInteger)
' ���Ӷ����������ʽ���ã�
db.Sql("SELECT * FROM users WHERE name = ? AND age > ?") _
.Param("name", "����", VBMAN.adVarWChar) _
.Param("age", 18, VBMAN.adInteger)ExecParam ����
�
ExecParam ����ִ�д������� INSERT��UPDATE��DELETE ������
vb
Function ExecParam(Optional RecordsAffected) As Boolean����˵��
| ���� | ���� | ˵�� |
|---|---|---|
RecordsAffected | Variant | ������Ӱ�����������ѡ�� |
����ֵ
True- ִ�гɹ�False- ִ��ʧ�ܣ���ͨ��LastErr�鿴������Ϣ��
ʾ��
vb
' ��������
If db.Sql("INSERT INTO users (name, age, email) VALUES (?, ?, ?)") _
.Param("name", "����", VBMAN.adVarWChar) _
.Param("age", 25, VBMAN.adInteger) _
.Param("email", "zhangsan@example.com", VBMAN.adVarWChar) _
.ExecParam Then
Debug.Print "����ɹ���ID: " & db.LastInsertId
End If
' ��������
If db.Sql("UPDATE users SET age = ?, email = ? WHERE id = ?") _
.Param("age", 26, VBMAN.adInteger) _
.Param("email", "newemail@example.com", VBMAN.adVarWChar) _
.Param("id", 1, VBMAN.adInteger) _
.ExecParam Then
Debug.Print "���³ɹ�"
End If
' ɾ������
If db.Sql("DELETE FROM users WHERE id = ?") _
.Param("id", 1, VBMAN.adInteger) _
.ExecParam Then
Debug.Print "ɾ���ɹ�"
End IfQueryParam ����
�
QueryParam ����ִ�д������� SELECT ��ѯ��
vb
Function QueryParam(Optional CurType As CursorTypeEnum = adOpenKeyset, _
Optional LockType As LockTypeEnum = adLockOptimistic) As Boolean����˵��
| ���� | ���� | ˵�� |
|---|---|---|
CurType | CursorTypeEnum | �α����ͣ���ѡ��Ĭ�� adOpenKeyset�� |
LockType | LockTypeEnum | �������ͣ���ѡ��Ĭ�� adLockOptimistic�� |
����ֵ
True- ��ѯ�ɹ�������洢��db.Rs��False- ��ѯʧ�ܣ���ͨ��LastErr�鿴������Ϣ��
��Ҫ��QueryParam �������ص��Dz���ֵ������ Recordset����ѯ�����Ҫͨ�� db.Rs ���Է��ʡ�
ʾ��
vb
' ��ѯ������¼��ʹ�� db.Rs ���ʽ����
If db.Sql("SELECT * FROM users WHERE id = ?") _
.Param("id", 1, VBMAN.adInteger) _
.QueryParam Then
' ʹ�� db.Rs ���� Recordset
If Not db.Rs.EOF Then
Debug.Print db.Rs("name")
End If
db.Rs.Close
End If
' ��ѯ������¼��ʹ�� db.Rs ���ʽ����
If db.Sql("SELECT * FROM users WHERE age > ? AND status = ?") _
.Param("age", 18, VBMAN.adInteger) _
.Param("status", "active", VBMAN.adVarWChar) _
.QueryParam Then
' ʹ�� db.Rs �������
Do Until db.Rs.EOF
Debug.Print db.Rs("name")
db.Rs.MoveNext
Loop
db.Rs.Close
End If
' ���ʹ�� Fetch ���������Զ�ת��Ϊ db.Rows �� db.Row
If db.Sql("SELECT * FROM users WHERE id = ?") _
.Param("id", 1, VBMAN.adInteger) _
.QueryParam Then
' ʹ�� Fetch ����Է��� db.Rows �� db.Row
If db.Fetch Then
If db.Rows.Count > 0 Then
Debug.Print db.Row("name")
End If
End If
End If��������
������������
| ���� | ֵ | ˵�� | ʾ�� |
|---|---|---|---|
adVarChar | 200 | �ɱ䳤���ַ��� | "����" |
adVarWChar | 202 | Unicode �ַ��� | "����" |
adInteger | 3 | 32 ��� | 25 |
adBigInt | 20 | 64 ��� | 1234567890 |
adDouble | 5 | ˫���ȸ����� | 3.14 |
adDate | 7 | ����ʱ�� | Now |
adBoolean | 11 | ����ֵ | True |
adDecimal | 14 | ��ȷ��ֵ | 99.99 |
��������ѡ��
vb
' �ַ���
db.Param("name", "����", VBMAN.adVarWChar) ' Unicode �ַ������Ƽ���
db.Param("name", "����", VBMAN.adVarChar) ' ANSI �ַ���
' ����
db.Param("age", 25, VBMAN.adInteger) ' 32 ���
db.Param("id", 1234567890, VBMAN.adBigInt) ' 64 ���
' ������
db.Param("price", 99.99, VBMAN.adDouble) ' ˫����
db.Param("amount", 99.99, VBMAN.adDecimal) ' ��ȷ��ֵ
' ����ʱ��
db.Param("created", Now, VBMAN.adDate) ' ����ʱ��
' ����ֵ
db.Param("active", True, VBMAN.adBoolean) ' ����ֵ��ȫ����
SQL ע�����
vb
' ? Σ�գ�ֱ��ƴ��
Dim sInput As String
sInput = "'; DROP TABLE users; --"
db.Sql("SELECT * FROM users WHERE name = '" & sInput & "'").Query
' �����users ����ɾ��
' ? ��ȫ����������ѯ
db.Sql("SELECT * FROM users WHERE name = ?") _
.Param("name", sInput, VBMAN.adVarWChar) _
.QueryParam
' ����ֵ����ȫ��������ִ�ж�����������ַ�����
vb
' ? Σ�գ������ַ����ܵ��´���
Dim sName As String
sName = "O'Brien"
db.Sql("SELECT * FROM users WHERE name = '" & sName & "'").Query
' SQL: SELECT * FROM users WHERE name = 'O'Brien'
' ��������δת��
' ? ��ȫ����������ѯ�Զ�����
db.Sql("SELECT * FROM users WHERE name = ?") _
.Param("name", sName, VBMAN.adVarWChar) _
.QueryParam
' �����ַ����Զ�ת��ʹ��ʾ��
ʾ�� 1���û���¼
vb
Function UserLogin(sUsername As String, sPassword As String) As Boolean
' ʹ�ò�������ѯ��ֹ SQL ע��
If db.Sql("SELECT * FROM users WHERE username = ? AND password = ?") _
.Param("username", sUsername, VBMAN.adVarWChar) _
.Param("password", sPassword, VBMAN.adVarWChar) _
.QueryParam Then
If db.Rows.Count > 0 Then
UserLogin = True
Else
UserLogin = False
End If
Else
UserLogin = False
End If
End Functionʾ�� 2����������
vb
Function SearchUsers(sKeyword As String) As Collection
Set SearchUsers = New Collection
' ʹ�� LIKE �Ͳ�������ѯ
Dim sSql As String
sSql = "SELECT * FROM users WHERE name LIKE ? OR email LIKE ?"
If db.Sql(sSql) _
.Param("name", "%" & sKeyword & "%", VBMAN.adVarWChar) _
.Param("email", "%" & sKeyword & "%", VBMAN.adVarWChar) _
.QueryParam Then
Set SearchUsers = db.Rows
End If
End Functionʾ�� 3����������
vb
Sub BatchInsertUsers(colUsers As Collection)
db.TransBegin
Dim i As Long
For i = 1 To colUsers.Count
Dim dictUser As Scripting.Dictionary
Set dictUser = colUsers(i)
If Not db.Sql("INSERT INTO users (name, age, email) VALUES (?, ?, ?)") _
.Param("name", dictUser("name"), VBMAN.adVarWChar) _
.Param("age", dictUser("age"), VBMAN.adInteger) _
.Param("email", dictUser("email"), VBMAN.adVarWChar) _
.ExecParam Then
db.TransRollback
Exit Sub
End If
Next
db.TransCommit
End Subʾ�� 4����̬��ѯ
vb
Function GetUsers(Optional sName As String = "", _
Optional lMinAge As Long = 0, _
Optional sStatus As String = "") As Collection
Set GetUsers = New Collection
Dim sSql As String
sSql = "SELECT * FROM users WHERE 1=1"
' ��̬���� SQL �Ͳ���
If sName <> "" Then
sSql = sSql & " AND name LIKE ?"
End If
If lMinAge > 0 Then
sSql = sSql & " AND age >= ?"
End If
If sStatus <> "" Then
sSql = sSql & " AND status = ?"
End If
' ���� SQL
db.Sql(sSql)
' ���Ӳ���
If sName <> "" Then
db.Param("name", "%" & sName & "%", VBMAN.adVarWChar)
End If
If lMinAge > 0 Then
db.Param("age", lMinAge, VBMAN.adInteger)
End If
If sStatus <> "" Then
db.Param("status", sStatus, VBMAN.adVarWChar)
End If
' ִ�в�ѯ
If db.QueryParam Then
Set GetUsers = db.Rows
End If
End Function���ʵ��
1. ʼ��ʹ�ò�������ѯ�����û�����
vb
' ? �Ƽ���ʹ�ò�������ѯ
db.Sql("SELECT * FROM users WHERE name = ?") _
.Param("name", txtName.Text, VBMAN.adVarWChar) _
.QueryParam
' ? ���Ƽ���ֱ��ƴ���û�����
db.Sql("SELECT * FROM users WHERE name = '" & txtName.Text & "'").Query2. ѡ����ȷ����������
vb
' ? �Ƽ�����ȷָ����������
db.Param("age", 25, VBMAN.adInteger)
db.Param("name", "����", VBMAN.adVarWChar)
db.Param("price", 99.99, VBMAN.adDecimal)
' ? ���Ƽ���ʹ��Ĭ�����ͣ��������Ͳ�ƥ�䣩
db.Param("age", 25) ' Ĭ�� adVarChar�����ܳ���3. ʹ�� Unicode �ַ�������
vb
' ? �Ƽ���ʹ�� adVarWChar ֧������
db.Param("name", "����", VBMAN.adVarWChar)
' ? ���Ƽ���ʹ�� adVarChar�������������룩
db.Param("name", "����", VBMAN.adVarChar)4. ����˳��Ҫ�� SQL �е�ռλ��˳��һ��
vb
' ? ��ȷ������˳���� ? ˳��һ��
db.Sql("SELECT * FROM users WHERE name = ? AND age > ?") _
.Param("name", "����", VBMAN.adVarWChar) _
.Param("age", 18, VBMAN.adInteger)
' ? ������˳�����
db.Sql("SELECT * FROM users WHERE name = ? AND age > ?") _
.Param("age", 18, VBMAN.adInteger) _
.Param("name", "����", VBMAN.adVarWChar)5. ��������
vb
' ע�⣺ExecParam �� QueryParam ���Զ���������
' ���ֻ���� Param ����ִ�У���Ҫ�ֶ�������ͨ��ִ�л����´���������������
Q1: ��������ѯ��ֱ��ƴ������
�ش�: ���ᣬ��������ѯͨ�����죬��Ϊ��
- ���ݿ���Ի���ִ�мƻ�
- ������ SQL ����ʱ��
- �������ַ���ƴ�ӿ���
Q2: ����� LIKE ��ѯ��ʹ�ò�����
vb
' ? ��ȷ���ڲ���ֵ�а���ͨ���
db.Sql("SELECT * FROM users WHERE name LIKE ?") _
.Param("name", "%" & sKeyword & "%", VBMAN.adVarWChar) _
.QueryParam
' ? ������ SQL ��ʹ��ͨ���
db.Sql("SELECT * FROM users WHERE name LIKE '%?%'") _
.Param("name", sKeyword, VBMAN.adVarWChar) _
.QueryParamQ3: ��δ��� NULL ֵ��
vb
' ʹ�� Null ֵ
db.Sql("SELECT * FROM users WHERE email = ?") _
.Param("email", Null, VBMAN.adVarWChar) _
.QueryParam
' ����ʹ�� IS NULL
db.Sql("SELECT * FROM users WHERE email IS NULL").QueryQ4: ��������ѯ֧�� IN �Ӿ���
vb
' ע�⣺ADO ��������ѯ�� IN �Ӿ�֧������
' ���飺ʹ�ö�� OR ������̬���� SQL
' ���� 1��ʹ�ö�� OR
db.Sql("SELECT * FROM users WHERE id = ? OR id = ? OR id = ?") _
.Param("id1", 1, VBMAN.adInteger) _
.Param("id2", 2, VBMAN.adInteger) _
.Param("id3", 3, VBMAN.adInteger) _
.QueryParam
' ���� 2����̬��������Ҫ��֤���룩
Dim sIds As String
sIds = "1,2,3" ' ��Ҫ��֤��ʽ
db.Sql("SELECT * FROM users WHERE id IN (" & sIds & ")").Query������: 2026-01-21