What's In Your Database? Find Out with the Schema API in ADO.NET 2.0

ost data access technologies provide a way for you to query the schema of a database, and obtain information about the tables, stored procedures, data types, users, and other content of a database. However, the technique varies depending on the data provider or database driver you are using and is not always straightforward to implement. In ADO.NET 2.0, Microsoft has added a set of features to the standard managed code classes that you can use as an API to query database schemas. This article looks at how you can use these features in your applications. It covers:

Reading a list of metadata collections from SQL Server
Querying other types of databases
Exploring the various metadata collections
Programming with the Schema API
Understanding metadata collection restrictions
Creating and using a restriction array

The GetSchema Method
Querying the schema of a database involves use of a single method that is added to the Connection class in ADO.NET 2.0. This method, named GetSchema, uses the current connection to retrieve information about the database to which that connection points. To query across databases, you have to create a separate connection to each one and then use these connections to query each database individually.

The GetSchema method is implemented within the abstract base class DbConnection (in the System.Data.Common namespace) and is available in all the provider-specific connection classes such as SqlConnection, OleDbConnection, OracleConnection, and OdbcConnection. There are three overloads of the GetSchema method; the syntax of each is shown in Table 1.

It's quick, easy and you get access to all the articles on DevX.
This registration/login is to allow you to read articles on devx.com.
Already a member?

To become a member of DevX.com create your Member Profile by completing the form below.
Membership is free!

Already registered? Sign In

E-mail (User Name)

Create a Password

Re-enter Password

First Name

Last Name

Company

Company Size

Address

City

State/Province

Country

Postal Code/Zip

Job Function

Phone

Note: Extension number is optional, but may be included by adding an "x" before the extension number.

By registering you will also be signed up for the Enterprise Development Daily newsletter.
After registering you may choose to opt-out by clicking on the "Manage Profile" link.

Cancel

What's In Your Database? Find Out with the Schema API in ADO.NET 2.0

ADO.NET 2.0 includes an extremely useful set of providers that you can use dozens of ways to quickly and accurately query your SQL Server or JET database. This article shows you how to be the pied piper of your metadata using the Schema API.