ASP 101 - Active Server Pages 101 - Web06
The Place ASP Developers Go!

Please visit our partners


Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates














ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash



 Top ASP 101 Stories Top ASP 101 Stories
The Top 10 ASP Links @ Microsoft.com
What is Adovbs.inc and Why Do I Need It?
An Overview of ASP.NET

QUICK TIP:
Previewing ASP pages in Visual InterDev 6.0
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


Advanced Data Shaping

Advanced Data Shaping

by Chris Payne

Introduction

Our last article on data shaping introduced you to the basics, with a few applications. This time we'll talk about reshaping, and explore one topic more in depth - that of a hierarchical file system. Briefly, let's review what data shaping is.

Data Shaping

Data shaping is a little known feature of ADO 2.0 and above that allows you to create hierarchical recordsets, which can give you tremendous power over manipulating and displaying data. Unfortunately, data shaping has taken a back seat for many developers.

Data shaping lets you create parent-child relationships between recordsets, i.e. recordsets within recordsets, all with a single ADO object. This is a dream for a web developer, because it eliminates messy joins, complicated filtering, and the need for spaghetti-code in presentation logic. This in turn reduces the amount of traffic crossing a network and reduces overhead when interfacing with other tools, such as XML.

The Shape Command

Let's go over the syntax for the shape command for review:

SHAPE {parent-command} [[AS] table-alias]
APPEND {child-command} 
RELATE(parent-column TO child-column)

And to tickle your syntax funny bone, here's a more complex syntax for shape structures with grandchildren:

SHAPE {parent-command} [[AS] table-alias]
APPEND (
    ( SHAPE {child SQL statement} [[AS] alias]
    APPEND ( {grandchild SQL statement} [[AS] alias]
    RELATE child column TO grandchild column) [[AS] alias] )
RELATE parent column TO child column) [[AS] alias]

As you can see, you simply embed another shape statement within the append statement of the first shape command.

A File System

A good example of a true hierarchy would be a typical file system (imagine Windows Explorer). Let's look at the following example:

Inetpub
    wwwroot
        index.asp
        default.asp
    mailroot
    ftproot
        mydoc.doc
    temp.asp
Inetpub2

Here we have a typical file system (albeit a small file system). Using data shaping would be an excellent way to gather and display this type of structure. Of course, if this were an actual file system, you'd be more likely to use the FileSystemObject to retrieve and display data (go here for a tutorial on the FSO). Meanwhile, let's assume the database structure for this system looks like this:

tblRoots
DID Unique ID to keep track of records Autonumber
Name Directory name String

tblSubDirectories
SID Unique ID to keep track of records Autonumber
DID Which root does this belong too? Foreign key to tblRoots Integer
Name Directory name String

tblFiles
FID Unique ID to keep track of records Autonumber
SID Which subdirectory does this belong to? Foreign key to tblRoots Integer
Name File name String

Each table then consists of a separate level in the hierarchy. The shape command to return the data in the manner presented above would look like:

SHAPE {SELECT DID, Name FROM tblRoot} 
APPEND (
    ( SHAPE { SELECT SID, Name FROM tblSubDirectories } As SubDirectory 
    APPEND (
        {SELECT FID, Name FROM tblFiles} AS Files RELATE SID TO SID) )
RELATE  DID TO DID)

This shape command can be used to display the hierarchy as outlined above. However, what happens if you have an arbitrary depth of the hierarchy? Meaning, you don't know ahead of time how many levels you have. Data shaping is great, but even it can't handle arbitrary depths by itself.

Let's examine this command a bit further. If the maximum number of levels you had was three, then you could use this statement without a problem. The command would return data in the following manner:

File System Hierarchy

For those directories or subdirectories that don't have anything beneath them, the reference to the sub-chapter would be an empty recordset. Therefore, you needn't perform any special tasks for hierarchy data of varying depths. So in essence, you simply have to know the lowest level you need to grab data from.

Note: The tables described above are not a very efficient method to store hierarchical file system information, and are used solely for example. For a more efficient method, try this article.

Data Looping

Let's say now that you don't know ahead of time how many levels there will be in the hierarchy, and for some reason, you can't just go with the lowest level available - if for example, you wish to stop at a different level for each row in the database depending on some outside conditions. You can easily set up a loop to tack on the shape commands as necessary.

<%
for i = 0 to n
    strSQL = "SHAPE { sql statement } APPEND (" _
      & strSQL & " RELATE id TO id )"
next
%>

Where n equals the number of times you would like to loop through, or, in other words, the depth of your shape command (for n = 1 you will have grandchildren, n = 2, great grandchildren, etc). For each iteration, you would fill in sql statement and id TO id. This of course requires some knowledge ahead of time - you would need to know what fields to relate the tables by, and how many levels you need to dive into.

This loop will assemble a shape command in the proper format to reach any arbitrary level in a hierarchy. The problem with this approach is if you would like multiple children for each level. Since the shape syntax is very strict, you would have to assemble your command very carefully, and each level would have to have the same format and database members.

Due to this level of complexity, if you ever reach this scenario, it would probably be best to take a step back and examine other ways of structuring your database, as this method is cumbersome and inflexible. Data shaping is always most valuable when not used in a generic setting, such as in a for loop.

Reshaping

Reshaping is a way to use data from an existing shaped recordset to return new or different information. Normally, you might have to re-execute the entire shape command, but this method allows you to use cached data that you've already requested instead, saving you time and overhead. Reshaping also allows you to get at child recordsets without having to get to them through the parents. Let's take a look at a simple example, taken from our first article:

<%
Dim strSQL, rst, rstChild, strConnectionString, rstChild
strConnectionString = _
    "Provider=MSDataShape;dsn=MyDSN;database=MyDatabase"
' Original recordset
strSQL = "SHAPE { SELECT MID, Name, Address FROM tblManufacturer } " _
  & "APPEND ({ SELECT Name, Instock_flag, Price, PID " _
  & "FROM tblProducts } " _
  & "AS Products RELATE MID to MID )"
' Open original recordset
Set rst = Server.CreateObject("ADODB.Recordset")
rst.Open strSQL, strConnectionString
' Reshaped recordset
strSQL = "SHAPE Products"
' Open reshaped recordset
Set rstChild = Server.CreateObject("ADODB.Recordset")
rstChild.Open strSQL, strConnectionString
%>

Here, we have essentially separated the filtered child recordset from its parent, by referencing the Products alias used in the original shape command. We are now free to do whatever we wish with this child recordset, including filtering and displaying data. This opens up a whole new area of possibilities for data shaping - not only can it do hierarchical recordsets, but regular recordsets as well!

We can also do more advanced queries with reshaping. The following example uses the compute command to return additional information:

<%
strSQL = "SHAPE Products COMPUTE Products, Sum(Products.Price) " _
  & "AS TotPrice"
%>

Find out that you need another parent for your child recordset? Not a problem. Let's assume we have a new Customers table - we set the Customers tables as the new parent, and the existing cached Products table as the child:

<%
strSQL = "SHAPE {SELECT * FROM Customers} APPEND " _
  & "(Products AS Products RELATE PID TO PID)"
' Open recordset
Set rstSecond = Server.CreateObject("ADODB.Recordset")
rstSecond.Open strSQL, strConnectionString
%>

Or even have the Products table as the parent:

<%
strSQL = "SHAPE Products APPEND " _
  & "({SELECT * FROM Customers} AS Customers RELATE PID TO PID)"
%>

Reshaping saves a lot of headache since you can build off of your existing recordsets, without having to execute additional statements or send more information across a network.

File System Revisited

Now, in the situation where we have an unknown level of depth in a hierarchy, we have more options. It is no longer necessary to know ahead of time how many levels there are or which levels have children, or even what format the additional tables are. Using reshaping, we can start with one parent and its child, and later, if necessary, we can append another children or parents anywhere we wish. This could continue on ad infinitum. For example, here is a fictional scenario where we create a recordset with grandchildren, and then depending on some condition at the grandchild level, we may append another child onto the recordset and display further data:

<%
strConnectionString = _
    "Provider=MSDataShape;dsn=MyDSN;database=MyDatabase"
strSQL = "SHAPE {SELECT DID, Name FROM tblRoot} APPEND ( " & _
  "( SHAPE { SELECT SID, Name FROM tblSubDirectories } " & _
  "As SubDirectory " & _
  "APPEND ( {SELECT FID, Name FROM tblFiles} AS Files " & _
  "RELATE SID TO SID) " & _
  "RELATE  DID TO DID)"
' Open original recordset
Set rst = Server.CreateObject("ADODB.Recordset")
rst.Open strSQL, strConnectionString
Do While Not rst.EOF
    Response.Write(rst("Name") & "<br>")
    ' Set object to child recordset and iterate through
    Set rstChild = rst("SubDirectory").Value
    if not rstChild.EOF then
        Do While Not rstChild.EOF
            Response.Write(rstChild("Name") & "<br>")
            if some condition then
                strSQL = "SHAPE Files APPEND " _
                  & "( { SELECT * FROM tblMoreFiles } AS " _
                  & "MoreFiles RELATE FID TO FID)"
                call AddChild(strSQL)
            end if
            rstChild.MoveNext
        Loop
        rst.MoveNext
    end if
Loop
Function AddChild (strSQL)
    dim blnChild, rst2
    Set rst2 = Server.CreateObject("ADODB.Recordset")
    rst2.open strSQL, strConnectionString
        Response.write(rst2("Name") & "<br>")
        blnChild = rst2("Child")
        rst2.close
        set rst2 = nothing
    if blnChild then
        AddChild(strSQL)
    end if
End Function
%>

The above is a very simple example (which, by the way, should not be used without modification) of how we could use reshaping, but we can see how the recursive AddChild function can perform whatever tasks we need, without having to know anything ahead of time. Thus, we can start with a simple shape command, and delve as deep as we need to go at run time.

Conclusion

In the last article on data shaping, we showed you some great uses and how to implement data shaping effectively in your applications. In this article, hopefully, we have showed you how to be more creative with data shaping, and eventually see when it is inappropriate for the task at hand. Part of using data shaping effectively, or any technology for that matter, is knowing wisely when and where to use them.

Meanwhile, data shaping still applies to many different tasks - so the more you practice, the more prepared you will be.

Resources

Check out our last data shaping article:

Check out these links for further resources on data shaping syntax and methodology:


Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers