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

<font face="Verdana" size="2">From <a href="http://www.zdjournals.com/ivb/0003/ivb0031.htm">Inside
Visual Basic Magazine</a>, </font><font face="Verdana"><font size="2">February
2000</font><br>
</font><font face="Verdana" size="2">Reposted with Permission of <a href="http://www.zdjournals.com/">ZD
Net Journals</a><br>
<p><font face="Verdana">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, <a href="/vb/scripts/ShowCode.asp?lngWId=1&txtCodeId=7474">"Creating an n-tier application in under 30 minutes</a>", 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.</font>
<h3><font face="Verdana">Data retrieval alternatives</font></h3>
<font face="Verdana">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.</font>
<h3><font face="Verdana">Row-locking</font></h3>
<font face="Verdana">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.</font>
<h3><font face="Verdana">SQL statements—still the best choice</font></h3>
<font face="Verdana">To ensure that a system can handle a high number of users,
there's really no substitute for executing <code>SQL INSERT, UPDATE,</code> and <code>DELETE</code>
statements. These statements are the fastest solution because they lock the
affected row for the brief moment as the SQL statement executes.</font>
<p><font face="Verdana">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.</font>
<h3><font face="Verdana">Enter the Flexible Object Framework</font></h3>
<font face="Verdana">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.</font>
<h3><font face="Verdana">Download and install the Business Object Framework</font></h3>
<font face="Verdana">Before we start, you need to install the Flexible Business
Object Framework. To do so, download and unzip this month's sample file from <a href="ftp://ftp.zdjournals.com/ivb/200002.ZIP">ftp.zdjournals.com/ivb/200002.ZIP</a>.
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.</font>
<h3><font face="Verdana">The business case</font></h3>
<font face="Verdana">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.</font>
<p><font face="Verdana">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 <code>clsAntenna</code>
and <code>clsAntennas</code>.</font>
<p><font face="Verdana">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.</font>
<p><font face="Verdana"><b>Figure A:</b> We'll use the Flexible Object Framework
to load and update the data in this form.<br>
<img alt="[ Figure A ]" border="0" height="253" src="/vb/tutorial/vb/images/NTier2_1.gif" width="456"></font>
<p><font face="Verdana">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.</font>
<p><font face="Verdana"><b>Figure B:</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.<br>
<img alt="[ Figure B ]" border="0" height="154" src="/vb/tutorial/vb/images/NTier2_2.gif" width="299"></font>
<h3><font face="Verdana">Implement the application's user interface tier</font></h3>
<font face="Verdana">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.</font>
<p><font face="Verdana">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.</font>
<h3><font face="Verdana">Enable the user to view Acme's antennas</font></h3>
<font face="Verdana">The <code>clsServer</code> 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:</font>
<pre><font face="Verdana">Public gobjServer as clsServer</font></pre>
<font face="Verdana">Next, we initialize the variable in the startup form's <code>Load()</code>
event, like this:</font>
<pre><font face="Verdana">'create new topmost object
Set gobjServer = new clsServer</font></pre>
<font face="Verdana">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 <i>lvAntenna</i>.</font>
<p><font face="Verdana">Back in the form's <code>Load()</code> event, we need to
initialize the listview control. If you've coded this control before, then
you're on familiar territory:</font>
<pre><font face="Verdana">With lvAntenna
 .HideColumnHeaders = False
 .View = lvwReport
 .ColumnHeaders.Add , , "Id"
 .ColumnHeaders.Add , , "Name"
 .ColumnHeaders.Add , , "Manufacturer"
End With</font></pre>
<font face="Verdana">Next, we call a routine to load and show the antennas, as
in</font>
<p><font face="Verdana">‘show antennas</font></p>
<p><font face="Verdana">mShowAntennas</font></p>
<font face="Verdana">That's it for the <code>Load()</code> event, because most
of the work will be done inside <code>mShowAntennas</code>.</font>
<p><font face="Verdana">As the procedure's first act, it must clear the listview
of existing items, like so:</font></p>
<pre><font face="Verdana">Sub mShowAntennas()
With lvAntenna
 .ListItems.Clear</font></pre>
<font face="Verdana">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.</font>
<p><font face="Verdana">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 <code>clsAntennas</code>
collection will do the trick. Since this collection object is just like any
other VB collection, we'll iterate through it with a <code>FOR EACH </code>statement.
This means we'll also need a <code>clsAntenna</code> object:</font>
<pre><font face="Verdana">‘create new antenna and antennas objects
Dim objAntenna As clsAntenna
Set objAntenna = CreateAntenna(gobjServer)
Dim objAntennas As clsAntennas
Set objAntennas = CreateAntennas(gobjServer)</font></pre>
<font face="Verdana">What did we just do? The <code>Dim</code> statements
declare two object variables, and the <code>CreateAntenna / CreateAntennas</code>
functions initialize it. You'll see throughout this article that we always
initialize our framework variables with <code>Create</code>, before doing
anything else.</font>
<h3><font face="Verdana">Fill the clsAntennas collection</font></h3>
<font face="Verdana">Now we're ready to use the variables. Looking at the class
diagram, the <code>clsAntennas</code> object has a method called <code>LoadAll</code>
that loads it with every antenna. This is exactly what we want to do, so the
next line of code simply calls that method:</font>
<pre><font face="Verdana">'load all antennas
objAntennas.LoadAll</font></pre>
<font face="Verdana">That's all the code it takes to load the antennas into the
collection. Behind the scenes, the business object, <code>clsAntennas,</code>
directly executes SQL to retrieve the data. However, due to encapsulation, we
don't have to concern ourselves with how it's done!</font>
<h3><font face="Verdana">Fill the listview</font></h3>
<font face="Verdana">Now that the procedure has loaded the antennas, it simply
needs to use a <code>For Each...Next</code> loop to add each antenna to the
listview. Again, this only requires typical listview coding:</font>
<pre><font face="Verdana">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) = _
 objAntenna.Manufacturer
Next objAntenna
End Sub</font></pre>
<font face="Verdana">As denoted by the <code>End Sub</code>, 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!</font>
<h3><font face="Verdana">Add antennas to the list</font></h3>
<font face="Verdana">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.</font>
<p><font face="Verdana">To do so, first create a new button on the form and set
the caption to <i>&Add</i>. In the button's <code>Click()</code> event, add
the following code:</font>
<pre><font face="Verdana">'show add form
frmAdd.Show vbModal
'update listview with results
mShowAntennas</font></pre>
<font face="Verdana">This code displays the form, <code>frmAdd</code>, seen in
Figure B (which we'll create in a moment), and then updates the listview with
the results of the new addition.</font>
<p><font face="Verdana">Next, create a new form and name it <i>frmAdd</i>. Using
Figure B as a guide, layout the textboxes, labels, and command buttons as shown.
Name the Name textbox <i>txtName</i>, and name the Manufacturer textbox <i>txtManufacturer</i>.
Now, as the Cancel button's <code>Click() event</code>, add</font>
<pre><font face="Verdana">Unload Me</font></pre>
<font face="Verdana">In the OK button's <code>Click()</code> event, the code
will add the new antenna to the <code>clsAntennas</code> class. To enable the
code to do so, first declare and initialize a new antenna object:</font>
<pre><font face="Verdana">'create a new antenna object
Dim objAntenna As clsAntenna
Set objAntenna = CreateAntenna</font></pre>
<font face="Verdana">Next, the code sets its properties from the text boxes and
saves it:</font>
<pre><font face="Verdana">'set the object's properties
objAntenna.Name = txtName
objAntenna.Manufacturer = txtManufacturer
'save it to the database
objAntenna.Save</font></pre>
<font face="Verdana">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!</font>
<h3><font face="Verdana">Delete an Antenna from the list</font></h3>
<font face="Verdana">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 <i>&Delete</i>. In the button's <code>Click()</code>
event, we'll first validate that the user selected an item, like so</font>
<pre><font face="Verdana">'check for nothing selected
If (lvAntenna.SelectedItem Is Nothing) Then
 MsgBox "An antenna must be selected " & _
 "to delete.", vbOKOnly, "User Error"
 Exit Sub
</font></pre>
<p><font face="Verdana">End If</font></p>
<font face="Verdana">Now that we know the code has a valid antenna to delete, it
loads an <code>Antenna</code> object into memory and issues the <code>Delete</code>
method as follows:</font>
<pre><font face="Verdana">'create object
Dim objAntenna As clsAntenna
Set objAntenna = CreateAntenna()
'load it
objAntenna.Load lvAntenna.SelectedItem _
 .Text
'delete it
objAntenna.Delete</font></pre>
<font face="Verdana">Finally, the procedure refreshes the display to erase the
deleted antenna from the GUI:</font>
<pre><font face="Verdana">'refresh display
mShowAntennas</font></pre>
<font face="Verdana">Again, the code is extremely short as well as SQL and
recordset free.</font>
<h3><font face="Verdana">Conclusion</font></h3>
<font face="Verdana">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.</font>
Upload