Make your own free website on Tripod.com

ADO.NET
Retrieving Database Metadata using C# and .NET

Gopalan Suresh Raj

Note
To work with any of these samples, you will need the following:
.........................................Microsoft .NET SDK
.........................................Microsoft Visual Studio.NET Beta 2 or higher

 

Sometimes, in addition to querying and updating data in a database, you also need to retrieve information about the database itself and its contents. This information is called Database Metadata. The OleDbConnection Class allows you to retrieve this kind of information. It's GetOleDbSchemaTable() method can be used to retrieve any information about the database and its metadata.

1. Develop the DatabaseInfo.cs Application

After a database connection is opened in the DatabaseInfo's constructor, metadata information is obtained from the database that lists the table metadata and the column metadata information.

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:
32:
33:
34:
35:
36:
37:
38:
39:
40:
41:
42:
43:
44:
45:
46:
47:
48:
49:
50:
51:
52:
53:
54:
55:
56:
57:
58:
59:
60:
61:
62:
63:
64:
65:
66:
67:
68:
69:
70:
71:
72:
73:
74:
75:
76:
77:
78:
79:
80:
81:
82:
83:
84:
85:
86:
87:
88:
89:
90:
91:
92:
93:
94:
95:
96:
97:
98:
///////////////////////////////////////////////////////////
/// The following example shows querying database Metadata
/// Information developed using C# and the .NET Framework.
///
/// author: Gopalan Suresh Raj
/// Copyright (c), 2002. All Rights Reserved.
/// URL: http://gsraj.tripod.com/
/// email: gopalan@gmx.net
///
///////////////////////////////////////////////////////////
using System;
using System.Data;
using System.Data.OleDb;

/// <summary>
/// Summary description for DatabaseInfo.
/// </summary>
class DatabaseInfo {

  /// <summary>
  /// The Connection String
  /// </summary>
  static readonly string CONNECTION_STRING = "Provider=Microsoft.Jet.OLEDB.4.0;"+
    "Data Source=C:/Program Files/Microsoft Office/Office10/Samples/Northwind.mdb;";

  /// <summary>
  /// The Connection Object
  /// </summary>
  OleDbConnection connection_;

  /// <summary>
  /// Default No-Argument constructor that
  /// Creates a connection to the database
  /// </summary>
  DatabaseInfo() {
    this.connection_ = new OleDbConnection( DatabaseInfo.CONNECTION_STRING );
    this.connection_.Open();
  }

  /// <summary>
  /// Closes the Connection to the Database
  /// </summary>
  void Dispose() {
    this.connection_.Close();
  }

  /// <summary>
  /// Retrieves Database Metadata information about Tables
  /// of the specific database exposed to this user
  /// </summary>
  public void RetrieveTableInformation() {

    DataTable tables = this.connection_.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
    Console.WriteLine("\nListing Table Metadata Information ...");
    foreach( DataColumn column in tables.Columns ) {
      Console.WriteLine(column);
    }
    Console.WriteLine("\nListing Tables ...");
    foreach( DataRow row in tables.Rows ) {
      Console.WriteLine(row["TABLE_NAME"]);
    }
  }

  /// <summary>
  /// Retrieves Database Metadata information about Columns
  /// of the specific database exposed to this user
  /// </summary>
  public void RetrieveColumnInformation() {
    DataTable tables = this.connection_.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, null);
    // Print out the columns
    Console.WriteLine("\nListing Column Metadata Information ...");
    foreach( DataColumn column in tables.Columns ) {
      Console.WriteLine(column);
    }
    Console.WriteLine("\nListing Columns (TableName : ColumnName format)...");
    foreach( DataRow row in tables.Rows ) {
      Console.WriteLine(row["TABLE_NAME"]+" : "+ row["COLUMN_NAME"]);
    }
  }

  /// <summary>
  /// Default Entry Point that tests the system
  /// </summary>
  /// <param name="args"></param>
  static void Main(string[] args) {
    try {
      DatabaseInfo info = new DatabaseInfo();
      info.RetrieveTableInformation();
      info.RetrieveColumnInformation();
      info.Dispose();
    }
    catch( OleDbException exception ) {
      foreach(OleDbError error in exception.Errors) {
        Console.WriteLine("Error :"+error);
      }
    }
  }
}

 

2. Build and Run the Application

Build the files that make up the App and run it .

Command Prompt
C:\MyProjects\Cornucopia\DatabaseMetaData\bin\Debug>DatabaseMetaData

Listing Table Metadata Information ...
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
TABLE_TYPE
TABLE_GUID
DESCRIPTION
TABLE_PROPID
DATE_CREATED
DATE_MODIFIED

Listing Tables ...
Alphabetical List of Products
Categories
Category Sales for 1997
Current Product List
Customers
Employees
Invoices
MSysAccessObjects
MSysACEs
MSysCmdbars
MSysIMEXColumns
MSysIMEXSpecs
MSysObjects
MSysQueries
MSysRelationships
Order Details
Order Details Extended
Order Subtotals
Orders
Orders Qry
Product Sales for 1997
Products
Products Above Average Price
Products by Category
Quarterly Orders
Sales by Category
Shippers
Suppliers
Ten Most Expensive Products

Listing Column Metadata Information ...
TABLE_CATALOG
TABLE_SCHEMA
TABLE_NAME
COLUMN_NAME
COLUMN_GUID
COLUMN_PROPID
ORDINAL_POSITION
COLUMN_HASDEFAULT
COLUMN_DEFAULT
COLUMN_FLAGS
IS_NULLABLE
DATA_TYPE
TYPE_GUID
CHARACTER_MAXIMUM_LENGTH
CHARACTER_OCTET_LENGTH
NUMERIC_PRECISION
NUMERIC_SCALE
DATETIME_PRECISION
CHARACTER_SET_CATALOG
CHARACTER_SET_SCHEMA
CHARACTER_SET_NAME
COLLATION_CATALOG
COLLATION_SCHEMA
COLLATION_NAME
DOMAIN_CATALOG
DOMAIN_SCHEMA
DOMAIN_NAME
DESCRIPTION

Listing Columns (TableName : ColumnName format)...
Alphabetical List of Products : CategoryID
Alphabetical List of Products : CategoryName
Alphabetical List of Products : Discontinued
Alphabetical List of Products : ProductID
Alphabetical List of Products : ProductName
Alphabetical List of Products : QuantityPerUnit
Alphabetical List of Products : ReorderLevel
Alphabetical List of Products : SupplierID
Alphabetical List of Products : UnitPrice
Alphabetical List of Products : UnitsInStock
Alphabetical List of Products : UnitsOnOrder
Categories : CategoryID
Categories : CategoryName
Categories : Description
Categories : Picture
Category Sales for 1997 : CategoryName
Category Sales for 1997 : CategorySales
Current Product List : ProductID
Current Product List : ProductName
Customers : Address
Customers : City
Customers : CompanyName
Customers : ContactName
Customers : ContactTitle
Customers : Country
Customers : CustomerID
Customers : Fax
Customers : Phone
Customers : PostalCode
Customers : Region
Employees : Address
Employees : BirthDate
Employees : City
Employees : Country
Employees : EmployeeID
Employees : Extension
Employees : FirstName
Employees : HireDate
Employees : HomePhone
Employees : LastName
Employees : Notes
Employees : Photo
Employees : PostalCode
Employees : Region
Employees : ReportsTo
Employees : Title
Employees : TitleOfCourtesy
Invoices : Address
Invoices : City
Invoices : Country
Invoices : CustomerID
Invoices : Customers.CompanyName
Invoices : Discount
Invoices : ExtendedPrice
Invoices : Freight
Invoices : OrderDate
Invoices : OrderID
Invoices : PostalCode
Invoices : ProductID
Invoices : ProductName
Invoices : Quantity
Invoices : Region
Invoices : RequiredDate
Invoices : Salesperson
Invoices : ShipAddress
Invoices : ShipCity
Invoices : ShipCountry
Invoices : ShipName
Invoices : ShippedDate
Invoices : Shippers.CompanyName
Invoices : ShipPostalCode
Invoices : ShipRegion
Invoices : UnitPrice
MSysAccessObjects : Data
MSysAccessObjects : ID
MSysCmdbars : Grptbcd
MSysCmdbars : TbName
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
MSysRelationships : ccolumn
MSysRelationships : grbit
MSysRelationships : icolumn
MSysRelationships : szColumn
MSysRelationships : szObject
MSysRelationships : szReferencedColumn
MSysRelationships : szReferencedObject
MSysRelationships : szRelationship
Order Details : Discount
Order Details : OrderID
Order Details : ProductID
Order Details : Quantity
Order Details : UnitPrice
Order Details Extended : Discount
Order Details Extended : ExtendedPrice
Order Details Extended : OrderID
Order Details Extended : ProductID
Order Details Extended : ProductName
Order Details Extended : Quantity
Order Details Extended : UnitPrice
Order Subtotals : OrderID
Order Subtotals : Subtotal
Orders : CustomerID
Orders : EmployeeID
Orders : Freight
Orders : OrderDate
Orders : OrderID
Orders : RequiredDate
Orders : ShipAddress
Orders : ShipCity
Orders : ShipCountry
Orders : ShipName
Orders : ShippedDate
Orders : ShipPostalCode
Orders : ShipRegion
Orders : ShipVia
Orders Qry : Address
Orders Qry : City
Orders Qry : CompanyName
Orders Qry : Country
Orders Qry : CustomerID
Orders Qry : EmployeeID
Orders Qry : Freight
Orders Qry : OrderDate
Orders Qry : OrderID
Orders Qry : PostalCode
Orders Qry : Region
Orders Qry : RequiredDate
Orders Qry : ShipAddress
Orders Qry : ShipCity
Orders Qry : ShipCountry
Orders Qry : ShipName
Orders Qry : ShippedDate
Orders Qry : ShipPostalCode
Orders Qry : ShipRegion
Orders Qry : ShipVia
Product Sales for 1997 : CategoryName
Product Sales for 1997 : ProductName
Product Sales for 1997 : ProductSales
Product Sales for 1997 : ShippedQuarter
Products : CategoryID
Products : Discontinued
Products : ProductID
Products : ProductName
Products : QuantityPerUnit
Products : ReorderLevel
Products : SupplierID
Products : UnitPrice
Products : UnitsInStock
Products : UnitsOnOrder
Products Above Average Price : ProductName
Products Above Average Price : UnitPrice
Products by Category : CategoryName
Products by Category : Discontinued
Products by Category : ProductName
Products by Category : QuantityPerUnit
Products by Category : UnitsInStock
Quarterly Orders : City
Quarterly Orders : CompanyName
Quarterly Orders : Country
Quarterly Orders : CustomerID
Sales by Category : CategoryID
Sales by Category : CategoryName
Sales by Category : ProductName
Sales by Category : ProductSales
Shippers : CompanyName
Shippers : Phone
Shippers : ShipperID
Suppliers : Address
Suppliers : City
Suppliers : CompanyName
Suppliers : ContactName
Suppliers : ContactTitle
Suppliers : Country
Suppliers : Fax
Suppliers : HomePage
Suppliers : Phone
Suppliers : PostalCode
Suppliers : Region
Suppliers : SupplierID
Ten Most Expensive Products : TenMostExpensiveProducts
Ten Most Expensive Products : UnitPrice

C:\MyProjects\Cornucopia\DatabaseMetaData\bin\Debug>

 

 

click here to go to
My Advanced C#/.NET Tutorial Page...

About the Author...
Gopalan Suresh Raj is a Software Architect, Developer and an active Author. He has co-authored a number of books including "Professional JMS", "Enterprise Java Computing-Applications and Architecture" and "The Awesome Power of JavaBeans". His expertise spans enterprise component architectures and distributed object computing. Visit him at his Web Cornucopia site (http://gsraj.tripod.com/) or mail him at gopalan@gmx.net.

 


Go to the Component Engineering Cornucopia page

This site was developed and is maintained by Gopalan Suresh Raj

This page has been visited times since February 13, 2002.

Last Updated : Feb 13, '02

If you have any questions, comments, or problems regarding this site, please write to me I would love to hear from you.


Copyright (c) 1997-2002, Gopalan Suresh Raj - All rights reserved. Terms of use.

All products and companies mentioned at this site are trademarks of their respective owners.