Visual Basic Magazine, February
Reposted with Permission of ZD
When you create GUIs for enterprise-strength systems,
implementing data storage and retrieval from the database can present some major
difficulties. Providing a flexible way to manipulate the data without locking up
the system is a serious concern. In part 1 of this series, "Creating an n-tier application in under 30 minutes", found in the January issue,
we showed you how the Flexible Business Object Framework quickly generates a
structure for an n-tier system. In this article, we'll show you how to use it to
overcome the data retrieval and storage problem described above.
Data retrieval alternatives
At the basic level, VB provides its data-bound controls for
easy data manipulation through the GUI. These controls have the advantage of
requiring almost no programming and offer quick and easy set up. On the other
hand, data-bound controls require exclusive locks to the underlying database
tables and become impractical when the application grows beyond one user.
As an alternative, you might combine SQL Server 7.0's
row-locking capabilities with the ADO or RDO recordset object model. A GUI that
used this combination would only lock a database row for the time it took to
fetch a recordset, modify its contents, and update it. For a moderate sized
system, this method offers a better solution than data bound controls, but on a
system with a large number of simultaneous users it will choke off access to the
SQL statements—still the best choice
To ensure that a system can handle a high number of users,
there's really no substitute for executing
SQL INSERT, UPDATE, and
statements. These statements are the fastest solution because they lock the
affected row for the brief moment as the SQL statement executes.
In spite of their power, however, direct SQL statements
do have some problems. SQL syntax becomes unwieldy when combined with a GUI's
dynamic nature, making direct SQL generation algorithms complex, tedious to
code, and error-prone. Also, if you change a single GUI field, you must rewrite
the core algorithm. This usually has nasty effects, most often causing new
enhancements to break existing functionality that used to work perfectly.
Enter the Flexible Object Framework
Fortunately, the Flexible Object Framework provides a way
to tap the power of direct SQL statements, without having to resort to complex
SQL generation routines. Beginning with a prototype and ending with a final
application, we'll quickly generate a GUI faster and quicker than you could in a
standard client/server application.
Download and install the Business Object Framework
Before we start, you need to install the Flexible Business
Object Framework. To do so, download and unzip this month's sample file from ftp.zdjournals.com/ivb/200002.ZIP.
Inside, you'll find the Framework2.zip. (For those who missed last month's
article, this file also includes all of the classes that we created previously).
If you use WinZip, make sure to select the Use Folder Names check box so that it
preserves the existing folder structure on your local hard drive. Also, don't
forget to take note of the directory into which you unzip the files. We'll refer
to it quite often throughout this article.
The business case
Let's start with a brief recap of the situation we found
ourselves in last month. The company we worked for, Acme Antenna Corporation,
sold television, satellite and infrared antennas to customers through direct
mail. The program management department needed a system to let them better track
First, we analyzed the users' requirements and created a
class-diagram that modeled their situation. Then, we used the Flexible Object
Framework and the Object Builder to quickly create two classes, which we called
Now, let's assume that while we created our classes, the
business analyst busily developed a prototype with the end-users shown in Figure
Figure A: We'll use the Flexible Object Framework
to load and update the data in this form.
The business analyst explains that the application needs
to display all of the antennas owned by Acme, along with each antenna ID, name,
and manufacturer. The form also needs to add and delete antennas using the
buttons on the right. If a user presses the add button, the program should show
a screen like the one shown in Figure B. Armed with this information, we're free
to create the application.
Figure B: With only a few lines of code, the
Flexible Object Framework generates the necessary statements to add the antenna
you supply to the list.
Implement the application's user interface tier
To begin implementing the application's user interface
tier, launch Visual Basic and open the master.vbg file located in the directory
into which you installed Framework2.zip. After the project loads, you'll see the
four Active-X DLL's that make up the framework.
First, select File | Add Project from the menu bar to
add a new standard EXE project to this group. Make sure to right-click on the
project in the Project Explorer window and select Set As Startup from the
resulting shortcut menu. Finally, choose Project | References, and then select
clsBaseClassLib10, clsBusObjLib10, clsClassFactoryLib10, and clsServerLib10.
Enable the user to view Acme's antennas
clsServer in the Flexible Object Framework
is similar to the Application object in the Microsoft Office object model; you
must create it before you can use the rest of the object model. To do so, first
add a new module to the project and then add the following code to the general
Public gobjServer as clsServer
Next, we initialize the variable in the startup form's
event, like this:
'create new topmost object
Set gobjServer = new clsServer
Now that we have the top-most framework object in place,
we're ready to proceed. We'll use a list view control to display Acme's
antennas, so we'll have to add the Microsoft Windows Common Controls OCX to the
project. To do so, select Project | Components from the menu bar, and then
select the Microsoft Windows Common Controls 6.0 check box and click OK. Add the
listview to the startup form and name it lvAntenna.
Back in the form's
Load() event, we need to
initialize the listview control. If you've coded this control before, then
you're on familiar territory:
.HideColumnHeaders = False
.View = lvwReport
.ColumnHeaders.Add , , "Id"
.ColumnHeaders.Add , , "Name"
.ColumnHeaders.Add , , "Manufacturer"
Next, we call a routine to load and show the antennas, as
That's it for the
Load() event, because most
of the work will be done inside
As the procedure's first act, it must clear the listview
of existing items, like so:
Next, it declares object variables. Before doing so, we
should consider what types of objects we need. The users need a list of
antennas, so the procedure should have some sort of object that holds this
Looking back at our class diagram from last month
(included in the \Design subdirectory underneath the directory into which you
installed the Framework2.zip file), it's pretty obvious that the
collection will do the trick. Since this collection object is just like any
other VB collection, we'll iterate through it with a
FOR EACH statement.
This means we'll also need a
‘create new antenna and antennas objects
Dim objAntenna As clsAntenna
Set objAntenna = CreateAntenna(gobjServer)
Dim objAntennas As clsAntennas
Set objAntennas = CreateAntennas(gobjServer)
What did we just do? The
declare two object variables, and the
CreateAntenna / CreateAntennas
functions initialize it. You'll see throughout this article that we always
initialize our framework variables with
Create, before doing
Fill the clsAntennas collection
Now we're ready to use the variables. Looking at the class
clsAntennas object has a method called
that loads it with every antenna. This is exactly what we want to do, so the
next line of code simply calls that method:
'load all antennas
That's all the code it takes to load the antennas into the
collection. Behind the scenes, the business object,
directly executes SQL to retrieve the data. However, due to encapsulation, we
don't have to concern ourselves with how it's done!
Fill the listview
Now that the procedure has loaded the antennas, it simply
needs to use a
For Each...Next loop to add each antenna to the
listview. Again, this only requires typical listview coding:
Dim objListItem As ListItem
'output each one to listbox
For Each objAntenna In objAntennas
Set objListItem = lvAntenna.ListItems _
.Add(, "Key" & objAntenna.AntennaId)
objListItem.Text = objAntenna.AntennaId
objListItem.SubItems(1) = objAntenna.Name
objListItem.SubItems(2) = _
As denoted by the
End Sub, at this point we've
finished. Go ahead and run the program, and you'll see a functioning listview
filled with Acme Inc's antennas. It's amazing how much we've done with just
three declarations and just over a dozen lines of code!
Add antennas to the list
While viewing the antenna list provides some simple
functionality, to make this GUI more than just a fancy report, we should allow
users to add new antennas to the inventory list.
To do so, first create a new button on the form and set
the caption to &Add. In the button's
Click() event, add
the following code:
'show add form
'update listview with results
This code displays the form,
frmAdd, seen in
Figure B (which we'll create in a moment), and then updates the listview with
the results of the new addition.
Next, create a new form and name it frmAdd. Using
Figure B as a guide, layout the textboxes, labels, and command buttons as shown.
Name the Name textbox txtName, and name the Manufacturer textbox txtManufacturer.
Now, as the Cancel button's
Click() event, add
In the OK button's
Click() event, the code
will add the new antenna to the
clsAntennas class. To enable the
code to do so, first declare and initialize a new antenna object:
'create a new antenna object
Dim objAntenna As clsAntenna
Set objAntenna = CreateAntenna
Next, the code sets its properties from the text boxes and
'set the object's properties
objAntenna.Name = txtName
objAntenna.Manufacturer = txtManufacturer
'save it to the database
That's all there is to it. Again, we've added significant
functionality to this program with very little code and absolutely no SQL or
Delete an Antenna from the list
As a final feature to our application, let's give users the
ability to delete an antenna. To start, add a new button to the startup form and
set the caption to &Delete. In the button's
event, we'll first validate that the user selected an item, like so
'check for nothing selected
If (lvAntenna.SelectedItem Is Nothing) Then
MsgBox "An antenna must be selected " & _
"to delete.", vbOKOnly, "User Error"
Now that we know the code has a valid antenna to delete, it
Antenna object into memory and issues the
method as follows:
Dim objAntenna As clsAntenna
Set objAntenna = CreateAntenna()
objAntenna.Load lvAntenna.SelectedItem _
Finally, the procedure refreshes the display to erase the
deleted antenna from the GUI:
Again, the code is extremely short as well as SQL and
In this article, we detailed how to program Flexible Object
Framework GUIs that can harness the power of direct SQL statements without
requiring complex SQL generation routines. Using the techniques demonstrated
here, you can quickly generate GUIs in a fraction of the time that it would take
you, using other methods.