Defining Access To Data
This column appeared in the January 1999 edition of
EXE, and covered the Data Environment Designer (mainly because few knew it was
there)
In the November edition of EXE I outlined the basics of OLE
DB, the ADO layer, and it’s use in Visual Basic 6. In addition to this new
data access model Microsoft have also provided new or updated tools which are
intended to give the developer a more sophisticated means of defining access to
data sources. In this month’s column I’m introducing the Data
Environment Designer (DED), a useful tool that provides the facility to create
data access definitions at design time.
For any Visual Basic 5 users that discovered the RDO-based
User Connection Designer (UCD) then the DED is intended to be a more
sophisticated ADO-based successor to this tool. If you weren’t even aware
of the User Connection Designer then don’t worry, you weren’t alone.
In fact most developers seem to have missed it, not surprising really because
Microsoft never seemed to do much to bring it to the attention of the
development community. At the keynote address of the German VBITS conference a
while back the audience were asked who had heard of the UCD. Only two hands
went up. This time around, however, the software giant has made a greater
effort in publicising it’s data tools; anyone who attended one of the
DevDays 98 Visual Basic seminars will have been given a demonstration of this
new feature. Because of the general lack of knowledge that exists concerning
the User Connection Designer I will not attempt to provide a comparison with
it’s successor, but will instead start from basic principles. However
anybody who did get to master the UCD might be pleased to know that it is still
provided for backward compatibility.
The DED is a graphical tool that helps you to define
Connection and Command objects based on underlying databases. Once such a
definition has been created then the DED object is recognised as a valid data
source for data-bound controls. Such a definition can include multiple
Connection objects to provide access to numerous databases at different
locations. Although the technology is built upon ADO, which is itself built
upon the highly flexible OLE DB, the ability to connect to sources of data is
limited to relational databases rather than the more diverse forms of data that
OLE DB can provide access to. In the examples that I provide here I have chosen
SQL Server as the source of data although it could just have easily been an
Access database. I chose to stick with SQL Server in order to make the point
that the Command objects can be based around stored procedures as well as
straightforward SQL statements.
Defining a DED
As with all projects it is necessary to plan beforehand what
data you are going to need, how best to structure it, and how you are going to
both store and retrieve it. In this case we can fortunately make use of the
ever-useful pubs database that is installed along with SQL Server. If you are
more familiar with Access, or Oracle, or whatever then you still should be able
to follow the flow of the discussion. Having said that you should be prepared
for minor differences resulting from the use of different data sources.
To make a start with the DED you can either create a new
Standard EXE Project or you can create a Data Project. If this is your first
time I suggest that you create a Standard EXE Project so that you can see for
yourself how to add a DED into a project. To set up the project definition
select Add Data Environment from the Project menu. This action automatically
creates a reference to both the Microsoft Data Environment and to ADO itself,
and consequently you should have a Data Environment window pop into existence.
Notice, too, that a new category folder – Designers – will have
appeared in the Project window.
Within this initial DataEnvironment1 instance there also
exists a default Connection object, as yet undefined. To set appropriate values
either right-click the Connection1 object and select Properties, or just select
it and press the Properties button on the toolbar. This action presents a
dialog that allows you to choose which of the installed OLE DB drivers you want
to use. In this case select the Microsoft OLE DB Provider for SQL Server. The
Next button takes you on to the next tab page, which is concerned with
establishing the actual server connection details. This part of the process is
self-explanatory: select a server from the drop down list, enter login
information, and provide the database name. The useful Test Connection button
allows you to immediately establish whether the information that you have
provided is correct. The Advanced and All tabs allow for more specific
performance tweaks to be made to the connection profile such as timeouts and
packet sizes. Some of this information is also available in the Properties
window, as are the settings for design-time and run-time connection
username/password pairs, should you have a need to differentiate them in this
way. You can also rename your connection object to something more meaningful,
perhaps to include the name of your server – in my case conGalileo.
Having defined the Connection object you can start adding
associated Command objects – while ADO itself does not enforce a strict
hierarchy as RDO did, the DED does impose this kind of structure among its
object types. To create a Command object right click the Connection object and
select Add Command, or choose the Add Command button from the toolbar. As
before you then need to edit the properties for this object. Within the General
tab of this dialog you can choose to use a table as a source of data, or you
can use a predefined view, a stored procedure, or you can define your own SQL
statement. As would be hoped, this dialog comes with a SQL query builder that
allows you drag and drop tables onto a query definition. Existing foreign key
relationships are identified and automatically associated, where appropriate.
Figure 1 shows the query builder in operation where I have specified an
operation involving the titles, titleauthor, and authors tables. Note that the
relevant SQL SELECT statement, including 2 JOIN constructs and an ORDER BY
clause, were automatically created as the graphical definition proceeded. We
can close the query builder window when we are done.
Figure 1: SQL Query Builder
The remaining tabs on the properties dialog allow for a more complex definition
of the Command object. The Advanced tab allows the lock type on the data to be
specified (the default is Read Only), and also allows you to specify whether
the cursor should exist on the client-side or the server-side. After the OK
button has been pressed the resulting DED window is shown in Figure 2, within
which you can see that I have also renamed the command query to the more
appropriate cmdTitles.
Figure 2: Data Environment window
Once the DED definition has been completed the Command object
can be dragged over to a form and dropped, at which time the real power of this
technology can be seen. The form now contains three bound text box fields and
three labels, the latter of which can be freely altered without messing things
up. I’ve also added two navigation buttons. On the assumption that Form1
is still your startup form then invoking a run of the project should cause the
text fields to be instantly populated. The results can be seen in Figure 3. The
lengths of the three text fields are exactly as the DED originally decided, as
are the types of controls chosen for each underlying data type. These default
control types can be altered for each data type by clicking the Options button
within the DataEnvironment window.
Figure 3: The resulting form1, with data!
Figure 4 shows the ADO datatype adDBTimeStamp being changed
from the default TextBox control to the Date Picker control (see footnote).
Alternatively, performing a drag and drop of a Command object with the
right-hand mouse button provides a popup menu at the drop point allowing you to
choose between a data grid, a hierarchical flex grid, or the bound controls.
Figure 4: Changing the default VB control for a specific
ADO data type
The various components within the Data Environment window are
similar to standard Visual Basic controls in that they can have code associated
with them. Double clicking the connection item (conGalileo in my case) displays
the code edit window for this object showing that, in this case, it exposes the
full set of events that you would normally get for a Connection object, namely
BeginTransComplete, CommitTransComplete, ConnectComplete, Disconnect,
ExecuteComplete, InfoMessage, RollbackTransComplete, WillConnect, and
WillExecute. The cmdTitles object displays a similar collection of events, but
interestingly the code object is now called rscmdTitles to highlight the fact
that we are dealing with a Recordset object that has been spawned from the
Command object.
The form in Figure 3 also includes the two command buttons
that I’ve added to navigate through data. Listing 1 shows a minimalist set
of code (i.e. no error handling, as usual) which illustrates this code-based
manipulation. I’ve included the check for the BOF and EOF conditions to
prevent errors being raised from any attempts to navigate off the end of the
set.
Private Sub cmdNext_Click()
If Not DataEnvironment1.rscmdTitles.EOF Then
DataEnvironment1.rscmdTitles.MoveNext
End If
End Sub
Private Sub cmdPrev_Click()
If Not DataEnvironment1.rscmdTitles.BOF Then
DataEnvironment1.rscmdTitles.MovePrevious
End If
End Sub
Listing 1 – Prev and Next button implementations
Now add a little code…
A downside to this drag and drop design is that you don’t
have any control over when the Command object will actually fire. If it is
bound to a set of controls on a form then the Command object will fire when the
form is loaded. This won’t always be a problem, but it could be if, for
example, connection resources are tight. Remember that the version of SQL
Server that is supplied with Visual Studio Enterprise Edition can only have a
maximum of five connections open at any one time.
The Data Environment object that you define can be used
entirely programmatically without the need to drag and drop, and then you can
control when the Command will be fired. The following code fragment shows a
simple example of this programmatic access. Note that the default behaviour of
this model requires you to make a call to the actual Command object before you
can connect to the returned record set instance.
Dim env As New DataEnvironment1
Dim rs As New Recordset
env.cmdTitles
Set rs = env.rscmdTitles
Do While Not rs.EOF
Debug.Print rs(0)
rs.MoveNext
Loop
Set rs = Nothing
Set env = Nothing
The ability to drag fields onto a form in order to create
bound controls is perhaps the most memorable part of the DED technology.
However, the fact that they can be programmatically manipulated means that they
can also be used in middle tier non-UI components just as effectively.
I, Jon Michael Perkins, hereby assert and give notice of my right under section 77 of the Copyright, Designs,
and Patents Act 1988 to be identified as the author of the foregoing article.