���߷���
���ĵ���ϸ���� cDataBase ���ṩ�ĸ��ֹ��߷��������ڼ������ݿ������
? Ŀ¼
- Count ����
- LastInsertId ����
- TableExists ����
- GetTableFields ����
- GetTables ����
- GetDatabases ����
- GetVersion ����
- Escape ����
- CheckConnection ����
Count ����
�
ͳ�Ʊ��еļ�¼����
vb
Function Count(Optional ByVal TableName As String = "") As Long����˵��
| ���� | ���� | ˵�� |
|---|---|---|
TableName | String | ��������ѡ��Ϊ��ʱʹ�õ�ǰ SQL�� |
����ֵ
���ؼ�¼����Long ���ͣ���
ʾ��
vb
' ͳ��ָ�����ļ�¼��
Dim lCount As Long
lCount = db.Count("users")
Debug.Print "�û�����: " & lCount
' ͳ�Ƶ�ǰ SQL ��ѯ����ļ�¼��
db.Sql("SELECT * FROM users WHERE age > 18")
lCount = db.Count() ' ʹ�õ�ǰ SQL
Debug.Print "�����û���: " & lCountʵ��ԭ��
vb
' ���ָ���˱���
SELECT COUNT(*) AS cnt FROM users
' ���ʹ�õ�ǰ SQL
SELECT COUNT(*) AS cnt FROM (SELECT * FROM users WHERE age > 18) AS TLastInsertId ����
�
��ȡ��������������� ID��
vb
Function LastInsertId() As Variant����ֵ
����������� ID��Variant ���ͣ���
֧�ֵ����ݿ�
| ���ݿ� | ʵ�ַ�ʽ |
|---|---|
| SQL Server | SCOPE_IDENTITY() |
| MySQL | LAST_INSERT_ID() |
| Access | @@IDENTITY |
ʾ��
vb
' ��������
If db.Sql("INSERT INTO users (name, age) VALUES ('����', 25)").Exec Then
' ��ȡ������� ID
Dim lId As Variant
lId = db.LastInsertId
Debug.Print "���û� ID: " & lId
End Ifע������
������ INSERT ����������
vb' ? ��ȷ db.Sql("INSERT INTO users (name) VALUES ('����')").Exec Dim lId As Variant lId = db.LastInsertId ' ? �����м����������� db.Sql("INSERT INTO users (name) VALUES ('����')").Exec db.Sql("SELECT * FROM users").Query ' ������� ID Dim lId As Variant lId = db.LastInsertId ' ���ܻ�ȡ������ȷ�� ID��֧����������
vb' ���������������� CREATE TABLE users ( id INT IDENTITY(1,1) PRIMARY KEY, -- SQL Server name NVARCHAR(50) )
TableExists ����
�
�жϱ��Ƿ���ڡ�
vb
Function TableExists(ByVal TableName As String) As Boolean����˵��
| ���� | ���� | ˵�� |
|---|---|---|
TableName | String | ���������裩 |
����ֵ
True- ������False- ��������
֧�ֵ����ݿ�
| ���ݿ� | ʵ�ַ�ʽ |
|---|---|
| SQL Server | INFORMATION_SCHEMA.TABLES |
| MySQL | INFORMATION_SCHEMA.TABLES |
| Access | MSysObjects |
ʾ��
vb
' �����Ƿ����
If db.TableExists("users") Then
Debug.Print "users ������"
Else
Debug.Print "users ��������"
' ������
db.Sql("CREATE TABLE users (id INT PRIMARY KEY, name NVARCHAR(50))").Exec
End Ifʹ�ó���
vb
' ���� 1��������ǰ���
If Not db.TableExists("users") Then
db.Sql("CREATE TABLE users (...)").Exec
End If
' ���� 2��ɾ����ǰ���
If db.TableExists("temp_table") Then
db.Sql("DROP TABLE temp_table").Exec
End IfGetTableFields ����
�
��ȡ�����ֶ��б���
vb
Function GetTableFields(ByVal TableName As String) As Collection����˵��
| ���� | ���� | ˵�� |
|---|---|---|
TableName | String | ���������裩 |
����ֵ
�����ֶ������ϣ�Collection ���ͣ���
֧�ֵ����ݿ�
| ���ݿ� | ʵ�ַ�ʽ |
|---|---|
| SQL Server | INFORMATION_SCHEMA.COLUMNS |
| MySQL | INFORMATION_SCHEMA.COLUMNS |
| Access | MSysObjects |
ʾ��
vb
' ��ȡ�ֶ��б�
Dim colFields As Collection
Set colFields = db.GetTableFields("users")
' �����ֶ�
Dim i As Long
For i = 1 To colFields.Count
Debug.Print "�ֶ� " & i & ": " & colFields(i)
Nextʹ�ó���
vb
' ���� 1����̬���� SQL
Function BuildSelectSQL(sTableName As String) As String
Dim colFields As Collection
Set colFields = db.GetTableFields(sTableName)
Dim sFields As String
Dim i As Long
For i = 1 To colFields.Count
If sFields <> "" Then sFields = sFields & ", "
sFields = sFields & colFields(i)
Next
BuildSelectSQL = "SELECT " & sFields & " FROM " & sTableName
End Function
' ���� 2����֤�ֶ��Ƿ����
Function FieldExists(sTableName As String, sFieldName As String) As Boolean
Dim colFields As Collection
Set colFields = db.GetTableFields(sTableName)
Dim i As Long
For i = 1 To colFields.Count
If colFields(i) = sFieldName Then
FieldExists = True
Exit Function
End If
Next
FieldExists = False
End FunctionGetTables ����
�
��ȡ���ݿ��еı����б���
vb
Function GetTables(Optional ByVal DatabaseName As String = "") As Collection����˵��
| ���� | ���� | ˵�� |
|---|---|---|
DatabaseName | String | ���ݿ�������ѡ��Ϊ��ʱʹ�õ�ǰ���ݿ⣩ |
����ֵ
���ر������ϣ�Collection ���ͣ���
֧�ֵ����ݿ�
| ���ݿ� | ʵ�ַ�ʽ |
|---|---|
| SQL Server | INFORMATION_SCHEMA.TABLES |
| MySQL | INFORMATION_SCHEMA.TABLES |
| Access | MSysObjects |
ʾ��
vb
' ��ȡ��ǰ���ݿ�ı��б�
Dim colTables As Collection
Set colTables = db.GetTables()
' ��������
Dim i As Long
For i = 1 To colTables.Count
Debug.Print "�� " & i & ": " & colTables(i)
Next
' ��ȡָ�����ݿ�ı��б���SQL Server/MySQL��
Set colTables = db.GetTables("mydb")ʹ�ó���
vb
' ���� 1���г����б�
Sub ListAllTables()
Dim colTables As Collection
Set colTables = db.GetTables()
Dim i As Long
For i = 1 To colTables.Count
Debug.Print colTables(i)
Next
End Sub
' ���� 2���������б�
Sub BackupAllTables()
Dim colTables As Collection
Set colTables = db.GetTables()
Dim i As Long
For i = 1 To colTables.Count
Dim sTableName As String
sTableName = colTables(i)
db.Sql("SELECT * INTO " & sTableName & "_backup FROM " & sTableName).Exec
Next
End SubGetDatabases ����
�
��ȡ���ݿ�������е����ݿ��б���
vb
Function GetDatabases() As Collection����ֵ
�������ݿ������ϣ�Collection ���ͣ���
֧�ֵ����ݿ�
| ���ݿ� | ʵ�ַ�ʽ |
|---|---|
| SQL Server | sys.databases |
| MySQL | SHOW DATABASES |
ʾ��
vb
' ��ȡ���ݿ��б�
Dim colDatabases As Collection
Set colDatabases = db.GetDatabases()
' �������ݿ���
Dim i As Long
For i = 1 To colDatabases.Count
Debug.Print "���ݿ� " & i & ": " & colDatabases(i)
Nextʹ�ó���
vb
' ���� 1���г��������ݿ�
Sub ListAllDatabases()
Dim colDatabases As Collection
Set colDatabases = db.GetDatabases()
Dim i As Long
For i = 1 To colDatabases.Count
Debug.Print colDatabases(i)
Next
End Sub
' ���� 2���л����ݿ�
Sub SwitchDatabase(sDatabaseName As String)
' ������ݿ��Ƿ����
Dim colDatabases As Collection
Set colDatabases = db.GetDatabases()
Dim i As Long
Dim bExists As Boolean
bExists = False
For i = 1 To colDatabases.Count
If colDatabases(i) = sDatabaseName Then
bExists = True
Exit For
End If
Next
If bExists Then
db.Sql("USE " & sDatabaseName).Exec
Else
Debug.Print "���ݿⲻ����: " & sDatabaseName
End If
End SubGetVersion ����
�
��ȡ���ݿ�汾��Ϣ��
vb
Function GetVersion() As String����ֵ
���ذ汾�ַ�����String ���ͣ���
ʾ��
vb
' ��ȡ���ݿ�汾
Dim sVersion As String
sVersion = db.GetVersion
Debug.Print "���ݿ�汾: " & sVersionʹ�ó���
vb
' ���� 1��������ݿ�汾
Sub CheckDatabaseVersion()
Dim sVersion As String
sVersion = db.GetVersion
Debug.Print "��ǰ���ݿ�汾: " & sVersion
End Sub
' ���� 2���汾�����Լ��
Function IsVersionCompatible(sMinVersion As String) As Boolean
Dim sVersion As String
sVersion = db.GetVersion
' �Ƚϰ汾��...
IsVersionCompatible = True
End FunctionEscape ����
�
ת�� SQL �ַ����е������ַ�����ֹ SQL ע�룩��
vb
Function Escape(ByVal Str As String) As String����˵��
| ���� | ���� | ˵�� |
|---|---|---|
Str | String | Ҫת����ַ��������裩 |
����ֵ
����ת�����ַ�����String ���ͣ���
ʵ��ԭ��
�������� ' ת��Ϊ���������� ''��
ʾ��
vb
' ת���ַ���
Dim sName As String
sName = "O'Brien"
Dim sEscaped As String
sEscaped = db.Escape(sName)
' ���: "O''Brien"
' ʹ��ת�����ַ���
db.Sql("SELECT * FROM users WHERE name = '" & sEscaped & "'").Queryע������
�Ƽ�ʹ�ò�������ѯ������ Escape ������
vb
' ? �Ƽ���ʹ�ò�������ѯ
db.Sql("SELECT * FROM users WHERE name = ?") _
.Param("name", "O'Brien", VBMAN.adVarWChar) _
.QueryParam
' ?? ����ʹ�ã�ʹ�� Escape���������������ѯ��ȫ��
db.Sql("SELECT * FROM users WHERE name = '" & db.Escape("O'Brien") & "'").QueryCheckConnection ����
�
�������״̬������������
vb
Function CheckConnection() As Boolean����ֵ
True- ���������������ɹ�False- ���ӶϿ�������ʧ��
����
- �������״̬
- ����Ͽ���������������
- �������ӱ��
ʾ��
vb
' �������
If Not db.CheckConnection Then
Debug.Print "�����ѶϿ�����������..."
' ��������
db.Connect VBMAN.enumDbType_MsSql, "127.0.0.1,1433", "sa", "pwd", "mydb"
End Ifʹ�ó���
vb
' ���� 1�����ڼ������
Private Sub Timer1_Timer()
If Not db.CheckConnection Then
Debug.Print "���ӶϿ�����Ҫ��������"
End If
End Sub
' ���� 2������ǰ�������
Sub ExecuteQuery()
' �������
If Not db.CheckConnection Then
Debug.Print "���Ӳ�����"
Exit Sub
End If
' ִ�в�ѯ
db.Sql("SELECT * FROM users").Query
End Sub�ۺ�ʾ��
ʾ�� 1�����ݿ���Ϣ�鿴��
vb
Sub ShowDatabaseInfo()
' ��ʾ���ݿ�汾
Debug.Print "���ݿ�汾: " & db.GetVersion
' ��ʾ���ݿ��б�
Dim colDatabases As Collection
Set colDatabases = db.GetDatabases()
Debug.Print "���ݿ��б�:"
Dim i As Long
For i = 1 To colDatabases.Count
Debug.Print " - " & colDatabases(i)
Next
' ��ʾ���б�
Dim colTables As Collection
Set colTables = db.GetTables()
Debug.Print "���б�:"
For i = 1 To colTables.Count
Debug.Print " - " & colTables(i)
' ��ʾÿ�������ֶ�
Dim colFields As Collection
Set colFields = db.GetTableFields(colTables(i))
Dim j As Long
For j = 1 To colFields.Count
Debug.Print " * " & colFields(j)
Next
Next
End Subʾ�� 2�����ṹ�Ƚ�
vb
Function CompareTableStructure(sTable1 As String, sTable2 As String) As Boolean
Dim colFields1 As Collection
Dim colFields2 As Collection
Set colFields1 = db.GetTableFields(sTable1)
Set colFields2 = db.GetTableFields(sTable2)
' �Ƚ��ֶ�����
If colFields1.Count <> colFields2.Count Then
CompareTableStructure = False
Exit Function
End If
' �Ƚ��ֶ���
Dim i As Long
For i = 1 To colFields1.Count
If colFields1(i) <> colFields2(i) Then
CompareTableStructure = False
Exit Function
End If
Next
CompareTableStructure = True
End Function������: 2026-01-21