Using the Data Environment Designer

Team-Fly

---

The Data Environment Designer (shown in Figure 12-3) seemed to show a lot of potential—at first. However, as the development community and I waded into its functionality, the crooked seams started to show. I expect the Data Environment Designer was intended to replace and augment the functions and features of the User Connection Designer. Let's look at the Data Environment Designer's laundry list of features:

Figure 12-3: The Visual Basic 6.0 Data Environment Designer with Four Commands

• Provides a way to capture one or more OLE DB connection strings and automatically establishes appropriate connections at design and run time.
• Provides a way to easily author queries of all types at design time using an intuitive GUI.
• Persists these queries in a form that could be shared among an entire development team.
• Provides an interface to ADO that exposes underlying ADO Connection, Command, and Recordset objects and their properties.
• Exposes an event interface to map to underlying ADO events and helps manage synchronous as well as asynchronous operations.
• Manages input, output, input/output, and return status arguments passedto stored, as well as ad hoc, procedures.
• Manages the construction of ADO Shape statements, and displays hierarchical data structures.
• Acts as a "data source" control so that bound controls can access the data returned from the queries without writing code.
• Supports intuitive drag-and-drop functionality to make it easy to set up bound control forms and reports based on the DataEnvironment data source object.

Now let's take a closer look at the Data Environment Designer and how it manages (or at least attempts) to do all of this.

Data Environment Designer—Basic Techniques

There are several ways to use the Data Environment Designer. Many developers tell me that they find adequate functionality in the basic techniques, but that they stay away from the rest. Others say that the way the Data Environment Designer manages shapes and hierarchical result sets makes development of reports and complex shape operations far easier. I'll let you judge for yourself. The Data Environment Designer is also a "must-use" component of the Data Object Wizard, which constructs procedure-based applications and custom data-bound components based on Data Environment Designer Commands.

You can use the Data Environment Designer to create a persisted Connection object very simply—just drag a Table, View, or stored procedure from one of your existing Data Links (created with the Data View window) over to the Data Environment Designer window. This operation also creates a DataEnvironment Command object that returns the result set from the table, view, or stored procedure. However, you don't want to drag a table to the Data Environment Designer—not without clarifying the query—because the DataEnvironment runtime will construct an ADO Command object that will return all rows and all columns from the target table.

Using the Query Designer Window

To correct this problem, click on the newly created Command object and right-click to change its properties. Click on SQL Statement in the source of data frame and click on SQL Builder. This launches the four-paned query editor as shown in Figure 12-4.

Figure 12-4: The Data Environment Designer's query editor

It's essential that you pare down the query to return just the required rows and columns from your table(s). It's easy to use the Query Designer window—just drag the tables needed from the Data Environment Designer or Data View window. The SQL query is created for you in the third pane, and you can select the required columns in the second. The top pane graphically shows the join relationships. If you right-click on the first pane and choose run, the Data Environment Designer will run the query and fill the fourth pane with the Recordset returned. Change any of the panes, and the changes are reflected in the other panes automatically. Very cool.

When you save your application, or just the .DSR file generated by the Data Environment Designer, the designer saves all of the parameters, properties, and other settings you provided when dragging and dropping and setting property page values. All of these values are stored in the binary (proprietary) DSR file and become a part of your project. The DSR file is processed again at runtime by the Data Environment Designer runtime engine, which reconstructs the DataEnvironment objects that you specified at design time. The DSR file can be shared with others in your development team. They can simply add the DSR to their project and then simply refer to the DataEnvironment object Connections and Commands as if they had constructed them on their own.

If you save the Data Environment Designer-generated .DSR file at this point and run your program, the Data Environment Designer runtime engine simply instantiates the DataEnvironment object and nothing else happens. The connection is not opened and the query is not executed. However, if you take the next GUI step and drag your Data Environment Designer Command object to a form, the designer creates a set of controls (right-click and you get to choose) and binds the Command object to these controls. However, there is no accommodation for input parameters for your query. In this case, when you run your program, the DataEnvironment object behaves (or misbehaves) like a Data control and starts to run before Form_Load—long before your error handlers get a chance to start (as if they did any good).

To run the DataEnvironment Command object against this connection without binding, you have to write a little code. To make it easier to code, I like to dereference the DataEnvironment object. Besides that, there's only one line of code required to run the query. Let's assume I have created a DataEnvironment Command object that executes a stored procedure called MySP. I created this Command object by dragging the stored procedure from the Data View window to the Data Environment Designer window. The MySP stored procedure requires three parameters, as shown in the following code, which executes this stored procedure at runtime:

 Dim DE as DataEnvironment1 Set DE = New DataEnvironment1 DE.MySP "cat", 5, 32.5 

The ADO Recordset resulting from this query is returned in an object exposed off of the DataEnvironment object and named after the query. In this case, the Recordset is returned in an object named rsMySP. However, to prevent losing it once we reference the Recordset, we have to create a separate Recordset variable to manage it.

 Dim rs as Recordset Set rs = DE.rsMySP 

To rerun the query, close the Recordset and simply use the same code (DE.MySP...), as shown previously, with new parameters. No, there is no Refresh method.

 DE.rsMySP.Close DE.MySP "dog", 8, 45 

Data Environment Designer Connection Error Handling

Note that none of this code explicitly opened the connection. This was handled behind the scenes by the DataEnvironment object runtime when the DataEnvironment Command is referenced for the first time. So, what if the connection cannot be established? Well, just as with ADO, you have to set up your own error traps to catch these not-so-unlikely errors. If the DataEnvironment gets in trouble when connecting, one of the first strategies it uses is to reprompt the user with a dialog box asking for the UserID and Password, along with options to change to another database. Unfortunately, this prompting behavior does not pay any attention to your Visual Basic error handler. This means that the user gets to guess at the password (if this is the problem) until they get bored or get it right. And the problem might not be the password at all. I simply paused the server and triggered this error scenario.

This is why it's essential that you tell the Data Environment Designer to let you handle the errors in your own code. It's not obvious how to do this, but it can be done:

1. Open the Data Environment Designer window.
2. Click the Connection icon in the Data Environment Designer window. You'll have to repeat this for each Connection in the designer.
3. Do not right-click to access the Connection properties—that's what most folks try, and it won't work.
4. Do press F4 to open up the VBIDE Property Page—the DataEnvironment object's Connection property page should appear (see Figure 12-5).
5. Change the DesignPromptBehavior and RunPromptBehavior to adPromptNever. Yes, you may be prompted several times in the process of changing this property. And no, you can't change it at runtime—it's too late then, and the property isn't exposed anyway.

   
   Figure 12-5: The Data Environment Designer Property page where you set prompt behavior

What's Missing in the Data Environment Designer

While the Data Environment Designer goes a long way toward addressing development problems, it seems to paint itself in a corner on many of these functions. Unless you go to a lot of trouble, you're likely to revert back to raw code. Let's look at what's missing or simply broken:

• There's no facility to "bind" the parameters—input or output. When you drag the DataEnvironment Command object to a form, the Data Environment Designer instantiates a grid or set of labels and text box controls for each Recordset Field that returns from the query. However, there are no controls created to handle the input parameters of your procedure, not to mention the output parameters or return status.
• Accessing the Command parameter collection is easy when executing the command as a method on the DataEnvironment object. However drilling down to a specific Command object's Parameters collection (somewhere in the DataEnvironment Commands collection) requires far more code than managing Command objects in ADO code.
• There's no refresh method on the DataEnvironment object or the Commands associated with it. This means that once you have run the DataEnvironment Command with one set of parameters (that you provide by hand), you can't simply change the parameters and reexecute the Command—at least not if you expect the bound controls to reflect the new Recordset.
• When using simple bound controls (such as TextBox controls) you also have to manually rebind on each query. For instance, if you try to run a parameter query, you have to close and rerun the DataEnvironment Command object from scratch for each iteration. While you can rerun the query from scratch, the DataEnvironment Recordset to form control data bindings are lost as soon as you close the DataEnvironment Recordset object, as the DataEnvironment Recordset object is required to rerun the query. This means the coded connection between the columns (Fields) returned in the DataEnvironment Recordset and the bound controls on your form are dropped and have to be reset for binding to work. Rebinding requires that you revisit each bound control and reset the DataSource and DataMember properties manually in code.
• When the Recordset returns from the DataEnvironment, it exposes a unique "touch-me-once" object. That is, once referenced, it cannot be referenced again. The rowset is dropped by the DataEnvironment runtime to prevent instance memory leaks. This means that you have to capture the DataEnvironment object's returned Recordset in a separate memory variable to prevent it from being lost after first reference.
• The Data Environment Designer was designed with and for ADO 2.0. Unless Visual Studio 6.0 SP4 corrects this problem, the DataEnvironment event handlers will no longer compile if you switch references to ADO 2.1 or later. This can be corrected by changing the events you expose to return Recordset20, but you won't be able to access any of the new ADO 2.1 or later Recordset object properties, methods, or events.
• The shape generator creates expensive SELECT * queries (with no WHERE clauses). While this works fine for small databases, it's a problem for databases with more than a few hundred rows. To get around this, you have to carefully construct the queries leading up to the Shape operation, as well as cross-link the queries manually in code.
• The Data Environment Designer is particularly sensitive about changes instored procedures or database schema. Unless your stored procedures are frozen, accessing them with the DataEnvironment object is problematic.
• The default behavior of the Data Environment Designer is to create an expensive updatable, scrollable keyset cursor. In contrast, ADO's default behavior creates an efficient forward-only, read-only cursorless Recordset.
• The default behavior of the Data Environment Designer when it comes to table queries leads inexperienced developers into expensive locking situations. Dragging a table to the Data Environment Designer from the Data View window generates a SELECT * FROM <table> query.
• The default prompt behavior is exactly the opposite of ADO's prompt behavior. This means that the Data Environment Designer dialog boxes constantly and incessantly prompt the developer for the same UserID and password regardless of how many times it has been entered before.
• Trying to get the Data Environment Designer to correct this prompting behavior is about as hard as getting HAL to open the pod bay doors. There is no facility in the Connection dialog boxes to capture appropriate prompting behavior at design time, so it has to be repeated for each Connection object created in a separate properties window. We discussed earlier how to reset this behavior, but remember it can't be done in code—it must be done at design time.
• This default approach to connecting also causes the DataEnvironment runtime to ignore the Visual Basic On Error handling. This means the user can be confronted with data provider prompt dialog boxes regardless of the actual connection problem, requiring you to reset the prompting behavior before using the DataEnvironment object.
• While the Data Environment Designer exposes a property page to modify a number of critical aspects of the Connection and Command objects, these are not exposed to the developer at runtime. This means that you can't modify the prompting behavior, UserID, Password, or most other DataEnvironment object behaviors at runtime.

---

Team-Fly