Getting database schema using ADO.NET

ADO.NET provides a rich infrastructure to get the database schema information. Learn how to retrieve it.

Let other users know how useful this tip is by rating it below. Got a tip or code of your own you'd like to share? Submit it here!


A database schema describes the organisation structure of the database; schema broadly consists of the structure of tables, stores procedures and the views of the database. ADO.NET provides a rich infrastructure to get the database schema information. The OLEDBConnection object exposes a GetOLEDBSchemaTable function that is used to retrieve the schema information from the currosponding database.

The GetOLEDBSchemaTable function accepts two parameters:

 

  • OLEDBSchemaGUID
  • Restrictions

    The OLEDBSchemaGUID members are used for the schema tables; the members consist of tables, procedures, views, columns, catalogs, etc. The intellisense will give a list of all the members of OLEDBSchemaGUID.

    The restrictions is an object array used to filter the schema result. Each maps to the values of the datacolumn returned. The example below will make it clear.

    Open a new windows application in VS.NET. This is pretty simple code and can be easily mapped to C# or VB.NET.

    Add a datagrid > datagrid1

    Code to get a list of all the tables from the database schema:

     //Open a connection object OleDbConnection con = new OleDbConnection ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/Test.mdb;Persist Security Info=False"); con.Open(); //Declare a Datatable object System.Data.DataTable dt ; //Query for getting the list of all tables from the schema, no restriction dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); //display the data in a datagrid dataGrid1.DataSource = dt;
    

    The above code will return a list of all tables, system as well as user tables. We will now see how we can apply a restriction for only returning the user tables. Notice that the fourth column is "TABLE_TYPE." All the user tables will have a value "TABLE," so this is our filtering criteria. We will pass an object array that will have the filtering/restriction criteria.

    Code to get a list of all user tables by applying restriction:

     //Query for getting only the user tables dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[]{null,null,null,"TABLE"} ); //display the data in a datagrid dataGrid1.DataSource = dt; //Query for getting stored prodedures dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, null ); //display the data in a datagrid dataGrid1.DataSource = dt;
    

    Code to get a list of all primary keys from the database:

     //Query for getting all primary keys OleDbSchemaGuid.dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null ); //display the data in a datagrid dataGrid1.DataSource = dt;
    

    Code to get the all supported provider data types:

     //Query for getting all supported types dt = con.GetOleDbSchemaTable(OleDbSchemaGuid.Provider_Types, null ); //display the data in a datagrid dataGrid1.DataSource = dt;
    

    Check out the OLEDBSchemaGUID members to get a list of supported schema members. Happy coding.

     


    Source: DotNetExtreme.com

This was first published in March 2003

Dig deeper on ADO.NET development

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchCloudComputing

SearchSoftwareQuality

SearchSOA

TheServerSide

SearchCloudApplications

Close