Database Metadata // OleDB

[sloan]

Back in ADO days, it was fairly easy to get the meta data (tablenames,
columnnames , etc) .. with all that schema stuff.

I have a Access database, that I'd like to get the list of tableNames in it.

How does one get the metadata about a database thru DotNet?

Thanks.

[Steve Barnett]

Dloan
This was part of my first ever .Net app, so it's probably not the best
or most optimised code, but the following returns you a list of tables...
DBDef is a strongly typed dataset, but I'm sure you can get round that. The
important call is the call to "dBase.GetOleDbSchemaTable" which is where
the table list comes from.

public DBDef ProcessDatabase()
{
// Datatable to receive the list of tables names from the database
DataTable schemaTable= new DataTable("TableNames");
DBDef.TableNamesRow newTableName;

// Setup a connection to the database
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + databaseFilename + ";Jet OLEDB:Engine
Type=4;";
OleDbConnection dBase = new OleDbConnection(strConnection);
// Start by getting a list of tables in to the data table/dataset
try
{
// Open the connection to the database
dBase.Open();

// Retrieve the list of data tables in the database, excluding system
tables
schemaTable = dBase.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
Object[] {null, null, null,
"TABLE"});
for (int i=0; i < schemaTable.Rows.Count; i++)
{
string tableName = schemaTable.Rows[i].ItemArray[2].ToString();

// For each table in the database, retrieve the column information
ProcessTable(dBase, tableName);

newTableName = dbStruct.TableNames.NewTableNamesRow();
newTableName.dbTableName =
schemaTable.Rows[i].ItemArray[2].ToString();
dbStruct.TableNames.AddTableNamesRow(newTableName) ;

ProcessIndexes(dBase, tableName);
}
}
finally
{
dBase.Close();
}

return dbStruct;
}

To get information on specific columns ina table, I use code like this:

// Create the datareader, retrieving only the schema information
dataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
// Copy the stuff we retrieved in to a data table
schemaTable = dataReader.GetSchemaTable();
// And give the data table a name that we can work with.
schemaTable.TableName = "ColumnNames";
// We don't need the data reader any more - we have what we want
dataReader.Close();

Where "cmd" is an OleDbCommand object with a simple "SELECT * FROM
tablename" statement in it. Again, the main work is done with the
GetSchemaTable call.

HTH
Steve
"sloan" <sl***@ipass.net> wrote in message
news:uM**************@TK2MSFTNGP04.phx.gbl...

Back in ADO days, it was fairly easy to get the meta data (tablenames,
columnnames , etc) .. with all that schema stuff.

I have a Access database, that I'd like to get the list of tableNames in
it.

How does one get the metadata about a database thru DotNet?

Thanks.

[sloan]

Steve,

Thanks.... I appreciate it.

That'll definately save me alot of time.


"Steve Barnett" <no****@nodomain.com> wrote in message
news:uH**************@TK2MSFTNGP03.phx.gbl...

Dloan
This was part of my first ever .Net app, so it's probably not the best
or most optimised code, but the following returns you a list of tables...
DBDef is a strongly typed dataset, but I'm sure you can get round that. The important call is the call to "dBase.GetOleDbSchemaTable" which is where
the table list comes from.

public DBDef ProcessDatabase()
{
// Datatable to receive the list of tables names from the database
DataTable schemaTable= new DataTable("TableNames");
DBDef.TableNamesRow newTableName;

// Setup a connection to the database
string strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" + databaseFilename + ";Jet OLEDB:Engine
Type=4;";
OleDbConnection dBase = new OleDbConnection(strConnection);
// Start by getting a list of tables in to the data table/dataset
try
{
// Open the connection to the database
dBase.Open();

// Retrieve the list of data tables in the database, excluding system
tables
schemaTable = dBase.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new
Object[] {null, null, null,
"TABLE"});
for (int i=0; i < schemaTable.Rows.Count; i++)
{
string tableName = schemaTable.Rows[i].ItemArray[2].ToString();

// For each table in the database, retrieve the column information ProcessTable(dBase, tableName);

newTableName = dbStruct.TableNames.NewTableNamesRow();
newTableName.dbTableName =
schemaTable.Rows[i].ItemArray[2].ToString();
dbStruct.TableNames.AddTableNamesRow(newTableName) ;

ProcessIndexes(dBase, tableName);
}
}
finally
{
dBase.Close();
}

return dbStruct;
}

To get information on specific columns ina table, I use code like this:

// Create the datareader, retrieving only the schema information
dataReader = cmd.ExecuteReader(CommandBehavior.KeyInfo);
// Copy the stuff we retrieved in to a data table
schemaTable = dataReader.GetSchemaTable();
// And give the data table a name that we can work with.
schemaTable.TableName = "ColumnNames";
// We don't need the data reader any more - we have what we want
dataReader.Close();

Where "cmd" is an OleDbCommand object with a simple "SELECT * FROM
tablename" statement in it. Again, the main work is done with the
GetSchemaTable call.

HTH
Steve
"sloan" <sl***@ipass.net> wrote in message
news:uM**************@TK2MSFTNGP04.phx.gbl...

Back in ADO days, it was fairly easy to get the meta data (tablenames,
columnnames , etc) .. with all that schema stuff.

I have a Access database, that I'd like to get the list of tableNames in
it.

How does one get the metadata about a database thru DotNet?

Thanks.