SQL trees

[Robert Schuldenfrei]

Hi NG,

After a break of two months I am back on my project of converting a COBOL
ERP product to C# and SQL Server. I now have to process a tree structure in
C#. Not only am I a newbie in C#, but SQL as well. So if these questions
sound stupid, please forgive me. All of my programming in C# to date, with
one exception, has been to "ask" SQL for a specific primary key. That
exception has been to fill a C# data table with all of the rows from a SQL
table. The process for the tree structure is a tad different. The tree's
primary key has 2 parts: a parent part number and a component part number.
The COBOL procedure makes use of the fact that the data is stored in an ISAM
file. By definition the index is in sequential order. It is easy to ask
COBOL for the first parent and store its component. Then see if that
component is a parent to some other component. From anywhere in the tree
you can always ask for the next sequential record and of course you can ask
for the first record. I can not figure out how to ask SQL for the first row
of a table. Further, I do not know how to ask for a row where I know the
parent, but not the component. Consider the following tree:

Parent Component
500-000 500-001
500-000 500-004
500-000 500-005
500-000 500-006
500-000 500-100
500-004 500-080
500-005 500-080
500-100 500-002
500-100 500-003

This is one tree. In the real case there would be many trees. Task 1 is to
ask SQL for the first row. In my past C# programs I would know what the
whole key is, 500-000 500-001 in this case. Now I do not know the key. How
do I ask for the first row? Next, I would want to check if the component,
500-001, is a parent (it is not). How do I ask SQL if any row begins with
500-001? The component of row 2 is in fact a parent. Asking SQL about this
component as parent should produce row 6.

My understanding of this issue would be greatly advanced if I could process
this table as a sequential file. For example I would like to ask SQL to
find the first parent that was equal to 500-100. Then I would like to ask
for the next parent that was 500-100 until it could find no more. In that
case it would find 2 rows starting at row 8.

If it would help I can supply the code I am using to get particular rows
when I know the full key to ask SQL.

Thanks in advance,

Bob
--
Robert Schuldenfrei
S. I. Inc.
32 Ridley Road
Dedham, MA 02026
(e-mail address removed)
781/329-4828

 

[Robby]

It is best that one field in a datebase table holds only one piece of
information. If your SQL table is named TREEList then there should be a
separate Compoment field and Parent field.

One way to traverse a tree is ...

To get the tree base you need to know the top parent

SELECT * FROM TREEList WHERE Component = @TopNode

To get the node's children you need to know the parent. You could order by
Component to keep the child nodes in order.

SELECT * FROM TREEList WHERE Parent = @ParentNode ORDER BY Component

Doing it this way you would setup each of these SQL statements as a
parameterised query or stored proceedure and call them through ADO.Net in
your C# application.

You could then create a Tree class that takes the parent node id and
recursively calls the children SQL statment to build the tree in your code.


Robby

 

[Bob Grommes]

Task 1 is to ask SQL for the first row. In my past C# programs I would
know what the whole key is, 500-000 500-001 in this case. Now I do not
know the key. How do I ask for the first row? Next, I would want to
check if the component, 500-001, is a parent (it is not). How do I ask
SQL if any row begins with 500-001?

To get the first parent / component combo, do this:

SELECT TOP 1 Parent,Component FROM MyTable ORDER BY Parent,Component

To find out if the first component is a parent or not (I'm assuming you are
parameterizing your queries and have place the key of the Component above
into a parameter called @Parent):

SELECT COUNT(*) AS Qty FROM MyTable WHERE Parent = @Parent

My understanding of this issue would be greatly advanced if I could
process this table as a sequential file. For example I would like to ask
SQL to find the first parent that was equal to 500-100. Then I would like
to ask for the next parent that was 500-100 until it could find no more.

One approach would be:

SELECT DISTINCT Parent FROM MyTable ORDER BY Parent

This would give you a list of all the parents keys. If you are going to
process it sequentially, forward-only, I'd use a DataReader to walk through
this list:

using (myReader) {

while (myReader.Read()) {
string parent = myReader.GetString(0);
// set up a SqlCommand and parameter here to get the Components for
the current "parent".
// then use a 2nd DataReader in an inner loop to process each
component.
}

}

If you need to be able to jump backwards in either list, use a DataTable
instead of a DataReader. But it doesn't sound like you need that
functionality, in which case, a DataReader is faster.

The above code is skelatal and incomplete but give the general idea.

--Bob

 

[Michael C#]

Joe Celko wrote two books "SQL For Smarties" and "Trees and Hierarchies in
SQL" that go into great detail concerning implementation of hierarchical
data structures in SQL. He provides several working examples including the
ERP-style "parts explosion". Well worth the investment.

 

[Robert Schuldenfrei]

Hi Robby,

Thank you for your help. Sadly, you put your finger on the nub of the
issue: "You need to know the top parent." In an ISAM file you always know
the top parent. In addition, Bills of Materials (BOM) store many trees that
each have a top parent. If memory were not an issue, I would ask SQL for
the whole BOM table and be done with it. Alas, in real world systems, these
tables have hundreds of thousands of rows. The good news seems to be that
there are two books by Joe Celko that Michael C# suggests will solve my
problem. I am about to order them.

Sincerely,

Bob
--
Robert Schuldenfrei
S. I. Inc.
32 Ridley Road
Dedham, MA 02026
(e-mail address removed)
781/329-4828

 

[Robert Schuldenfrei]

Hi Bob,

Thank you for dealing with my Bill of Material (BOM) problem. This looks
like it will address my concerns. A bit later on today, or maybe tomorrow,
I will write some code to investigate these techniques. Certainly, SELECT
TOP 1 Parent... looks like the answer to getting started. I am a tad afraid
of doing anything like SELECT DISTINCT Parent... as real world applications
could return thousands of rows. I have been told by Michael C# that there
are two books by Joe Celko that address my problem. I am ordering them
today.

Sincerely,

Bob
--
Robert Schuldenfrei
S. I. Inc.
32 Ridley Road
Dedham, MA 02026
(e-mail address removed)
781/329-4828

 

[Robert Schuldenfrei]

Hi Michael,

Thanks for the tip. I am going to order these books today. From a brief
Google search I am quite sure that this will more than handle my problem.

Sincerely,

Bob
--
Robert Schuldenfrei
S. I. Inc.
32 Ridley Road
Dedham, MA 02026
(e-mail address removed)
781/329-4828