use GetOleDbSchemaTable to get the columns of a table named "Street"

Question

I'm trying to read the column names of a table "Streets" in an Access database by opening an OleDbConnection. I call GetOleDbSchemaTable but I can't seem to figure out how to get at my columns.

I'd like to use .NET 3.5 framework if possible.

score 12 · Accepted Answer · edited Jun 05 '12 at 18:17

12

using (OleDbConnection connection = new OleDbConnection(connectionString))
{
    connection.Open();

    DataTable tableColumns = connection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, "Streets", null }));
    foreach (DataRow row in tableColumns.Rows)
    {
        var columnNameColumn = row["COLUMN_NAME"];
        var dateTypeColumn = row["DATA_TYPE"];
        var ordinalPositionColumn = row["ORDINAL_POSITION"];
        ...
    }
}

edited Jun 05 '12 at 18:17

Tom Mayfield

6,235
2
32
43

answered Oct 13 '10 at 14:32

vc 74

37,131
7
73
89

@vc 74 That doesn't work for me, I get "DataTable does not have public def of IEnum" so can't even use the foreach, then when I put it in a for {} it says COLUMN_NAME is not a member of TABLE "Tables" – patrick Oct 13 '10 at 14:49

score 0 · Answer 2 · answered Oct 13 '10 at 15:05

This works for me, puts column names of table street in a list

OleDbCommand mdbCommand = new OleDbCommand("SELECT * FROM Streets", mdbConnection);
OleDbDataReader myReader = mdbCommand.ExecuteReader(CommandBehavior.KeyInfo);
DataTable  schemaTable = myReader.GetSchemaTable();
List<String> columnNameList = new List<string>();
foreach (DataRow myField in schemaTable.Rows)
{
foreach (DataColumn myProperty in schemaTable.Columns)
{
if (myProperty.ColumnName == "ColumnName") columnNameList.Add(myField[myProperty].ToString());
}

}

Yes, but you are opening a query that can potentially take time to execute (longer than a metadata query), and you will probably not get all the info you can get from GetOleDbSchemaTable — vc 74, Oct 13 '10 at 15:08

use GetOleDbSchemaTable to get the columns of a table named "Street"

2 Answers2

Linked