Create your first n-tier app in under 30 minutes! : Part II

Submitted on: 1/6/2015 4:35:00 AM
By: Ian Ippolito (PSC)  
Level: Intermediate
User Rating: By 6 Users
Compatibility: VB 5.0, VB 6.0
Views: 1317
author picture
     Part 2 of this series we learn how to program dynamic gui's using the Flexible Object Framework we developed in Part 1.

This article has accompanying files
				From Inside
Visual Basic Magazine, February
Reposted with Permission of ZD Net Journals

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 database.

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 DELETE 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 Inside, you'll find the (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 their inventory.

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 clsAntenna and clsAntennas.

Now, let's assume that while we created our classes, the business analyst busily developed a prototype with the end-users shown in Figure A.

Figure A: We'll use the Flexible Object Framework to load and update the data in this form.
[ Figure A ]

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.
[ Figure B ]

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 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

The 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 declarations section:
Public gobjServer as clsServer
Next, we initialize the variable in the startup form's Load() 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:

With lvAntenna
 .HideColumnHeaders = False
 .View = lvwReport
 .ColumnHeaders.Add , , "Id"
 .ColumnHeaders.Add , , "Name"
 .ColumnHeaders.Add , , "Manufacturer"
End With
Next, we call a routine to load and show the antennas, as in

‘show antennas


That's it for the Load() event, because most of the work will be done inside mShowAntennas.

As the procedure's first act, it must clear the listview of existing items, like so:

Sub mShowAntennas()
With lvAntenna
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 information.

Looking back at our class diagram from last month (included in the \Design subdirectory underneath the directory into which you installed the file), it's pretty obvious that the clsAntennas 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 clsAntenna object:

‘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 Dim statements 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 anything else.

Fill the clsAntennas collection

Now we're ready to use the variables. Looking at the class diagram, the clsAntennas object has a method called LoadAll 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, clsAntennas, 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) = _
Next objAntenna
End Sub
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
frmAdd.Show vbModal
'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

Unload Me
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 saves it:
'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 recordset programming!

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 Click() 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"
 Exit Sub

End If

Now that we know the code has a valid antenna to delete, it loads an Antenna object into memory and issues the Delete method as follows:
'create object
Dim objAntenna As clsAntenna
Set objAntenna = CreateAntenna()
'load it
objAntenna.Load lvAntenna.SelectedItem _
'delete it
Finally, the procedure refreshes the display to erase the deleted antenna from the GUI:
'refresh display
Again, the code is extremely short as well as SQL and recordset free.


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.

winzip iconDownload article

Note: Due to the size or complexity of this submission, the author has submitted it as a .zip file to shorten your download time. Afterdownloading it, you will need a program like Winzip to decompress it.Virus note:All files are scanned once-a-day by Planet Source Code for viruses, but new viruses come out every day, so no prevention program can catch 100% of them. For your own safety, please:
  1. Re-scan downloaded files using your personal virus checker before using it.
  2. NEVER, EVER run compiled files (.exe's, .ocx's, .dll's etc.)--only run source code.
  3. Scan the source code with Minnow's Project Scanner

If you don't have a virus scanner, you can get one at many places on the net

Other 13 submission(s) by this author


Report Bad Submission
Use this form to tell us if this entry should be deleted (i.e contains no code, is a virus, etc.).
This submission should be removed because:

Your Vote

What do you think of this article (in the Intermediate category)?
(The article with your highest vote will win this month's coding contest!)
Excellent  Good  Average  Below Average  Poor (See voting log ...)

Other User Comments

 There are no comments on this submission.

Add Your Feedback
Your feedback will be posted below and an email sent to the author. Please remember that the author was kind enough to share this with you, so any criticisms must be stated politely, or they will be deleted. (For feedback not related to this particular article, please click here instead.)

To post feedback, first please login.