在vb.Net中统计MS Access数据库中所有表中的所有记录 [英] count all records in all tables in MS access database in vb.Net

问题描述

我在MS Access中有一个数据库，其中有4个表。我想用vb.Net计算所有表的所有记录。是否有任何oledb命令命令来计算数据库中所有表中的所有记录？

    con1.Open()
    Dim Restrictions() As String = {Nothing, Nothing, "Table1", Nothing}
    Dim CollectionName As String = "Columns"
    Dim dt As DataTable = con1.GetSchema(CollectionName, Restrictions)
    For Each TableRow As DataRow In dt.Rows
        Console.WriteLine(TableRow.Item("COLUMN_NAME").ToString)
    Next
    con1.Close()

推荐答案

我已经创建了两个函数。第一个通过在连接上调用GetSchema来获取表名。我通过检查名称是否以MS开头来排除系统表。我还通过仅添加TABLE_TYPE=TABLE排除了视图。

第二个函数获取表名列表并获取每个表中的计数。请注意表名称两边的方括号，以防该名称包含空格。

Private cs As String = My.Settings.AccessAddressConnection

Private Function GetTableNames() As List(Of String)
    Dim TableNames As New List(Of String)
    Dim dt As New DataTable
    Using cn As New OleDbConnection(cs)
        cn.Open()
        dt = cn.GetSchema("Tables")
    End Using
    For Each row As DataRow In dt.Rows
        If Not row("TABLE_NAME").ToString.StartsWith("MS") AndAlso row("TABLE_TYPE").ToString = "TABLE" Then
            TableNames.Add(row("TABLE_NAME").ToString)
        End If
    Next
    Return TableNames
End Function

Private Function GetTotalRecords(lst As List(Of String)) As Integer
    Dim counts As Integer
    Using cn As New OleDbConnection(cs),
            cmd As New OleDbCommand()
        cmd.Connection = cn
        cn.Open()
        For Each TName In lst
            cmd.CommandText = $"Select Count(*) From [{TName}];"
            counts += (CInt(cmd.ExecuteScalar))
        Next
    End Using
    Return counts
End Function

Private Sub Button1_Click() Handles Button1.Click
    Dim lst = GetTableNames()
    Dim Total = GetTotalRecords(lst)
    MessageBox.Show(Total.ToString)
End Sub

这篇关于在vb.Net中统计MS Access数据库中所有表中的所有记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！