如何检索实际的OleDb表架构(不包括其他表列) [英] How to retrieve actual OleDb table schema (excluding additional table columns)

问题描述

运行此代码时，它还会检索表中不存在的其他一些字段.我该如何克服?

When I run this code it is also retrieving some other fields which are not present in the table. How can I overcome this?

Dim conn As New OleDb.OleDbConnection
'Create a connection string for an Access database
Dim strConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\check\a.mdb"
'Attach the connection string to the connection object
conn.ConnectionString = strConnectionString
'Open the connection
conn.Open()
Dim Restrictions() As String = {Nothing, Nothing, selected, Nothing}
Dim CollectionName As String = "Columns"
Dim dt As DataTable = conn.GetSchema(CollectionName, Restrictions)
For Each TableRow As DataRow In dt.Rows
    ComboBox1.Items.Add(TableRow.Item("COLUMN_NAME"))

还检索到的其他列是:

1.ID
2.日期创建
3.日期更新
4.Id
5.Lv
6.名称
7.parentId
8，类型
9.GUID
10.Id

1.ID
2.Date create
3.Date update
4.Id
5.Lv
6.Name
7.parent Id
8.Type
9.GUID
10.Id

...和另外6个.原始架构仅包含5个字段.

... and 6 more. The original schema consists of only 5 fields.

推荐答案

您的问题很简单，当将变量selected插入到Restrictions()数组中时，其值就是Nothing.当我运行以下代码时，我仅获得名为[new]的表的列名:

Your problem is simply that the variable selected has the value Nothing when you insert it into the Restrictions() array. When I run the following code I only get the column names for the table named [new]:

Option Strict On

Imports System.Data.OleDb

Module Module1

    Sub Main()
        Dim connStr As String =
                "Provider=Microsoft.ACE.OLEDB.12.0;" &
                "Data Source=C:\Users\Public\test\so34490626\a.mdb"
        Using conn As New OleDbConnection(connStr)
            conn.Open()
            Dim selected As String = "new"
            Dim Restrictions() As String = {Nothing, Nothing, selected, Nothing}
            Dim CollectionName As String = "Columns"
            Dim dt As DataTable = conn.GetSchema(CollectionName, Restrictions)
            For Each TableRow As DataRow In dt.Rows
                Console.WriteLine(TableRow.Item("COLUMN_NAME"))
            Next
        End Using
    End Sub

End Module

结果是:

GENDER
MEMBER OF RISHI PRASAD
NAME OF SADHAK
PROFESSION

但是，当selected的值为Nothing ...

However, when the value of selected is Nothing ...

Dim selected As String = Nothing

...我得到您描述的额外"列

... I get the "extra" columns you describe

DateCreate
DateUpdate
Id
Lv
Name
ParentId
Type
Attributes
DataType
FieldName
IndexType
SkipColumn
SpecID
Start
Width
DateDelim
DateFourDigitYear
DateLeadingZeros
DateOrder
DecimalPoint
FieldSeparator
FileType
SpecID
SpecName
SpecType
StartRow
TextDelim
TimeDelim
GENDER
MEMBER OF RISHI PRASAD
NAME OF SADHAK
PROFESSION

如果我将Console.WriteLine更改为包括表名，就会发现原因:

The reason is revealed if I change the Console.WriteLine to include the table names:

Console.WriteLine("[{0}].[{1}]", TableRow.Item("TABLE_NAME"), TableRow.Item("COLUMN_NAME"))

然后我们看到:

[MSysAccessStorage].[DateCreate]
[MSysAccessStorage].[DateUpdate]
[MSysAccessStorage].[Id]
[MSysAccessStorage].[Lv]
[MSysAccessStorage].[Name]
[MSysAccessStorage].[ParentId]
[MSysAccessStorage].[Type]
[MSysIMEXColumns].[Attributes]
[MSysIMEXColumns].[DataType]
[MSysIMEXColumns].[FieldName]
[MSysIMEXColumns].[IndexType]
[MSysIMEXColumns].[SkipColumn]
[MSysIMEXColumns].[SpecID]
[MSysIMEXColumns].[Start]
[MSysIMEXColumns].[Width]
[MSysIMEXSpecs].[DateDelim]
[MSysIMEXSpecs].[DateFourDigitYear]
[MSysIMEXSpecs].[DateLeadingZeros]
[MSysIMEXSpecs].[DateOrder]
[MSysIMEXSpecs].[DecimalPoint]
[MSysIMEXSpecs].[FieldSeparator]
[MSysIMEXSpecs].[FileType]
[MSysIMEXSpecs].[SpecID]
[MSysIMEXSpecs].[SpecName]
[MSysIMEXSpecs].[SpecType]
[MSysIMEXSpecs].[StartRow]
[MSysIMEXSpecs].[TextDelim]
[MSysIMEXSpecs].[TimeDelim]
[new].[GENDER]
[new].[MEMBER OF RISHI PRASAD]
[new].[NAME OF SADHAK]
[new].[PROFESSION]

"MSys *"表是默认情况下在Access用户界面中隐藏的系统表.

The "MSys*" tables are system tables that are hidden by default in the Access user interface.

这篇关于如何检索实际的OleDb表架构(不包括其他表列)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！