Retrieving Database Schema Information using the OleDbSchemaGuid Class: ASP Alliance

Print Add To Favorites Email To Friend Rate This Article
Retrieving Database Schema Information using the OleDbSchemaGuid Class

page 3 of 4

by Steven Swafford
Feedback

Average Rating: This article has not yet been rated.
Views (Total / Last 10 Days): 25974/ 94

Article Contents:

Create the Web Form

The next step is to create the Web form. In my example, I have created the following Web form using DataGrid, Label, Panel, ListBox, and Button controls.

Figure 4: Example Web Form

Obtaining Schema Details from an Access Database

We have now created a Web form and used its codebehind to connect to the Access database. The next step is to retrieve the database schema information.

We will do this using the GetOleDbSchemaTable method of the OleDbConnection class. This method returns schema information from the connected data source. In this example I will be using the following three fields of the OleDbSchemaGuid class.

Tables - Returns the tables (including views) defined in the catalog that are accessible to a given user.
Columns - Returns the columns of tables (including views) defined in the catalog that are accessible to a given user.
Primary_Keys - Returns the primary key columns defined in the catalog by a given user.

For a full listing of the fields within this class be sure to look at the documentation for the OleDbSchemaGuid class.

The first thing that must be accomplished is establishing the database connection (see Figures 1, 2, and 3).

The next step is to write the code that will populate the ListBox that will display the name of the available tables in the Access database.

Figure 5: GetTableListing() Method

private void GetTableListing()
{
 try
 {
  DataTable dbSchema = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
   new object[] {null, null, null, "TABLE"});


  TablesListBox.Items.Add("Select");


  foreach(DataRow row in dbSchema.Rows)
  {
   TablesListBox.Items.Add(row["TABLE_NAME"].ToString());
  }


  TablesListBox.SelectedIndex = 0;
 }
 catch (OleDbException ex)
 {
  ErrorsLabel.Text = ex.Message;
 }
 catch (InvalidOperationException ex)
 {
  ErrorsLabel.Text = ex.Message;
 }
 catch (ArgumentException ex)
 {
  ErrorsLabel.Text = ex.Message;
 }
}

In the method above a number of actions will be performed. The first is to create a DataTable object using the GetOleDbSchemaTable method of the existing database connection. This method takes two parameters: the schema table to return and an array of restriction values. As we intend to retrieve a list of available tables, we provide the Tables field type as the first parameter, and a restriction to return only tables as the second parameter. Remember that it is good practice to catch and handle any exception.

When the example Web form first loads, you should see a populated ListBox, as shown below.

Figure 6: Populated Tables ListBox

The next step is to populate a ListBox that will contain the column names for the table selected in the previous ListBox. To accomplish this I am taking advantage of the OnSelectedIndexChanged method of the tables ListBox.

Figure 7: TablesIndexChanged() Method

public void TablesIndexChanged(Object sender, EventArgs e)
{
 string tableName = TablesListBox.SelectedItem.Value;
 GetColumnListing(tableName);
}

As you can see in the above example, I am performing two separate actions. First, I retrieve the SelectedItem value of the table ListBox. Second, I call a method named GetColumnListing and pass in the parameter named tableName. By passing this parameter to this method, we can retrieve a collection of column names that are associated with the named table.

Figure 8: GetColumnListing() Method

private void GetColumnListing(string tableName)
{
 if(tableName == "Select")
 {
  tableErrorLabel.Text = "You must select a table!";
  tableErrorLabel.Visible = true;
  ColumnsListBox.Items.Clear();
 }
 else
 {
  try
  {
   DataTable dbSchema = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns,
    new object[] {null, null, tableName, null});</font>


<font size="2">ColumnsListBox.Items.Clear();</font>


<font size="2">   foreach(DataRow row in dbSchema.Rows)
   {
    ColumnsListBox.Items.Add(row["COLUMN_NAME"].ToString());
   }
  }
  catch (OleDbException ex)
  {
   ErrorsLabel.Text = ex.Message;
  }
  catch (InvalidOperationException ex)
  {
   ErrorsLabel.Text = ex.Message;
  }
  catch (ArgumentException ex)
  {
   ErrorsLabel.Text = ex.Message;
  }
 }
}

I discussed earlier the GetOleDbSchemaTable method and the OleDbSchemaGuid class. In the above example, I am now using the field type Columns, and placing a restriction to return only columns of a given table. This table name comes from the parameter obtained in the TablesIndexChanged() method (see Figure 7). Note again that it is good practice to catch and handle any exceptions. If everything has gone as expected, you should now see a populated ListBox, as follows.

Figure 9: Populated Columns ListBox

The next thing to discuss is displaying the schema information. In the Web Form we have two DataGrids. To the first DataGrid we bind the selected data; to the second DataGrid we bind primary key details. The QueryButton_Click method performs a variety of actions. First, and most importantly, we need to validate that there have been selections made for both the table ListBox and the column ListBox controls. This is accomplished by ensuring there is a selected value for these controls.

Figure 10: QueryButton_Click Method

private void QueryButton_Click(object sender, System.EventArgs e)
{
 if(TablesListBox.SelectedItem.Value != null && ColumnsListBox.SelectedItem.Value != null)
 {
  QueryDatabase();
  GetPrimaryKeys();
  Panel1.Visible = true;
  CloseDatabaseConn();
 }
}

If you look at the above example, you will see that I am validating that there is a Selected value for both ListBox controls. When this validation statement evaluates to true, only then do I perform four more actions.

We take these two values and query our database. I will not go into the details of the OleDbDataApter and DataSet; rather, at the end of this article, I will provide further resources to assist you. Basically what is needed here is to build a dynamic SQL statement for the query we wish to run.

Figure 11: Dynamic SQL Statement

string strSql = "Select [" + tableName + "].[" + ColumnName + "] from [" + tableName + "]";

Because we are also going to display primary key details, we will call the method GetPrimaryKeys().

Figure 12: GetPrimaryKeys() Method

private void GetPrimaryKeys()
{
 string tableName = TablesListBox.SelectedItem.Value;</font>


<font size="2"> try
 {
  DataTable dbSchema = oleDbConn.GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys,
   new object[] {null, null, tableName});</font>


<font size="2">  // Set the DataGrids DataSource
  PKDataGrid.DataSource = dbSchema;</font>


<font size="2">  // Bind The DataGrid
  PKDataGrid.DataBind();</font>


<font size="2">  // Set The DataGrids Visibility State To True
  PKDataGrid.Visible = true;
 }
 catch (OleDbException ex)
 {
  ErrorsLabel.Text = ex.Message;
 }
 catch (InvalidOperationException ex)
 {
  ErrorsLabel.Text = ex.Message;
 }
 catch (ArgumentException ex)
 {
  ErrorsLabel.Text = ex.Message;
 }
}

I am using a Panel to hold the two DataGrids. I initially set the Visible property to false; I now set it to true.

Finally, we need to close and dispose of our database connection.

Once you bolt together these various methods, you can now execute this Web Form. If everything has compiled properly you should now see a screen similar to the following.

Figure 13: Executing the Web Form

It is worth noting that I have used a DataGrid to show the results of a selected column. Because a DataGrid does nt support BLOB data types, if you select any column having a BLOB datatype it will not display the data. This example is only to show how one can retrieve database schema information using the OleDBSchemaGuid class.