Working With ADOX and Combo Box Control - Code

(Page 2 of 5 )

After you download the project files, you will find out that we have a single form which has the following:

• Two Labels with captions of “Tables” and “Table Keys”.
• Two List Boxes:

      - lstTables: which will have all the Tables available in the database specified in the connection string.
      - lstKeys: when a user selects a table from the “lstTable”, the lstKeys will be filled with the Keys specified for the table (in the database for sure)

Design Consideration:

1. We need a connection to the data source, and we need an instance of the ADOX. They will be made available as private variables for the class, in our case, the form.
   
   
2. In the “Load”  event of the form, we will initialize the variables; please make sure to update the connection string.
   
   
3. We need to ensure that we have a reference to the ADO, and the ADOX COM components:

a. MSADO
b. MSADOX

Note: The dll’s are usually available on “C:\Program Files\Common Files\System\ado”

So, let's now have a look at the code, first of all the code that loads the tables available in the system, but we don’t want to list the System tables.

'======================================
    ' This Sub will fill the lstTables with the table names for

the Database specified in the connection String
    '======================================
    Private Sub FillTablesList()
        Dim oTable As ADOX.Table
        For Each oTable In oDatabase.Tables
            If oTable.Type = "TABLE" Then
                lstTables.Items.Add(oTable.Name)
            End If
        Next
    End Sub

As you can tell, we checked the type of the table to be “Table”, the type may have another two values, “SYSTEM TABLE”, and “GLOBAL TEMPORARY”.

And here is the code that will list the keys when a user selects a certain table.

'======================================
    ' This Sub will fill the lstKeys with the name of the Keys for

the table selected by the user.
    '======================================
    Private Sub lstTables_SelectedIndexChanged(ByVal sender As

System.Object, ByVal e As System.EventArgs) Handles

lstTables.SelectedIndexChanged
        Dim oTable As ADOX.Table
        Dim oKey As ADOX.Key

        oTable = oDatabase.Tables(lstTables.SelectedItem)
        lstKeys.Items.Clear()
        For Each oKey In oTable.Keys
            lstKeys.Items.Add(oKey.Name)
        Next

    End Sub

Note that we are looping through all the keys in the table. Since a table may not only have one key, besides, foreign keys, and unique keys are included in the collection.

As we mentioned at the beginning of the article. This is only the first step for a great tool. We can mention some of the big tools that can be based on this great component:

1) Code Generators: as a matter of fact, we do really care about the tables, columns, and all the available information about the database to make our data access tier. And from our data access tier we can make the business tier.

2) Database management tools: with more work, you can play with the tables. If you are not so comfortable with the database management tool you already have, or if your database vendor didn’t supply one, or you would like to add new functionality that is not available to your software, then you now know how to make things brighter.

And I will leave the list open for your preference.

Happy programming to all.

Next: Using Combo Box Control >>
 

More Database Articles
More By Mohammed Qattan

Comments On: Working With ADOX and Combo Box Control

>>> Be the FIRST to comment on this article!