Let's get started with the basics by creating a Visual Basic application that uses the Shape provider to do something simple. We'll use Visual Basic 6.0 Enterprise Edition for this exercise—I make no guarantees on the suitability of the Professional Edition. I also have SP3 installed, and the new ADO 2.5 (MDAC) library. I did, however, leave my references to the 2.0 type library—this prevents some problems with the Data Environment Designer caused by the newer versions of ADO. I accessed my local SQL Server 7.0 and the Pubs database to experiment with. I'm glad I did, because some of the sample applications would have taken all afternoon to run if they had a lot of data (more than 20 rows) to query. When you experiment, start with a set of small tables at first—graduating to a larger data store when you get comfortable.
Note | Remember that the Shape provider's default behavior uses two (or more) SELECT * queries (with no WHERE clauses), so do most of the examples in MSDN. That is, the Visual Basic tools generate one open-ended query as the "Parent" command and one additional open-ended query for each "child" command. These are submitted to SQL Server as a single batch. This means that if you don't code otherwise, the Shape provider will return the entire contents of all 'n' tables to your client (or middle-tier server)—or at least try to. |
So, how do you create a simple Shape statement in the first place? Well, you can leverage it from the Data Environment Designer or copy it from my code shown below. I also won't go into the raw syntax of the Shape statement—there are a half-dozen MSDN articles for that. See "Shape Commands in General" in MSDN for starters. Frankly, there's quite a bit there that you won't need to know at all to run the examples I'm about to show you.
Start by setting up some General Declaration variables.
Dim cnn As ADODB.Connection Dim rst As ADODB.Recordset Dim rstTitleAuthor As ADODB.Recordset
Next, open a connection to your favorite SQL Server—one that still has the sample Pubs database loaded. You need to create an ADO Connection object and set its Provider property to MSDataShape before you open it. You can use either the default ODBC to OLE DB data provider or a straight OLE DB provider for SQL Server. This means your Connection string should look just like it always does. Mine references a DSN, but you can use a DSN-less connection.
Set cn = New Connection cn.Provider = "MSDataShape" cn.Open "Data Provider=sqloledb;data source=(local);" initial catalog=pubs;", "admin", "pw"
Once you're connected, you can create a new Recordset and set the StayInSync property to False to prevent extra queries from being generated. I'll talk about this later.
Set rs = New Recordset rs.StayInSync = False
In the next step, you'll have to provide the Shape provider with a correctly constructed query using the Shape syntax. We pass this query to the provider through the Recordset Source property just as we would any query, but consider that the Shape provider does not really have much of a query processor, so stay within the white lines. This does not mean you can't submit complex queries, as the Shape provider simply passes these on to the backend for processing. It does mean that some of the tricks we discussed in the Recordset chapters 6 and 7 won't work here.
rs.Source = "SHAPE {select au_id,au_lname, au_fname", _ Address, city, state, zip from authors} " _ & " APPEND ({select * from titleauthor}" _ & " RELATE au_id TO au_id) AS chapTitleAuthor" rs.Open , cn, Options:=adCmdText
In the first bracketed "{ }" expression after the Shape statement, you need to provide an SQL SELECT statement (or a stored procedure) that returns the "parent" result (row) set. In our example, we'll request all of the rows from the Authors table.
Note | Of course, selecting the entire Authors table is not that clever. No one in their right mind would request all the columns and all of the rows from a production table—unless the table has as few rows as the Pubs demonstration database. We'll fix this later. |
When the parent result set is created, the Shape provider returns it to your application just like any other Recordset. However, the Shape provider tacks an extra Field object on to the end of the Recordset. This means that if your table has eight columns, nine Field objects return for each row. This extra (last) Field.Value contains another Recordset, which contains the rows for the child rows associated with the parent row. This way, each parent row contains not only its own data, but also all of the data for its children as well, and its grandchildren, and its great-grandchildren, ad nauseam. Figure 10-2 shows how each row returned from the Authors table contains an extra field pointing to the row(s) of the TitleAuthor table that correspond to this specific author.
Figure 10-2: Author "parent" rows and associated TitleAuthor "child" rows
The next line in the SQL query to be passed to the Shape provider should be an APPEND statement that tells the Shape provider what rows should be fetched from the child table (TitleAuthor in the example) for each parent row in the Authors table. Here's where we concatenate the "APPEND ({select * from titleauthor}" into our query. Yes, you can have more than one APPEND statement in your Shape query, but let's stick with one child relationship for now. Here's the same SQL Shape statement again with the APPEND clause highlighted:
rs.Source = "SHAPE {select au_id,au_lname, au_fname", _ Address, city, state, zip from authors} " _ & " APPEND ({select * from titleauthor}" _ & " RELATE au_id TO au_id) AS chapTitleAuthor"
The last line in our SQL query is a RELATE statement that tells the Shape provider how to tie the two sets of rows together.
Note | See Figure 10-2 to see how the primary key/foreign key relationships in the Authors and TitleAuthor tables are tied to the au_id column. |
In this case, it's the au_id (author id) field in the two tables that have to match up. This means that for each row in the Authors table, the Shape provider will locate all rows in the TitleAuthor table having the same au_id and create a child Recordset with those rows. We name this relationship with the AS clause in the Shape statement. This name is assigned to the final (extra) Field object on the parent's Recordset so we can refer to it by name.
Tip | Yes, it's the Shape provider that matches these rows—it's not SQL Server, or Jet, or any other backend you happen to be using. This means that the Shape provider has to have these rows loaded into memory (your client system's RAM) to get this to work. |
Once the Recordset returns, you can step through the records in the usual manner. Remeber that you need to immediately complete population of the Recordset. This frees up locks and permits other users to work with the data—especially because we used an open-ended query to build the parent result set.
For purposes of this example, I populated a TextBox control array with the values from the parent Recordset by binding the TextBox controls to an ADODC, which provides updatability and navigation through the parent Recordset. The TextBox controls and ADC were not initialized at design time; we'll set the required properties entirely in code.
' Bind the root Recordset to the text boxes For i = 0 To rst.Fields.Count − 2 txtFields(i).DataField = rst.Fields(i).Name Next I
We set the ADC's Recordset property to the parent Recordset returned by the Shape provider. This fills the TextBox controls and permits us to navigate up and down in the "Select * from authors" rowset.
Set Adodc1.Recordset = rst
As we arrive at each row, we reference the child rowset by extracting the Recordset from the extra Field object tacked on to the end of the Fields returned for each parent row. This Field object is named in the RELATE clause of the Shape statement.
Set rstTitleAuthor = rst.Fields("chapTitleAuthor").Value
Note | Referencing a Field object by name, as shown above, is the slowest possible technique. It can be as much as six times slower than referencing the Field object by number. In a high-performance application, it is better to use an ordinal reference such as rst.Fields(rst.Fields.Count-1).Value. This small change, multiplied by the number of Field object references, can make a big difference in overall performance. |
For this example, I added a MSHFlexGrid to the form to display the child rows. This is easy to do—you simply have to assign the child Recordset to the DataSource property. Once assigned, the grid fills with the current set of child rows.
Set MSHFlexGrid1.DataSource = rstTitleAuthor
Couldn't we have just used the power of the MSHFlexGrid to display both the parent and child rows? Sure. I'll illustrate that technique later. But for now, let's take this a step at a time.
When we use the ADC to position from row to row, we have to remind the MSHFlexGrid about the Recordset to display. We do this in the MoveComplete event, as shown next:
Private Sub Adodc1_MoveComplete(ByVal adReason As ADODB.EventReasonEnum, ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset As ADODB.Recordset20) ' Note we pass in Recordset20 instead of just Recordset. ' Use the faster technique here. Set rsTitleAuthor = rs(rs.Fields.Count − 1).Value Set MSHFlexGrid1.DataSource = rsTitleAuthor End Sub
Note | After you add this code to the ADODC event handler, your application will fail to compile.Visual Basic reports "Procedure Declaration does not match procedure or event having the same name." This is a bug, but an expected one because we are using the ADO 2.5 (or 2.1) MDAC stack. Remember that the Recordset object changed from 2.0 to 2.1 so all later versions will exhibit this problem.We'll have to force the ADODC event handler to accept the ADO 2.0 version of the Recordset object to get around this problem. Notice how I did this by recoding the MoveComplete event handler to expect Recordset20 instead of Recordset. If this is fixed in Visual Basic 6.0 SP4, this should no longer be a problem. |
What we end up with (as shown in Figure 10-3) is a form that shows a single parent row at the top in a number of bound TextBox controls, with the child rows shown below in the MSHFlexGrid.
Figure 10-3: Using bound TextBox controls with the MSHFlexGrid
Team-Fly |