Data Object Wizard
This column appeared in the February 1999 edition of
EXE. It continues the theme started in the January 1999 column concerning the
Data Environment Designer.
Sample code and material is available here
Last month I discussed the Data Environment Designer (DED), a
tool that allows ADO objects to be defined graphically. Following on with this
theme I am now going to introduce the Data Object Wizard (DOW). Whereas the DED
is concerned with reducing the amount of code that is needed the DOW works from
the opposite principle: it actually writes the code for you.
Available only within the Enterprise edition, this tool
actually provides two related functions. The first of these is to provide a
Visual Basic class module that is based around a DED command object. This class
module then effectively acts as a data source. Once this has been defined the
wizard then allows the creation of a User Control which can be applied to a
form. We will work through the creation of these components and then examine
the results.
Creating a data class
In order to efficiently explain how to use the DOW we will
work through an example. Because this article follows on from last month's
introduction to the DED we will again make use of the pubs database that is
shipped with SQL Server. As a quick reminder, this database contains a simple
set of book authors, titles, and publishers, and contains a rudimentary set of
test data. Unfortunately it doesn't contain a particularly well-stocked set of
stored procedures. If you want to actually try this example yourself you will
need to apply a small stored procedure creation file called exvb0299.sql . This
file applies basic Select, Insert, Update, and Delete stored procedures (called
au_select, au_insert, au_amend, and au_delete respectively) to the authors
table.
To get started you should first create a new Visual Basic 6
Data Project. By default this will create a form, a Data Environment, and a
Data Report. We won't be using Data Reports in this instance so it can be
deleted from the project list if you prefer. Perform the following actions:
-
Double click the DataEnvironment1 item within the Project Window in order to
bring up the DED. Right click on the Connection1 icon and select the Properties
menu. Then, as explained in last months article, add the necessary parameters
to connect you to your server, making sure that you also include pubs as your
target database.
-
Bring up the Data View window (found in the View menu). If you expand the
Connection1 folder, and then the Stored Procedures folder, you should see the
four additional stored procedures that were added from our exvb0299.sql file.
Sequentially drag each of these stored procedures over to the Connection1
object in the DED. This operation highlights the difference between the two
windows: the Data View window contains a snapshot (called Connection1) of the
data source defined by the connection parameters, whereas the DataEnvironment
window contains the working definition of our data object (also called
Connection1). Incidentally, when you add the Insert, Update, and Delete stored
procedures you will be asked in each case whether you actually want to execute
the command. This occurs because the engine is expecting to find data returned
from each procedure, which of course doesn't happen in these cases. I suggest
that you choose not to run the command in each of these cases because in doing
so you could actually modify the underlying data.
-
Assuming that the above steps worked ok you can now invoke the Data Object
Wizard itself, which can be found under the Add-Ins menu. After the initial
introductory page you are asked whether you want to either "create a class
object to which other objects can bind data", or to create a "user control
object bound to an existing Class object". Select the first of these options.
-
The wizard will then present you with a tree-view representation of all the
DataEnvironment objects that have been created. Click on the au_select stored
procedure; this identifies the primary means of obtaining information from the
data layer (see Figure 1).
-
The next page asks which field is the primary key. Change the value of the
Primary Key column for the au_id row from No to Yes.
-
Skip the next item - Define Lookup Table Information - for now. We'll come back
to this a little later.
-
The next three pages ask which stored procedures are to be used to Insert,
Update, and Delete data. This is where we select the remaining three custom
stored procedures that we added earlier.
-
The final data-gathering page asks you what name you would like to give to the
class; enter Publishers, and then click the Finish button.
Figure 1: Selecting the primary source of data
Although we would expect to see a single class created, the
first use of this wizard with the context of a project generates two classes.
The expected class - actually called rsclsPublishers - contains the code for
manipulating the table, including references to the various stored procedures
that we supplied to the wizard. The other class - clsDOW - is required to exist
if one or more DOW-generated classes exist and contains the definition for an
enum type called EnumSaveMode. This feature offers the ability to globally
change the way that ADO saves the data. If the value is set to Immediate then
the LockType is set to Optimistic and data is saved when moving the pointer to
another RecordSet record. The alternative value, Batch, sets the LockType to
BatchOptimistic and determines that data is only saved when the Update method
is called.
Space doesn't permit me to reproduce all of the code that is
automatically written into the rsclsPublishers class, so I'll just mention the
areas that I feel are important. The class contains Get/Let properties for each
of the fields specified within the au_select stored procedure, and the expected
complement of AddRecord, Delete, Update, and MoveNext/Previous/etc methods. The
class also offers four events: ClassError (for general application errors),
DeleteRecord (after a record has been deleted), rsMoveComplete (when the
RecordSet Move operation has completed), and rsUpdateEvent (when the rsUpdate
method has completed). Once this code has been generated Visual Basic doesn't
make any attempt to update it later if anything changes so you can safely
modify it if you need to. For example, the few places where there is error
handling within the class merely passes the problem further up the call chain
so this could be a candidate for adding your own, more sophisticated handler.
The only caution that I would add to this ability to change the code is to make
sure that your underlying data model is stable beforehand. If the stored
procedures that the class is based upon are altered then you will probably need
to rerun the wizard in order to recreate the class, thereby losing your
modifications. The alternative is to manually alter the class code yourself.
I deliberately skipped past the Define Lookup Table
Information so as not to cloud the main thrust of the discussion. Returning to
this matter now, its purpose is to resolve foreign key relationships to other
tables. For example the authors table, which of course predominantly deals with
American-format addresses, has a field called state. At run time this field
would display the two character short-form of each state, such as CA for
California. If the data model were to be altered such that a states table was
created which included the short-form field as the primary key and the full
name as the other field then the Define Lookup Table Information facility would
allow the full state name to be obtained and displayed at run time.
Creating a User Control object
Once the definition has been completed you can return to the
wizard and choose to create a User Control that uses our data class as its data
source. As before, load the VB6 Data Object Wizard and then:
-
Select the second option, "A User Control object bound to an existing Class
object".
-
You will be presented with a similar data source screen as shown in Figure 1,
but this time displaying the rsclsPublishers class that we have just created
(see Figure 2). Click on this class and proceed through the wizard.
-
You are asked to choose the underlying control that you wish to base your User
Control on. This can be a single record (really a collection of text boxes by
default), a data grid, a list box, or a combo box. For the purposes of this
example choose the single record option.
-
You are presented here with a list
of the fields that will be displayed, along with the facility
to change the type of control used for each field. The default
control is a text box.
Figure 2: Selecting the class that will act as the data
source for the user control
Once you have submitted a name for the control and pressed the
Finish button you will see that the new User Control item is displayed on the
main Visual Basic toolbox (Figure 3). If you have several of these in a project
then the tooltip will tell you which is which. In our particular case accepting
a default control name of Publishers actually produces a User Control called
uctPublishersSingleRecord1.
Figure 3: The new user control appears on the toolbox
Now that the control is ready for use you can then drag it
over to a form as usual. Figure 4 shows a basic form with an instance of the
User Control, together with a couple of simple navigation buttons that I added.
The Previous button, for example, simply contains the code
uctPublishersSingleRecord1.MovePrevious
to perform the basic navigation.
Figure 4: A form making use of the new user control
Usefulness?
The ability to create a User Control object which can be dragged onto a form in
order to display data might seem a little redundant in light of last month's
column in which I performed the same thing with the basic DED. The difference
comes with the flexibility of the two approaches. The DED method involves a
number of controls that are bound to a straightforward Recordset object so if a
simple data display is required then this would probably suffice. The DOW-based
approach, however, gives you the code for both a data class and a User Control,
thereby giving you the starting point for a much greater degree of
customisation. Whether the quality and nature of the code is sufficient depends
upon your own needs - the initial code certainly wouldn't be robust enough for
many corporate applications. One key drawback would be the lack of any support
in the initial code for transactions. Some companies have created their own
version of this tool for use in the creation of corporate business objects that
will produce code that adheres to local coding conventions and error handling
methodologies. This is probably a course of action that should be debated in
development departments of any size.
|
Loading the Add-In
Assuming that your Visual Basic 6 installation performed OK
then you should find that the Add-Ins management dialog contains the VB6 Data
Object Wizard entry, so you should set it as "Loaded". If, however, you are
running from a different user profile than the one that the VB installation was
performed under then you will find the list of Add-ins curiously depleted. The
reason for this is that the installation program writes most configuration data
into the HKEY_LOCAL_MACHINE hive of the registry (which is therefore usable to
all profiles), but the Add-Ins data is written to HKEY_CURRENT_USER. If you
can't see the Add-In then perform the following steps:
Open a Command Prompt window, and change directory to
<Visual studio root>\VB98\Wizards.
Run the command
regsvr32 msdatobj.dll. This will reapply the
registry entry. While you're in here you might like to also run this command on
the other DLLs.
|
