Build Smart With Data Shaping
Data shaping enables you to give your apps end-to-end speed.
by Jeff Jones
October 2002 Issue
Technology Toolbox: VB.NET, SQL Server 2000, VB6
One of the most sought-after goals in data applications is end-to-end speed. You should look for a good balance of speed and development time to achieve this goal. I'll show you how to create more efficient data shaping to make your hierarchical data as lean and as fast as possible.
You'll build a simple project that illustrates how to use data shaping to retrieve data and save changes. You'll use Visual Basic 6, SQL Server, and the Northwind database (see the sidebar, "Perform Data Shaping With .NET," for details on how to create a VB.NET version of this project). The project uses a master-detail relationship as an example of hierarchical data. The hierarchy begins with a list of customers from a specified country, the corresponding orders within a given time period, and each order's details. You'll use parameterized stored procedures (SPs) rather than static SQL, because SPs are more efficient and easier to maintain.
When users select a customer record from the Customers SP (sp_CustomerListByCountry), the application uses CustomerID to relate to the Orders SP (sp_SelectOrdersByCustomerIDDateRange) records for that customer. For each order that results from that SP, the application selects specific order details using the OrderID provided to the Order Details SP (sp_OrderDetailByOrderID).
The traditional method for obtaining the customer, order, and order detail information was to create a single, large SQL statement (see Listing 1). This method does return the desired data, but it has some drawbacks. First, the SQL can get complicated and difficult to debug. Second, the Customer and Order data are duplicated for each Order Detail record, which increases the returned data collection's total size. Finally, you must add code in the presentation layer to separate out the hierarchical data.
You can achieve a better balance of speed and development time by encapsulating the code that manages the SQL code and ActiveX Data Objects (ADO) in a middle-tier object, such as an ActiveX DLL. This provides excellent support for connection pooling, code reuse, and disconnected recordsets. The presentation layer gives this object the SP, and the object returns a disconnected recordset. Whether you choose Active Server Pages (ASP) or a Win32 VB client, you should use a middle-tier object to enhance performance and scalability. The code project provides code for the middle-tier object that supports data shaping. You can install the NorthwindSPs.sql with the Microsoft Query Analyzer or with the isql utility:
isql -Usa -Pxxxx -SmachineName -iNorthwindSPs.SQL -
olog.log -e
Write the Shaped SQL
Data shaping allows you to make a single query and return multiple ADO recordsets that are connected, such as master-detail collections of data. You can write shaped SQL by hand, but the syntax is a bit complicated, and no good environment exists in which to debug it.
You can create shaped SQL more easily by using the Data Environment (DE). Using these same stored procedures, the DE can generate a single shaped SQL statement you can use to build a single, multitier ADO recordset. Generate the source code using the DE: Open VB6, create an empty EXE application, and add a Data Environment. Now, open the DE, and add a connection in the GUI designer.
Next, add a Command object (rsCustomers) to the Connection object. Go to Properties | General and select and set the parent stored procedure, sp_CustomerListByCountry. Select the rsCustomers command object and add the child command, rsOrders. Go to Properties | General and choose the stored procedure sp_SelectOrdersByCustomerIDDateRange. Select the Parameters tab to set some values for the build. Next, select the Relations tab to define what fields relate rsOrders to rsCustomers (CustomerID). After you add rsOrders, do the same for rsOrderDetails (relate OrderID to OrderID), using the stored procedure sp_OrderDetailByOrderID (see the completed hierarchy in Figure 1). You can find the shaped SQL generated by the DE by right-clicking on the parent rsCustomers and choosing Hierarchy Information (see Figure 2):
SHAPE {{? = CALL
sp_CustomerListByCountry( ?) }} AS
rsCustomers APPEND (( SHAPE {{? =
CALL
sp_SelectOrdersByCustomerIDDateRange
( ?, ?, ?) }} AS rsOrders APPEND
({{? = CALL sp_OrderDetailByOrderID(
?) }} AS rsOrderDetails RELATE
'OrderID' TO PARAMETER 0) AS
rsOrderDetails) AS rsOrders RELATE
'CustomerID' TO PARAMETER 0) AS
rsOrders
Note in the edited shaped SQL that you declare the parameters explicitly in the grammar for the sp_SelectOrdersByCustomerIDDateRange SP. This is required in all child recordsets using more than one parameter. Notice also that you should use two curly brackets "{{" where static SQL uses one curly bracket "{":
SHAPE
{{ CALL sp_CustomerListByCountry( 'USA'
) }}
AS rsCustomers
APPEND
(( SHAPE
{{ CALL sp_SelectOrdersByCustomerIDDateRange(
@CustomerID = ?,
@BeginDate = '6/1/1998',
@EndDate = '12/31/1998' ) }} AS
rsOrders
APPEND
({{ CALL sp_OrderDetailByOrderID( ?)
}}
AS rsOrderDetails
RELATE 'OrderID' TO PARAMETER 0)
AS rsOrderDetails) AS rsOrders
RELATE 'CustomerID' TO PARAMETER 0) AS
rsOrders
It's good practice—not required in shaped SQL, but required by the middle-tier object provided with this column's code—to give the same name to columns and parameters used for relating recordsets.
Execute the Shaped SQL
Now that you've created the shaped SQL, you need to execute it using some application to return the data to the presentation layer. You can accomplish this in two ways: One methodology (two-tier) involves using an ADO connection and ADO recordset; the other uses an ActiveX DLL as middleware (three-tier). The sample VB6 project illustrates both methods.
The hierarchical recordset is ready to use. However, there's a little-known problem with this approach. Unlike regular ADO recordsets, you can't disconnect hierarchical recordsets. Even though the code setting the ActiveConnection property to Nothing executes without error, and the Connection object is destroyed, the ADO recordset retains a hidden child Connection object internally. The middle-tier object provided in this project's code eliminates this problem by making a copy of the ADO recordset hierarchy and data that's truly disconnected. I recommend using a middle-tier object to obtain disconnected recordsets. This offers several advantages, such as simpler presentation-layer code, code reuse, connection pooling, and increased scalability.
Now that you have the master-detail data in a convenient shrinkwrapped ADO recordset, the next step is to use it. Here's how to use the code with the Microsoft Hierarchical FlexGrid:
Set grdShape.Recordset = rsShape
grdShape.Refresh
Pretty simple! However, most real applications have more demanding and attractive interfaces. A more common method of using the data is to pull data from the recordsets to place in various user interface objects, such as textboxes, listboxes, and so on. The parent recordset is easy enough to use: Access data as you would with a regular two-dimensional recordset. You copy the recordset to a variable to use data in a child recordset:
Dim rsCustOrders as ADODB.Recordset
rsShape.Find "CustomerID = '" & _
strCustomerID & "'"
Set rsCustOrders = _
rsShape.Fields("rsOrders").Value
The Orders information and its child recordset for Order Details are now in the rsCustOrders object. This object is a reference to the child object, not a separate recordset. When you edit the rsCustOrders object, you're editing the underlying child object in the original hierarchical recordset. You can traverse this object to use the data. The child recordset only has data for the single parent record. If you edit, delete, or add records to the child recordset, you can update the original recordset like this:
'...Some editing, adding, or deleting
rsCustOrders.Update
rsShape.Update
'This next line only destroys the
'reference, not the underlying
'recordset
Set rsCustOrders = Nothing
The same holds true for child recordsets in rsCustOrders. As for sending changes back to the database, I recommend you use SPs and iterate through the recordset. The sample VB6 project illustrates one way to accomplish this.
You can apply the information you've learned here in numerous ways. For example, you can extend the sample code by applying the methodology of data shaping with parameterized stored procedures and create a powerful, efficient, scalable, and reliable three-tier application. Consider the applications you've created already where you used hierarchical data, and perhaps the projects you have in the queue, and use data shaping to work smarter.
About the Author
Jeff Jones is a senior software engineer with A.D.A.M. Inc. (www.adam.com), a medical illustrations and software company in Atlanta. He's a longtime VB and SQL Server developer. Reach Jeff at .
|