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 5

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

Querying the Tables Metadata Collection
As an example of using restrictions, the example page named get-tables.aspx demonstrates selecting in the Tables metadata collection with four restrictions and then with a single restriction. The code uses a generic routine that accepts a connection string, a metadata collection name, and a restrictions array; and returns a DataTable populated from the specified collection. The routine, a function named MyGetSchemaRoutine, is shown below:

Private Function MyGetSchemaRoutine(sConnString As String, _ sCollectionName As String, aRestrictArray As String()) As DataTable ' create a connection to the database Using conn As New SqlConnection(sConnString) Try conn.Open() ' return DataTable with all metadata about Tables that ' match the restrictions specified in the array Return conn.GetSchema(sCollectionName, aRestrictArray) Catch ex As Exception output.Text = "* ERROR: " & ex.Message Return Nothing Finally conn.Close() End Try End Using End Function

Figure 9. Using Restrictions: You can use restrictions with the Tables metadata collection.
The code in the Page_Load event handler can then call this routine to get a DataSet based on the values passed to it as parameters. In the following code listing, you can see that—after fetching the connection string for the web.config file—the code creates a restrictions array that specifies one table. Of course, not all of these restrictions are actually required to select a single table, as the connection itself defines the database that the information will be extracted from and (as you saw earlier) only the first three restrictions are required to uniquely identify a table. Once the restrictions array is populated, the code calls the MyGetSchemaRoutine function and then displays the results in the first GridView control declared in the HTML section of the page:

Sub Page_Load() ' read connection string from web.config Dim connect As String = ConfigurationSettings.ConnectionStrings( _ "nwind-sql").ConnectionString ' create a restrictions array to select specific rows ' Tables collection has 4 restrictions: ' table_catalog, table_schema, table_name, table_type ' null values indicate no restriction Dim restrictions(3) As String restrictions(0) = "Northwind" restrictions(1) = "dbo" restrictions(2) = "Orders" restrictions(3) = "BASE TABLE" ' call routine to get DataTable of matching metadata rows Dim table As DataTable = MyGetSchemaRoutine(connect, "Tables", restrictions) ' bind DataTable to first GridView control to display rows grid1.DataSource = table grid1.DataBind() ...

The code continues by clearing the restrictions array (setting all the items in it to null) and specifies just the table type restriction. Then it calls the MyGetSchemaRoutine function again and displays the results in the second GridView control declared in the HTML section of the page:

... ' clear restrictions array Array.Clear(restrictions, 0, restrictions.Length) ' specify new restriction to select: ' table_type="VIEW" (i.e. exclude BASE TABLES) restrictions(3) = "VIEW" ' call routine to get DataTable of matching metadata rows table = MyGetSchemaRoutine(connect, "Tables", restrictions) ' bind DataTable to second GridView control to display rows grid2.DataSource = table grid2.DataBind() End Sub

Figure 9 shows the results of running this example, allowing you to see how easy it is to get lists of tables from your database using the Schema API. And if all you want to do is check if a table exists, you just specify enough restrictions to uniquely select it in the Tables metadata collection, and then query the Count property of the Rows collection of the DataTable to see if it is equal to one (or greater than zero):

Dim restrictions(3) As String restrictions(0) = "Northwind" restrictions(1) = "dbo" restrictions(2) = "Orders" Dim dt As DataTable = conn.GetSchema("Tables") If dt.Rows.Count = 1 Then ' table exists in database Else ' table does not exist in database End If

Querying the Columns Metadata Collection
Figure 10. Column Extraction: Extract and display table column information using the Schema API.
As a final example of using the Schema API, the example page named get-column-details.aspx shows a common scenario that you may come across when building data management tools. If you want to know all about a specific table in a database and, in particular, details of the columns in the table, you can use the Columns metadata collection. This example queries the Columns metadata collection for columns in the Orders table of the Northwind database and displays the details in an attractive and readable format.

Obtaining the information from the Columns metadata collection is easy enough—you just populate restriction entries that specify the table but not the column name. In other words, you just require:

Dim restrictions(3) As String restrictions(0) = "Northwind" restrictions(1) = "dbo" restrictions(2) = "Orders"

Then you call the GetSchema method for the Columns metadata collection:

Dim dt As DataTable = conn.GetSchema("Columns", restrictions)

The awkward part is converting the contents of the DataTable into an attractive display. We chose to create an HTML table dynamically from the DataTable, adding style attributes to give the appearance shown in Figure 10 (not all columns from the original Orders table in the database are visible). The code to generate the HTML table is not listed here, as it is not really the focus of this article, but you can download the samples from http://www.daveandal.net/articles/schema.api/ to view all the code.

Author's Note: Note that the code used in this example does not display all the data items for each column that is included in the metadata collection. To make the display more compact, the code that creates the HTML table ignores the table_catalog, table_schema, and table_name—which are, of course, the same for every column.

The main point of this example is to give you a chance to explore the details that are available from the Columns metadata collection. You can see that the columns are ordered alphabetically in the DataTable containing the metadata but their ordinal position is contained in the metadata. Also visible (amongst other useful data) is the default value, nullability, data type, the maximum length for character-based columns; the precision, scale, and size for numeric columns; and the character set used.

Alex Homer is a director of Stonebroom, Ltd., a software development, consulting, and training organization. He was formerly lead technical author and reviewer for Wrox, specializing in Microsoft Web and database technologies. You can reach him through his Web site.
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