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 4

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

Programming with the Schema API
You saw a brief example near the start of this article of using the Schema API to get a list of the available metadata collections. In this section, you'll see in more detail how you can extract specific sets of data from these collections and use them in your applications. As a simple example, you saw in the previous section how the database explorer page displays the name and version of the database being queried. This is easy to do. Assuming you have already created a connection to the database, you just call the GetSchema method with the parameter "DataSourceInformation" to get a DataTable containing a single row that describes the database engine. Then you can pull the values out of the row, format them into the required string, and display them.

' get the database/server details and display them Dim dt As DataTable = conn.GetSchema("DataSourceInformation") output-label.Text = String.Format("{0} (version {1})", _ dt.Rows(0)("DataSourceProductName").ToString(), _ dt.Rows(0)("DataSourceProductVersion").ToString())

Figure 7 shows the resulting output from this code. You can, of course, use the database explorer example page to obtain the column names (and see what data they contain). It will help you write code that takes advantage of the Schema API.

Figure 7. Engine Info: The database engine name and version is displayed.
Understanding Metadata Collection Restrictions
So far, you've seen how you can extract the contents of a metadata collection by specifying the collection name in a call to the GetSchema method. However, there is an overload of the GetSchema method that takes both a collection name and a restriction array (as mentioned in Table 1 earlier). The restriction array is simply an array of String values that are used to filter the rows in the collection so that you can effectively select only the information you want.

Each collection has a specific number of restrictions (i.e. fields or columns that you can filter on), and you must provide a restriction array of the correct size for the collection you specify when you call GetSchema. The number of restrictions is contained the MetaDataCollections collection, as you can see by referring back to Figures 1 to 4. For example, the number of restrictions for the Tables and Columns collections is four, while the Databases and Catalogs collections (where available) accept only one restriction. Collections that accept zero restrictions do not allow you to filter the rows that are returned—examples being the MetaDataCollections collection itself, and the DataSourceInformation and DataTypes collections.

More details of the restrictions for each collection are available from the Restrictions collection, which should be available for all provider/database combinations. You can query this collection with the GetSchema method to see them all (you can't apply restrictions to this collection) using:

Dim dt As DataTable = conn.GetSchema("Restrictions")

Alternatively, you can use the Database Metadata Explorer sample we provide. Figure 8 shows the results for the SqlClient provider against a SQL Server 2005 database. The first column shows the metadata collection name, the second column shows the name of the restriction as used within the database (i.e. the name of the object is filters upon), the third column shows the name that defines the restriction (and is used as the column name of the returned DataTable), and the fourth column shows the order of the restrictions for each collection.

Figure 8. Using Restrictions: You can view the Restrictions metadata collection in the Database Explorer example page.
Unique Identifier Parts
One other item of information returned by the MetaDataCollections collection is the number of identifier parts that are required to make up a unique restriction on each metadata collection. For example, as you can see in Figures 1 to 4, the Tables metadata collection accepts four restrictions but only three of these (the value in the NumberOfIdentifierParts column) are required to specify a table uniquely. This is because a table can only be of type BASE TABLE or of type VIEW, and yet you cannot use the same name for a table and a view in the same database. Therefore, the combination of the database/catalog name, the owner/schema name, and the table name are sufficient to uniquely identify a table.

On the other hand, the Columns collection has four restrictions and four unique identifier parts. As you can see in Figure 8, the first three restrictions are the same as for the Tables collection (i.e. the database/catalog name, the owner/schema name, and the table name). But these alone are not sufficient to uniquely identify a column in a table, and so the fourth restriction (the column name) is also required. Hence the number of identifier parts for this collection is four.

You can, of course, filter on any of the restrictions, including those that are not part of the set of unique identifiers. For example, when using the Tables metadata collection, you can specify a restriction for the table type—perhaps to get a list of all views or all base tables when you don’t specify a table name restriction.

Creating and Using a Restriction Array
Restrictions are useful when you only want to select a specific item, for example to find details about one table that you know exists in the database. As you can see in Figures 1 to 4, you need to use a restrictions array of size four and the filters you can apply are the database (catalog) name, the owner (or schema name), the table name, and the table type ("BASE TABLE" or "VIEW"). Assuming you know the four values, you can filter on all of them like this:

Dim restrictions(3) As String restrictions(0) = "Northwind" ' database/catalog name restrictions(1) = "dbo" ' owner/schema name restrictions(2) = "Orders" ' table name restrictions(3) = "BASE TABLE" ' table type Using conn As New SqlConnection("your-connection-string") Try conn.Open() grid1.DataSource = conn.GetSchema("Tables", restrictions) grid1.DataBind() Catch ex As Exception output.Text = "* ERROR: " & ex.Message Finally conn.Close() End Try End Using

Authors Note: When creating an array in VB.NET, you specify the highest index. All arrays in .NET are indexed from zero, so the code Dim restrictions(3) As String creates an array of four items. However, in C# you specify the number of items, so the equivalent code is: String[] restrictions = new String[4];

You can also use just one, or some, of the restrictions. Any array items that you leave set to Nothing (null in C#) will be ignored. For example, if you want all tables named Orders, irrespective of which owner/schema they belong to, you could use:

Dim restrictions(3) As String restrictions(2) = "Orders" ' table name restrictions(3) = "BASE TABLE" ' table type

And if you want to find either a table or a view with a specific name, you can omit the fourth restriction as well:

Dim restrictions(3) As String restrictions(2) = "Orders" ' table name

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