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 : Page 3

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.




Building the Right Environment to Support AI, Machine Learning and Deep Learning

Exploring the Various Metadata Collections
Irrespective of which provider you use and which type of database you query using the Schema API, there are some common factors that you can rely on. These make it easier to write code that will work (and adapt, if required, to the specific database type and provider). Every provider and database combination should expose collections that allow code to find out about the built-in objects and the database engine itself, including:
  • A collection named MetaDataCollections that contains a list of all the available collections, and the number of restrictions that apply to each collection. If in doubt, you can use the GetSchema method with no parameters to get the default collection, which should be the MetaDataCollections collection.
  • A collection named DataSourceInformation, containing a single row that provides the name, version, and other details about the database. These details include the separation character for multi-part object identifiers (such as Northwind.dbo.Orders), the legal characters, and the format for string literals, parameters, identifiers, etc.
  • A collection named DataTypes that lists all the supported data types and provides details about them such as the size, whether they are incrementable, case-sensitive, nullable, etc.
  • A collection named ReservedWords that lists all the words that are reserved for use by the database and code that runs within it (including in stored procedures and SQL statements).
  • A collection named Restrictions that lists the restrictions you can apply to filter the results when querying any of the metadata collections. We look at the use of restrictions in detail later in this article.

Most of the remaining collections allow code to enumerate the common objects that make up the contents of the database, such as tables, columns, indexes, stored procedures, etc. Notice that some providers expose a Tables and a Views metadata collection, while others (OLE-DB in particular) just expose a Tables collection. In general, with most of the providers, the Tables collection includes Views that are declared within the database, and these are identified by a value in one of the columns in the DataTable that is returned by the GetSchema method.

Figure 5. Picking Your Collection: You need to select a metadata collection in the Database Explorer example page.
Some providers also allow you to enumerate the databases on the server, where you are connecting to a database server that can support more than one database (when querying an Access database file, for example, there is obviously only one database available). For SQL Server, this collection is named Databases when queried via the SqlClient provider, and Catalogs when queried via the OleDb provider. However, the Odbc provider for SQL Server does not expose this collection.

The Database Explorer Example Page
Before I give more detail on using the GetSchema method, it's a good idea to experiment. Find the ASP.NET page called database-explorer.aspx from the download; it allows you to see the list of metadata collections for SQL Server using the three standard providers and a JET database through two providers. The lists of collections are, in fact, the same as you saw in the earlier screenshots. However, you can select a metadata collection and see the entire contents in a second GridView control on the same page.

Figure 5 shows the example page in action, with the list of provider and database combinations at the top. Below this is the name and version of the selected database, which are extracted from the DataSourceInformation metadata collection. This is followed by the list of metadata collections that are available for the selected SQL Server with the SqlClient provider (not all are visible in the screenshot), with the Tables collection selected.

Figure 6. Inside Northwind: Part of the contents of the Tables metadata collection for the Northwind database is shown.
Figure 6 shows the second GridView control in this example page, populated with the contents of the Tables metadata collection (again, only part of the list is visible in the screenshot). However, you can see that the information available includes the database name (the table_catalog value), the name of the schema on which the table is based (in this case they are all owned by the dbo schema, but this is not always the case, especially in SQL Server 2005). There is, of course, the table name and also the table type. Notice that, as mentioned earlier, views as well as ordinary (base) tables are included in the collection and are differentiated by the value in the table_type column.

You will certainly find it useful to browse through the collections on your machine using the downloadable source (unfortunately, we are not able to run this example live on DevX for obvious security reasons). You'll probably be surprised at the amount of detail that is available. If you don't have a database server on your local machine, you can edit the connection strings in the web.config file to query a remote database elsewhere on your network instead.

Comment and Contribute






(Maximum characters: 1200). You have 1200 characters left.



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