Building a Hierarchical dataobject (with properties) from one or more datasets

Question

Building a Hierarchical dataobject (with properties) from one or more datasets

travis.myers

Grasshopper

Points: 16
More actions
September 19, 2008 at 12:21 pm

#191055

Hi ,
Right now I have an custom class that contains other classes and various properties. The class architecture is built in such a way that each level has properites and potential sub elements that themselves contain properties.
I have successfully worked out the concept using arraylists and I am using data readers to supply the property values from tables in my DB. Iam storing some system.diagnostics parameter specifications in a table called "indicators". Depending on a server node's service specification the indicators get set up or not on the servers. The result is a type of coordinate system:
Service(0).Server(0).Node(0).Indicator(0)
What I would like to do to however is build a heirarchical dataobject through datasets instead. The first idea that comes to mind is the treenode class in .net .....the concept is certainly the same but basically I need to make my own equalivant of such a class with its own properties and values and potentially subelements.
Would any of you be able to help me quanitify the datamodeling aspects I'm looking for and help provide me with the SQL-esque direction I should take to obtain such a direction?
Links to further learning also greatly appreciated.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

I've not written any non-TSQL in quite a bit... however, when I did, I wasn't opposed to using someone else's example to get the job done. Take a look at the following link for a T-SQL method to do this... it can provide you with the basic idea of how to design a CLR to do the same thing.

http://msdn.microsoft.com/en-us/library/aa172799(SQL.80).aspx

For the record, I think a CLR is going to be slower that the T-SQL code... and, there are several ways to pull it off in TSQL including the "Nested Set" method that Joe Celko wrote about...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Ron McCullough SSC Guru Points: 63877 More actions · Answer 2

Although not a direct answer to your question you might want to visit Books On Line for SQL Server 2008 in which Microsoft introduced the Hierarchical data type. This might be a good starting page

http://msdn.microsoft.com/en-us/library/bb677298.aspx

From there you can traverse to Hierarchical tables and on to enough to make for a full evening of reading. Adapting what you can to SQL 2005

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

The many reason to use a hierarchical data set is to have an unknown/undetermined number of levels. If you have a hard hierarchy like you have (and not some parent/child relation of unknown depth going on), there's no real reason to build a self-referring table.

If it's as straightforward as what you describe - looks to me that a 4-part integer compound index would be more than sufficient to capture the structure....Would be a whole heck of a lot faster too...

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?