The Shape Provider | Ado Examples and Best Practices

Team-Fly

As we discussed earlier, the idea behind OLE DB is that it is supposed to handle any kind of data—from flat, unstructured data, to relational data, to complex object-oriented data sources and everything in between. The Shape provider is one of the new features introduced in ADO 2.1 that extends OLE DB functionality. As we'll discover later, the Shape provider is used in much the same way we use a cursor library or other OLE DB "providers." As with a cursor provider, you still need a data provider to fetch the rows—this means the Shape provider can work with virtually any data source. Basically, the Shape provider manages hierarchical data for data sources that don't know how to do it, or for developers that don't know how to get their data providers to return hierarchical data. It fetches rows to the client, organizes them in related groups, and exposes them to display controls (such as the MSHFlexGrid) or to reporting engines (such as the Visual Basic 6 Data Report engine). The Shape provider, like a cursor provider, supports updatability when possible. It also supports a variety of aggregate functions to help summarize data or simply provide computed columns.

Who's using the Shape provider here at Microsoft? Well, a number of internal applications use it. As I'll discuss later, these applications use relatively small databases (one database fits on a floppy) and many use the Shape provider with Web applications. Some developers see the Shape provider being useful anywhere the user has data with hierarchical relationships and the native data store does not support hierarchies. That would include most relational data stores.

Hierarchies are useful in many places where joins are currently used. That is, while a join returns the related rows, no implied relationship is built into the structure that's returned. Developers have asked for a way to program against a hierarchical object model to expose their join relationships rather than having to crunch a flattened denormalized joined set of records. The Shape provider provides this capability by keeping data normalized on the client. It also provides a consistent programming model for doing this against any backend. The fabricated rowset capabilities in Shape also provide a powerful tool for creating temporary tables and hierarchies without reference to any permanent data store. If you want to see where Shape fits, talk to the Access and Data Page folks in Office 2000—they used Shape extensively.

Note

When I started researching the Shape provider, I discovered that there were many articles written about it, but none seemed to address my issues. That is, most of the examples use unbound queries against tiny databases, such as SELECT * FROM Authors in the sample Pubs database. I did find a useful article "Shape Up Your Data Using ADO 2.0 Hierarchical Recordsets in ASP" on the MSDN CD that built parameter queries, but this article discusses a Web-based application, and I usually focus on client/server Visual Basic applications.

The focus of this chapter is with the client/server and large database developer in mind. As I got deeper into the Shape functionality, it became clear that the provider was meant for smaller, table-based databases—especially if you use the default behavior. Too many of the Shape provider's functional assumptions (and defaults) made it unsuitable for sizeable databases—or databases that can generate complex hierarchical result sets on their own. SQL Server, for one, can easily return a Recordset based on a single or a set of parent rows and all associated child rows—to any depth necessary—with a single call to the server. However, not all OLE DB or ODBC providers are capable of this level of sophistication, and simpler providers have no way to return these hierarchies. However, the Shape provider is more than just a way to return Parent/child hierarchies. It can also be used to construct, compute, and manage more complex aggregate functions that are used when you create reports.

When I worked on Microsoft's Visual Studio Marketing team, I demonstrated the Shape provider any number of times. After about a minute of flashy clicking and dragging, I ended up with a three-table hierarchy that looked something like Figure 10-1. This is really pretty easy. Just drag the three tables from the Data View Window over to the Data Environment Designer window and set the Relation property (on the Properties page) to cross-reference the au_id and title_id fields. I dragged the parent object (the "authors" command in this case) over to a form and chose the MSHFlexGrid to display the rows. No, I'm not going to walk you through this again. That's because I want to show you an easier way that requires a few lines of code but gives you a lot better performance and far more control over your results. If you want to see the demo, stop me in the hall at VBits sometime.

click to expand
Figure 10-1: Hierarchy constructed by Data Environment Designer

Team-Fly