Working With ADOX and Combo Box Control

Support files are available here for the ADOX files, and here for the Combo Box files.

Visual Studio .Net has come with a very useful Add-In -- the Server Explorer. We will not be discussing it in detail, but I will talk a little bit about the Data connection.

Have you ever thought about how they made it? If not, have you been wondered when working with the SQL Enterprise Manager when it displays the system tables that you don’t want to see?

In this article, we will make the very first step in a tool that you have the control of. You can also customize this tool to speed up your development process.

Since we are working with .NET, I'm sure you have used the ADO.NET, and before that the great ADO. But as a matter of fact, these tools didn’t give us any information about the database, the tables, stored procedures, or views.

I will have to agree that when we use SQL server, we can run some queries against the system tables, and we will get much of the information that we need. But this is now how the Server Explorer works -- it can connect to virtually any database that supports OLE DB and many others.

So, what should we do?

We have to roll back to the ADO, but don’t worry, we will use it and X this time. Our solution has been there for a while, ADOX.

ADOX is a COM component that has a very rich object model. You can check it out at:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/admscobjectmodel.asp

Let's see some code. After all, don’t we all like to talk code?

Code

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.

Using Combo Box Control

Since Visual Studio 6.0 and even before, we have been using the great Combo Box control. Since .Net has come with a list of new features and implementation to make our lives easier, the wind of change didn’t miss the Combo Box control.

In this part of the article we will get our hands on the code, and we will be exploring what the old combo box has became in a manner that has made our code much easier to develop, implement, read, and maintain.

If you haven't downloaded the project yet, then please do, so that we can keep in sync.

Here is the case we will be working on:

1) We have a Department, with some employees working in that department.

2) Going into an object oriented design, we will be having the following classes:

    a. Department.
    b. Employees: a collection of class Employee (employees working in the department)
    c. Employee: the employee is the person who works in the department.

3) We will be doing the following on the Form:

   a. We need to load the combo box with the employees in the department.

   b. When the user selects a new employee from the Combo Box, then we need to reflect the changes on the Text boxes, where we will be displaying the employee’s name and salary.

We will not be talking much about the design and how we implemented the classes, but we will be discussing the Combo Box in more details.

Employee Form

So let's discuss the form, Employees Information. When this form is first loaded, the “cboEmployees” combo will be loaded with the employees who works in the department.

At the old days we used to add an item to the Combo Box where we had the String that will be displayed in the Combo Box, and the other is the value in that item, where we used to be bounded to the only data type, Integer.

Things have changed; now we can bound the Combo Box to a collection, and this collection will fill the Combo Box without writing a lot of for loops, creating and destroying objects.

When the user selects an item, there is no need to fetch it from the database since we only have its ID.

So let's do the “FillEmployeesCombo” Sub in more details:

Private Sub FillEmployeesCombo()
        Dim oDepartment As Department
        Try
            oDepartment = New Department()
            'Bind the combo box to the to the Employees in the

department
            With cboEmployees
                .DataSource = oDepartment.Employees
                .DisplayMember = "Name"
                'Select the first Employee in the combobox if

there is any
                If .Items.Count > 0 Then
                    .SelectedIndex = 0
                End If
            End With
        Catch oException As Exception
            'Handle the Exception here
            MessageBox.Show(oException.ToString)
        End Try
    End Sub

1) We first created a Department object, in which we have Employees Collection, which is a collection of all the employees who works in that department.

We need to bind the Combo Box control to the Employees Collection. Easy as 1,2,3.

cboEmployees.DataSource = oDepartment.Employees

2) So far so good, but hey, if we bounded to a collection, and each employee has a “Name” and a “Salary”, what would be displayed in the combo box? Yes, we have to specify the property which the Combo Box will be displaying.

cboEmployees.DisplayMember = "Name"

What we did was specify the property name as string; in other words, we told the Combo Box that the collection of object it is binding to has a property called “Name” and that we want to use it to display each item in the Combo Box.

3) Finally, if there are some employees in the department, then we want to select the first item.

                If cboEmployees.Items.Count > 0 Then
                    cboEmployees.SelectedIndex = 0
                End If

More on Combo Box

We checked if the Combo Box had some items, and we checked the Count of the items. Since we have more than the zero items, then we will ask the Combo Box to select the first item, and we specify that the SelectedIndex should be to the first item (Zero based)

Very true, that should not be it, when the user selects an employee from the Combo Box, then we need to display the employee information such as the name and the salary.

Mind you that we bound the control to a collection of employees.

Couldn’t be more clever -- what we have in the “Items” property of the “cboEmployees” is nothing but a collection of “Employee” object.

So let's get into the “cboEmployees_SelectedIndexChanged” to get a better understanding of what we are talking about.

Private Sub cboEmployees_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles

cboEmployees.SelectedIndexChanged
        Dim oEmployee As Employee
        Try
            'Get the item that was selected in the combo box so

that we can load
            'the textboxes with information from the Employee that

was selected
            If cboEmployees.SelectedIndex > -1 Then
                oEmployee = cboEmployees.SelectedItem
                txtName.Text = oEmployee.Name
                txtSalary.Text = oEmployee.Salary

            End If
        Catch oException As Exception
            'Handle the Exception here
            MessageBox.Show(oException.ToString)
        End Try
    End Sub

1) First we need to check that the Combo Box has items.

2) If true, then we can set the selected item to the oEmployee Object, (remember that the items of the cboEmployees are nothing but a collection of Employee?)

3) Since we have a valid Employee object, then we can use its properties such as the “Name” and the “Salary”.

That was a quick implementation on how to use the new .Net Combo Box. It is so efficient specially when working between tiers. And we also hold to the Collections design pattern, and the object oriented concepts.