Ado Examples and Best Practices

The Data Object Wizard was created to address many of the more complex issues of data query and user interface design. Developers told Microsoft that binding to base tables did not usually make a lot of sense. In their real-world applications, they wanted to bind to more sophisticated parameter-based queries—especially to stored procedures. That's because the result sets generated from their queries were rarely derived from a single table. Results were generated from two to many table joins, based on parameters passed to the query.

This is where the Data Object Wizard comes in. It was designed from the outset by approaching the problem from a very different point of view, when compared to the Data Environment Designer or a traditional data source control. Because it is a "wizard," the design time code generates Visual Basic source code that developers can tune, cajole, or take as is. Because the wizard generates clearly understood (albeit voluminous) source code, the developer has the flexibility to add his or her own logic to any stage of the data query operation. In addition, because the process constructs a user control that can be compiled to binary, other developers can easily share the control or the source code to access it.

Phase One—Getting Ready

The first phase of Data Object Wizard data frontend development does not involve the Data Object Wizard. This is when you plan how to access the data in question and write the procedures to do so. In many cases, these procedures might already exist. For example, to update the Publishers table in the sample Biblio database, we created several queries:

• A parameter-based query to fetch the publisher(s) to manage.^[3] In this case, I created a stored procedure that fetched publishers from a specific state. This rowset is used to populate a grid control in this example, but this query might return a single row to update.
• A parameter-based query to update a selected publisher by its unique PubID value. This routine is also implemented as a stored procedure that accepts the new column values and a PubID to locate the row to update. The procedure can handle collisions or other contingencies on its own, or it can leave this up to ADO if desired. You also have the option of indicating that updates are simply deletes followed by inserts.
• A parameter-based query to insert a new publisher. This routine is also implemented as a stored procedure that expects the new column values and a new unique PubID. You can also indicate that the inserted row has an Identity column to automatically provide the unique index. The routine also handles collisions or other contingencies on its own, or it can leave this up to ADO if desired.
• A parameter-based query to delete an existing publisher. This routine is also implemented as a stored procedure that expects a unique PubID to delete.
• A query to select all of the rows from the ValidStates lookup table. This is simply a Table-based query.

After these queries are created, you have to make them visible to the Data Object Wizard through the Data Environment Designer. Yes, the Data Object Wizard depends heavily on the Data Environment Designer to open connections, execute the queries, and manage the Recordsets returned.

Phase Two—Creating the Recordset and DataSource Classes

After the queries are defined, it's time to run the Data Object Wizard for the first time. During this phase, you simply point the Data Object Wizard at the procedures we have already defined. You also indicate the primary key that's used to uniquely identify the specific row to insert, update, or delete. You can also provide pointers to "lookup" procedures to aid your user when selecting coded fields such as the state code. When this phase is done, the Data Object Wizard has created two new files in your project to hold the new classes.

Phase Three—Creating a Custom User Control

When the DataSource class is completed, it's time to run the Data Object Wizard again. This time you describe how you want your data class to be presented to the user. You can choose among a grid, textboxes, or combinations thereof. When this phase is done, your project is nearly ready to run. At this time, you can add code to provide the initial parameter for the fetch query, or simply provide it in the properties dialog boxes.

If you don't like the code the Data Object Wizard generates, you can simply change it. Ripping out the DataEnvironment might be more work than you're comfortable with, but it's worth considering if you think you need the extra degree of control this change might provide.

^[3]The Biblio database is included on the CD and it contains four stored procedures (described here) that manage the Publishers table. These are designed to be used with the Data Object Wizard.