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.