Skip to content

���߷���

���ĵ���ϸ���� cDataBase ���ṩ�ĸ��ֹ��߷��������ڼ򻯳������ݿ������


? Ŀ¼


Count ����

�﷨

ͳ�Ʊ��еļ�¼����

vb
Function Count(Optional ByVal TableName As String = "") As Long

����˵��

��������˵��
TableNameString��������ѡ��Ϊ��ʱʹ�õ�ǰ 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 T

LastInsertId ����

�﷨

��ȡ��������������� ID��

vb
Function LastInsertId() As Variant

����ֵ

����������� ID��Variant ���ͣ���

֧�ֵ����ݿ�

���ݿ�ʵ�ַ�ʽ
SQL ServerSCOPE_IDENTITY()
MySQLLAST_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

ע������

  1. ������ 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
  2. ��֧����������

    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

����˵��

��������˵��
TableNameString���������裩

����ֵ

  • True - ������
  • False - ��������

֧�ֵ����ݿ�

���ݿ�ʵ�ַ�ʽ
SQL ServerINFORMATION_SCHEMA.TABLES
MySQLINFORMATION_SCHEMA.TABLES
AccessMSysObjects

ʾ��

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 If

GetTableFields ����

�﷨

��ȡ�����ֶ��б���

vb
Function GetTableFields(ByVal TableName As String) As Collection

����˵��

��������˵��
TableNameString���������裩

����ֵ

�����ֶ������ϣ�Collection ���ͣ���

֧�ֵ����ݿ�

���ݿ�ʵ�ַ�ʽ
SQL ServerINFORMATION_SCHEMA.COLUMNS
MySQLINFORMATION_SCHEMA.COLUMNS
AccessMSysObjects

ʾ��

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 Function

GetTables ����

�﷨

��ȡ���ݿ��еı����б���

vb
Function GetTables(Optional ByVal DatabaseName As String = "") As Collection

����˵��

��������˵��
DatabaseNameString���ݿ�������ѡ��Ϊ��ʱʹ�õ�ǰ���ݿ⣩

����ֵ

���ر������ϣ�Collection ���ͣ���

֧�ֵ����ݿ�

���ݿ�ʵ�ַ�ʽ
SQL ServerINFORMATION_SCHEMA.TABLES
MySQLINFORMATION_SCHEMA.TABLES
AccessMSysObjects

ʾ��

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 Sub

GetDatabases ����

�﷨

��ȡ���ݿ�������е����ݿ��б���

vb
Function GetDatabases() As Collection

����ֵ

�������ݿ������ϣ�Collection ���ͣ���

֧�ֵ����ݿ�

���ݿ�ʵ�ַ�ʽ
SQL Serversys.databases
MySQLSHOW 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 Sub

GetVersion ����

�﷨

��ȡ���ݿ�汾��Ϣ��

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 Function

Escape ����

�﷨

ת�� SQL �ַ����е������ַ�����ֹ SQL ע�룩��

vb
Function Escape(ByVal Str As String) As String

����˵��

��������˵��
StrStringҪת����ַ��������裩

����ֵ

����ת�����ַ�����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") & "'").Query

CheckConnection ����

�﷨

�������״̬������������

vb
Function CheckConnection() As Boolean

����ֵ

  • True - ���������������ɹ�
  • False - ���ӶϿ�������ʧ��

����

  1. �������״̬
  2. ����Ͽ���������������
  3. �������ӱ��

ʾ��

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

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