我有一個 MS 訪問資料庫，其中有 4 個表。我想使用 vb.Net 計算所有表的所有記錄。是否有任何 oledbcommand 命令來統計資料庫中所有表中的所有記錄？
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()
uj5u.com熱心網友回復：
我創建了 2 個函式。第一個通過呼叫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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/419080.html
標籤：