Retrieving Database Metadata using C# and .NET

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: [e-mail] /// /// 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>