Data-shaped SQL clauses

Since it's introduction in ADO 2.0, data shaping has remained largely
on the fringes of Visual Basic arcanum. Relegated to the back pages of
musty manuals, you may have overlooked this useful aspect of ADO. If
you're not familiar with data-shaping, in essence, it lets you create
recordsets within recordsets--or parent/child relationships--all with a
single ADO object. This means no messy joins, no complicated filtering,
and no need for spaghetti-code in presentation logic. Data shaping
reduces the amount of traffic crossing a network, provides more
flexibility when using aggregate functions, and reduces overhead when
interfacing with leading-edge tools like XML.

To create a shaped recordset, you use a standard SQL statement, along
with three major keywords: SHAPE, APPEND, and RELATE, like so

SHAPE {SELECT EmployeeID,FirstName, LastName
FROM Employees}
APPEND ({SELECT OrderID, shipname, EmployeeID
FROM orders} AS SomeAlias
RELATE EmployeeID TO EmployeeID);


The SHAPE keyword specifies and defines the parent recordset. Once you
create a parent object, you next APPEND the child recordset. This
clause uses a similar syntax as SHAPE and contains the necessary SQL
statement to create the child recordset within the parent. In addition
to the child recordset's SQL statement, you must also indicate how you
want the two recordsets to RELATE.

You might also like...

Comments

ElementK Journals

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“C++ : Where friends have access to your private members.” - Gavin Russell Baker