Tip

Getting database schema using ADO.NET

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

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.