Getting Schema Information (OLE DB) | Beginning ASP.NET 2.0 and Databases (Wrox Beginning Guides)

Chapter 3 - Connecting to a Data Source

byJohn Kauffman, Fabio Claudio Ferracchiatiet al.?
Wrox Press ?2002

Earlier in the chapter, we talked about some of the methods exposed by ADO.NET connection objects, but we deliberately weren't exhaustive. In this section, we'll take a look at a method that's supported only by the OleDbConnection class, but which can be enormously useful - especially when you're working with a data source whose precise structure you don't know.

If you always have good documentation about all of the data sources that you ever use, then not only will you be incredibly lucky, but also you'll never need to read this section. More likely, however, is that there will be inadequate (if any) documentation, and the guy who knows about the database will have left the company, using his stock-option wealth for a year's windsurfing in Bali. Fortunately, we can use an ADO.NET connection to dig up quite a bit of information about the schema, or structure, of the database being connected to.

Note

Just to reiterate, this technique is for OLE DB providers only. If you're connected to a SQL Server database (or some other enterprise DBMS), you can use standard SQL procedures for gathering schema information.

To get hold of this information, the syntax for connecting to the database is the same as we've studied so far, and we use DataSet and DataTable objects rather as we did with our XML connections. The difference comes when we fill these data containers with the results of a connection object method called GetOleDbSchemaTable(), which reads the structure of the connected database. To see how this works, we can start by looking at the syntax for getting a list of the tables in a given database. Let's say that we've set up a connection with the following lines of code:

    Dim strConnection As String = "provider=Microsoft.Jet.OLEDB.4.0;" & _                            "data source=C:\BegASPNETdb\datastores\nwind.mdb"    Dim objConnection As New OleDbConnection(strConnection)

Given that, the next step is not to instantiate a command object, but to create a DataSet and a DataTable object:

    Dim dsSchema As DataSet = New DataSet()    Dim dtSchema As DataTable

Then, we open the connection and employ GetOleDbSchemaTable() to read the schema information into the DataTable object. Note the italicized SchemaInformation below; here we can substitute the specific type of information we want to extract, such as "Tables":

    objConnection.Open()    dtSchema = objConnection.GetOleDbSchemaTable( _                            OleDbSchemaGuid.SchemaInformation, Nothing)

Finally, we add the table to our DataSet, and use that as the source of information for a DataGrid:

    dsSchema.Tables.Add(dtSchema)    dgSchema.DataSource = dsSchema    dgSchema.DataBind()    objConnection.Close()

The OleDbSchemaGuid class has static members that specify almost three dozen different kinds of information that can be extracted from the database; the most frequently used of these include:

Tables
Columns
Views
Indexes
Procedures
Foreign_Keys
Primary_Keys
Table_Constraints
Check_Constraints

Additional information may be found in sections of the ADO.NET Programmer's Reference (ISBN 1-86100-5-58-X), or in the MSDN documentation.

Try It Out - Discovering Schema Information

Let's imagine for now that the only thing we know about the Northwind database is that it exists in C:\BegASPNETdb\datastores, and that there is no password. Our goal is to find out the names of tables, and the columns within those tables.

Create a new page in the Ch03 folder named Schema.aspx and type (or download) the following code:

 <%@ Import namespace="System.Data" %> <%@ Import namespace="System.Data.Oledb" %> <html>   <head>     <title> Discovering Schema Information</title>   </head>   <body>     <h3>Discovering Schema Information</h3>     <asp:DataGrid  runat="server" />   </body> </html> <script language="VB" runat="server"> Sub Page_Load(Source As Object, E As EventArgs)   Dim strConnection As String = "provider=Microsoft.Jet.OLEDB.4.0;" & _                         "data source=C:\BegASPNETdb\datastores\nwind.mdb"   Dim objConnection As New OleDbConnection(strConnection)   Dim dsSchema As DataSet = New DataSet()   Dim dtSchema As DataTable   objConnection.Open()   dtSchema = objConnection.GetOleDbSchemaTable( _                         OleDbSchemaGuid.Tables, Nothing)   dsSchema.Tables.Add(dtSchema)   dgSchema.DataSource = dsSchema   dgSchema.DataBind()   objConnection.Close() End Sub </script>

Load up the page, and check the output for the tables that we've been working with. (Note that the tables whose names begin with MSys are for the internal use of Access, and are normally hidden from our view.)
If you want to explore the columns instead of the tables, it's easy - just change one part of one line:
```
 dtSchema = objConnection.GetOleDbSchemaTable( _                       OleDbSchemaGuid.Columns, Nothing) 
```
When you view this last page, there will be some delay - Access is searching all the tables, and eventually sends information on some 150 columns, spread across a dozen tables. Fortunately, we get the rows sorted by table name, so that all the columns of one table are adjacent to one another.

How It Works

The technique for getting schema information is not difficult, even though we're not entirely familiar with the DataSet and DataTable classes. Having created the connection as normal, our first task is to create an instance of each:

       Dim dsSchema As DataSet = New DataSet()       Dim dtSchema As DataTable

Then, we execute a method of the connection object to get the schema (the first parameter specifies the information set we want: tables, columns, foreign keys, etc.). That information is fed directly into our table object.

       objConnection.Open()       dtSchema = objConnection.GetOleDbSchemaTable( _                             OleDbSchemaGuid.Tables, Nothing)

Because a DataTable object cannot serve directly as the data source for a DataGrid, we must add our table to a DataSet object:

       dsSchema.Tables.Add(dtSchema)

The rest we are familiar with: just set and bind the dataset as the data source for the DataGrid.

       dgSchema.DataSource = dsSchema       dgSchema.DataBind()       objConnection.Close()

Of course, once you've used this technique to extract the schema, you still have a job ahead of you to put all of the data into context - but at least you have the pieces of the puzzle. I find that extracting schema information can be useful even with databases I know, in order to verify the exact spelling of a column or a procedure.