.Net Developer Professional Technical Resources US-WA-Bellingham Post A Job | Post A Resume

Advanced Data Shaping Techniques, Part 3

---

Up until now, we've looked at how to create parent-children data shape relationships. We are restricted, however, to limiting our shapes to parents and immediate children. We can APPEND data shapes to our children, creating grandchildren! You may be wondering when you would need to use grandchildren. Well, imagine that each class could have zero to many text books assigned to it. We could modify our ASP page to display, under each class, the list of text books required for that class.

The syntax for grandchildren is a little complex. What you need to do, is in the child statement, instead of using a child SQL statement, you will use another SHAPE statement. Here is the syntax for creating a grandchild:

SHAPE {parent SQL statement} [[AS] 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]

Wow, looks a little bewildering, eh? Well, let's look at it in action with the text book example discussed. Here is the table definition for the TextBook table:

TextBook
TextBookID	Autonumber
Title	Text
Price	Currency
ClassID	Number

Note that each class can have zero to many books assigned. The ClassID column is a foreign key to the ClassID in the Class table. Now, the actual SHAPE command we will use is as follows:

SHAPE {SELECT ProfessorID, FirstName, LastName, PhoneNumber FROM Professor} APPEND( ( SHAPE {SELECT ClassID, ClassName, RefNumber, ProfessorID FROM Class} AS Class APPEND({SELECT Title, Price, ClassID FROM TextBook} AS Book RELATE ClassID TO ClassID) ) RELATE ProfessorID TO ProfessorID), " & _ ({SELECT Title, ProfessorID FROM Project} AS Project " & _ RELATE ProfessorID TO ProfessorID)

We are using data shaping to create TWO children and one grandchild! Isn't this freakin' awesome? Anyway, to loop through the recordsets, we will modify the code we used in Part 2 to include this loop inside of the loop that loops through the objClassRS recordset:

Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;Books:<BR>" Set objTextBookRS = objClassRS("Book").Value If objTextBookRS.EOF then Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;None...<BR>" Else Do While Not objTextBookRS.EOF Response.Write "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;" & _ objTextBookRS("Title") & " (" & _ FormatCurrency(objTextBookRS("Price")) & ")<BR>" objTextBookRS.MoveNext Loop End If

With some sample data entered, this will generate the following output:

Dr. Smith (Phone: 123-4567)
   Classes:
    Analysis of Algorithms (CS355)
      Books:
       Algorithmics Explained ($65.95)
       Algorithm Theory ($99.95)
    Artificial Intelligence (CS341)
      Books:
       Applied A.I. ($79.50)
   Projects:
    Self-Documenting Programming
    Optimization Routines

Dr. Johnson (Phone: 123-8901)
   Classes:
    Distributed Systems (CS333)
      Books:
       None...
   Projects:
    A.I. In the Workplace

Dr. Mitchell (Phone: 123-1234)
   Classes:
    Formal Languages & Automata Theory (CS330)
      Books:
       Automata Theory ($69.95)
       A Study of Linguistics ($72.25)
    Study of Film (ART85)
      Books:
       Film History ($39.95)
    Software Engineering (CS301)
      Books:
       Software Engineering Practices ($79.25)
   Projects:
    Software Reuse Study
    Image Processing
    Image Recognition

I sure do like data shaping, it is really neat and powerful. The syntax is cool too. :) Not only is data shaping cool, but it is also very useful! Try generating a nice, hierarchical report showing professors, their classes taught (the classes text books), and the research projects worked on by the professors. You would not only have a nasty JOIN for your SQL statement, but you would also be returning a lot of redundant information.

Hope you learned something new and had fun! Happy Programming!

(Read Part 1)
(Read Part 2)

---

Attachments:

Code for Multiple Children Data Shaping in Text Format

Code for Grandchildren Data Shaping in Text Format

Windows Internet Technology | ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article