RSS Feed
Download our iPhone app
Browse DevX
Sign up for e-mail newsletters from DevX


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.

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.

Table 1. The Overloads of the DbConnection.GetSchema Method

Method Overload Description
GetSchema() Returns a DataTable containing a row for each of the metadata collections that are available from the database. Equivalent to specifying the String value "MetaDataCollections" when using the GetSchema(String) overload.
GetSchema(String) Takes the name of a metadata collection and returns a DataTable containing a row for each item found in that metadata collection in the database.
GetSchema(String, String-Array) Takes the name of a metadata collection and an array of String values that specify the restrictions on which the rows in the returned DataTable will be filtered. Used to select and return a DataTable containing only the rows in the metadata collection specified in the first parameter that match the restrictions specified in the second parameter.

Author's Note: The code described in this article is based on the Beta 1 release of the .NET Framework v 2.0. All the examples can be downloaded from http://www.daveandal.net/articles/schema-api/, and you can also run many of them online from the same location.

Close Icon
Thanks for your registration, follow us on our social networks to keep up-to-date