Ado Examples and Best Practices
Keeping this in mind, I wanted to find a way to get just the rows I wanted, but still let the Shape provider do its thing—providing the one-to-many relationship management in memory and still providing updatability for those (albeit rare) cases where I want to update through a cursor.
First, it was clear that I needed to limit the rows fetched by the initial "parent" query. Once this is done, we can selectively fetch the child rows on demand—or can we? Well, it turns out that you can, but you have to use another feature of the Shape provider that we'll talk about next—an index to cross-connect the parent and child.
So, limiting the initial parent query seems easy enough—just provide a WHERE clause. But, we also don't want to hard code a WHERE clause (in most cases), so I tried to create an ADO Command object that would generate the Shape Recordsets. Forget it, it didn't work. However, I did get the old-reliable "concatenate-on-the-fly" technique to work. By limiting the authors query to return only a single author, I forced ADO to refetch each time I wanted to see another author. You could also set up a query that returned only a selected group of parent rows—say, just the authors from California. We'll talk about using stored procedures to deal with this problem in a minute.
However, even when we fetch a single parent row, the child query is still returning all of the rows from the TitleAuthor table, not just those that match the parent. I figured the Shape provider must have a way to query the child rows only as needed, and it does. If you rephrase your Shape statement to pass a parameter from the parent query to the child query, ADO and the Shape provider change their strategy to fetch the child rows on demand. Here's how the parameter-based syntax looks:
rst.Source = "Shape {select au_id, au_lname, au_fname, city, state, zip" _ & " From authors where state ='" & txtStateWanted & "'} " _ & " APPEND ({select * from titleauthor where au_id = ?}" _ & " RELATE au_id TO Parameter 0) AS chapTitleAuthor"
| Note | The fetch-all-the-rows-at-once approach might be required for situations in which you can't depend on a persistent connection, as with Web pages or middle-tier components. But this does not mean you must fall back on the open-ended queries. There are still ways to write queries that only fetch "just enough" data. |
I capture the state wanted in a TextBox control (see Example3)[1] and concatenate it into the query when the user presses the Search button. The code ties the au_id in the parent query to a "?" in the child query in the RELATE clause.
Can you simply execute a stored procedure instead of the SELECT statement(s) in the parent and child queries? Sure. See Example4 for a working example.
rst.Source = "Shape {execute GetAuthorsByState '" & txtStateWanted & "'} " _ & " APPEND ({select * from titleauthor where au_id = ?}" _ & " RELATE au_id TO Parameter 0) AS chapTitleAuthor"
[1]All of the examples mentioned in this chapter are located on the companion CD. This chapter's examples are located under ..\Sample Applications\Hierarchy and Shape\Shape.
| Team-Fly |