Database Advances Define VB6

Choose the Run option from the Query menu to see the results of your query in the Results pane of the Query Designer. If the results look correct, close the MDI child window that's hosting the Query Designer. When VB prompts you with the message "Save changes to the query 'comAuthors'?", click on Yes. You'll notice that the icon next to comAuthors has changed to the SQL icon and that a plus sign indicates the branch can be opened. Open the branch, and you'll see child branches for each field returned by the command.

One important feature of the Command object property sheet is its ability to let you specify a stored procedure in the General tab, and to supply parameter input values in the Parameters tab. In my testing, I found that commands built on ODBC-based connections provide you with a populated list of available stored procedures and then automatically fetch all the parameter names for which you must supply input values. Anyone who has written the code necessary to bind an ADO command and its Parameters collection to a stored procedure will immediately appreciate this feature. Also of interest are the Grouping and Aggregates tabs, which handily generate SQL GROUP BY clauses. The Advanced tab lets you control the type of ADO Recordset the Command object creates and how the command is executed.

Navigate the Hierarchy
You're doing well so far, but you have more work to do. Right-click on comAuthors and this time choose Add Child Command. A command object called Command1 appears after the last field object ("contract"); change the child command's name to comTitlesAuthors right away. Now right-click on comTitlesAuthors and configure it to execute this query:

SELECT titleauthor.au_id, titles.*
FROM titleauthor INNER JOIN
   titles ON titleauthor.title_id = _
      titles.title_id

When you're done, go back into the comTitlesAuthors property sheet and click on the Relation tab. The parent command combo box should already be displaying comAuthors; if it's not, then configure it to do so. Next, make sure the Parent Fields and Child Fields/Parameters combo boxes both display au_id; click on the Add command button, then click on OK.

Congratulations! You've created your first hierarchical command object. To see the SQL that the Data Environment designer built for you, right-click on comAuthors and choose Hierarchy Info... from the context menu. A popup window displays the SQL-like SHAPE command used to generate the recordset, assuming the View Shape Command radio button is selected.

To enjoy the fruits of your labor, open Form1 and set its width to 5800. Open envMain and choose Window | Tile Vertically from VB6's menu. Drag the comAuthors object from the Data Environment window to the top center of Form1. When you let go of the mouse, you'll see something impressive: bound controls with accompanying labels for each field in the parent command, and a bound MSHFlexGrid (the reference to which was added to your project automatically) to display the child command's resultset. This is ready to run; click on the Start toolbar button to see for yourself.

New Data-Bound Properties
When you look at each control's properties, you'll notice that the DataSource property of each bound control is set to envMain, our DataEnvironment object, proving that objects other than data controls can act as valid DataSources. Now remember, a Data Environment can contain many commands, so simply indicating envMain by itself as the DataSource is ambiguous. That's where the new DataMember property comes in. It indicates which object within the DataSource the control should be bound to. You'll notice that the MSHFlexGrid's DataMember property is set to comTitlesAuthors, and that all the other controls are set to comAuthors.

Here are some more quick facts: The DataFormat property lets you tell VB how to format your data, reducing the need to use third-party masked-edit controls. The CausesValidation property, when set to True, causes the control's Validate event to fire when the control loses focus. In the Validate event procedure, if the data fails your validation test, you can set the Cancel parameter to True, in which case the control retains focus automatically. This is cleaner than writing code in the LostFocus event. You can set the CausesValidation property to True or False at run time to enable and disable that code dynamically.

One more trick before you move on: Add a new form to the project, place a single MSHFlexGrid on it, and size the grid to fill the form's client area. Set the DataSource property of the grid to envMain, and the DataMember property to comAuthors. Change your startup object to this new form and run the application. You might need to scroll right to appreciate fully what this control does: It displays the titles information to the right (and below, in the case of multiple titles) of the author information and provides Expand/Collapse buttons to the left of each author. The combination of the Hierarchical Grid control and ADO hierarchical recordsets is powerful indeed.

Roll Your Own
So far, you've written no code whatsoever. I know what you're thinking: "That's great for demos, but real programmers don't use bound controls." You'll see later in this article why that's no longer true; meanwhile, let's look at how you can write code to work with the Data Environment commands you've set up. Let's start with something simple: a MoveNext button on Form1. Add a command button somewhere on the form, and place this line of code in the Click event procedure:

envMain.rscomAuthors.MoveNext

Feel free to add similar buttons and code for MoveFirst, MovePrevious, and MoveLast functionality. Now run your code (set the startup object back to Form1 first) and see how well it works. If you're comfortable with that, you can work with rscomAuthors without using bound controls at all. Just make sure you call its Open method (with no parameters) before trying to read or manipulate it. What if you want programmatic access to fields in the comAuthors' child recordset (the one created by comTitlesAuthors)? It's not difficult. Simply access the child recordset as if it were a field of the parent recordset. Use this line of code to display in a message box the first title belonging to the current author:

MsgBox _
   envMain.rscomAuthors.Fields( _
   "comTitlesAuthors").Value. _
   "Fields("Title").Value

Note that the Value property at the end of the line (on Fields("Title")) is optional, but explicitly referencing the Value property of Fields("comTitlesAuthors") is absolutely required.

You might have noticed a rule here: The recordset opened by a command can be addressed as a property of the DataEnvironment object where the property's name is "rs" concatenated with the name of the command object ("rs" + "comAuthors" = "rscomAuthors"). You can also refer to the recordset through the DataEnvironment object's Recordsets collection, which is 1-based. Addressed in this fashion, our recordset would be envMain.Recordsets("comAuthors") or envMain.Recordsets(1).

By the way, if you're really brave, you can create hierarchical recordsets on your own. The SHAPE statement for the recordset in this example is the one that was displayed in the Hierarchy Info popup; it uses the new SHAPE command to create the hierarchical recordset. In order to use the SHAPE command, you must use a special OLE DB service provider named MSDataShape.1 in combination with the data provider normally required (SQLOLEDB.1 in the case of SQL Server). If you interrogate the value of conPubs' Connection String (envMain.conPubs.ConnectionString), here's what you'll see:

Provider=MSDataShape.1; _
   Persist Security Info=False; _
   Connect Timeout=15; _
   Data Source=TEST;User ID=sa; _
   Initial Catalog=pubs; _
   Data Provider=SQLOLEDB.1

The Provider and Data Provider clauses reference the two OLE DB providers mentioned before. The Data Source clause selects TEST as the SQL Server (this is similar to the SERVER connect string clause the SQL Server ODBC Driver uses); the Initial Catalog clause selects Pubs as the database (similar to the DATABASE connect string clause the SQL Server ODBC Driver uses). The Persist Security Info and Connect Timeout clauses are not strictly required.

Data-Aware Classes
I promised I'd explain why data binding has now entered the domain of "real" programming. The objection to data binding in the past pointed out that it was, by nature, a two-tier technology. It worked on the premise of binding UI elements directly to the data services tier, bypassing any middle tier that might have been in place. With VB6, that no longer holds true. Now you can automatically bind properties of a class to fields in a database resultset (including one from a Data Environment command). This means you can use data binding to attach business objects to databases. Perhaps more significant is that VB classes can act as data sources. This means that UI elements can bind to recordsets returned by classes and don't need to talk directly to a database to get data. In fact, the recordsets returned by classes don't even need to come from a database; you can create them and their records entirely in code.

The ActiveX components that make all this possible are called the Microsoft Data Binding Collection and the Microsoft Data Source Interfaces. To use "simple bound" data-aware classes, you must add (in the References dialog) the first of these two libraries to your project. You'll need the second one for "complex bound" operations (where your class provides several recordsets), and its clients use their DataMember property to pick one.

Next, add code required to create a Data Consumer class that binds to the authors table in the Pubs database, though for reasons of space, only one property will be implemented: FirstName (see Listing 1). The Class_Initialize procedure contains most of the magic code. A recordset is opened (it's private and inline SQL-based, but you could use a DataEnvironment recordset as well), and the BindingCollection object's DataSource property is set to it. Next, use the Add method of the BindingCollection object to bind the class's FirstName property to the au_fname field of the recordset. This ensures that whenever the recordset is navigated, the Property Let FirstName procedure is always called and passed the current value of au_fname in its szNewValue parameter.

VB6	An Educated Consumer

Option Explicit Private WithEvents rsAuthors As Recordset Private bclMain As BindingCollection Private mszFirstName As String Private Sub Class_Initialize() Set rsAuthors = New Recordset Set bclMain = New BindingCollection rsAuthors.Open "select * from authors order" & _ "by au_fname", "Provider=SQLOLEDB.1; " & _ "Data Source=TEST; User ID=sa; " & _ "Initial Catalog=pubs", adOpenKeyset, _ adLockOptimistic Set bclMain.DataSource = rsAuthors bclMain.Add Me, "FirstName", "au_fname" End Sub Public Property Get FirstName() As String FirstName = mszFirstName End Property Public Property Let FirstName(ByVal szNewValue As String) mszFirstName = szNewValue If rsAuthors.Fields("au_fname") <> szNewValue Then rsAuthors.Fields("au_fname") = szNewValue End If End Property

Listing 1 The code in this listing is for a simple Data Consumer class for the SQL Server Pubs database authors table. For simplicity, only one property (FirstName) is implemented and it's bound directly to the au_fname field in the table. When the recordset is moved to a different record, the Property Let procedure is executed automatically and the property value is updated. If a client of the class writes to the property value, the code inside the If block in the Property Let propagates that change to the field. Notice that it's not necessary to call the underlying recordset's Update method.

The If...End If block of code in the Property Let ensures that if you change the value of the property in the application, the recordset updates accordingly. If you wish to build a Data Consumer class on a nonupdatable recordset, simply declare the recordset using the WithEvents keyword. You can put code in the Recordset_WillChangeRecord event procedure to intercept the change yourself, setting the adStatus parameter to adStatusCancel when you're done. The code in Listing 1 works only if you set the class's DataBindingBehavior property to vbSimpleBound.

Next, implement clsAuthorSource, a Data Source class that wraps the authors table (download Listing 2). I gave the class a Cycle method to navigate through the resultset. Create a form whose controls will bind (for reading and writing) to clsAuthorSource as their data source, along with a command button to call the Cycle method (download Listing 3). With a little work, the code in clsAuthorSource could be ported to a UserControl object, allowing the data binding to occur at design time instead of in code in the Form_Load. Remember, the recordset that clsAuthorSource returns doesn't have to come from a database; you could create a recordset in code and make anything look like a data table. In such a case, you're writing a data provider of sorts. Wish you could write a full-fledged OLE DB Simple Provider that any OLE DB client could use? Well, I can't cover it in depth in this article, but rest assured: It's documented and doable. The code in Listings 2 and 3 works only if you set clsAuthorSource's DataSourceBehavior property to vbDataSource.

Time is short, so I can only briefly tell you about VB6's new built-in Data Report Designer. The good thing about this Access Report Writer-like tool is that it's small and compatible with VB6's data-binding plumbing, including the ability to drag and drop Data Environment commands and fields. Implemented as an ActiveX Designer, it's ready for you to insert VB code into any of its nine event procedures. On the other hand, its reporting capabilities are crude compared to those of its competitors. Given that Seagate/Crystal and Data Dynamics both have ActiveX Designer-based reporting tools available, it might not be time to switch just yet.

That's about all I have space to cover. As you can see, VB6 brings a lot to the table (no pun intended) in terms of new features for writing robust n-tier database applications and server objects. Much thought has gone into simplifying developers' lives and facilitating their transition to ADO and OLE DB. Get your hands on VB6 as soon as you can, and set aside some time to experiment with all these new capabilities. After you understand them, they might bring fundamental changes to the way you write applications.


Andrew J. Brust is president of Progressive Systems Consulting Inc., a New York City-based firm specializing in the development of, and developer training in, Internet/intranet, client/server, and other custom business applications. Reach Andrew by e-mail at abrust@progsys.com or visit the Progressive Systems Consulting Web site at http://www.progsys.com.