I have a table called tblOrg in a SQL Server 2000 database.

It has PK = Org_ID and a field called ParentOrg_ID so an Organization can
have 0, 1 or many children but only one parent.

I have a recursive function that builds a tree view of this and populates a
tree control with Org_ID as the key and OrgName as the Text.  Everything
works great and performance is great as long as the data base and the
application are on the same machine.  But performance is unacceptable on a
remote machine connected via a LAN.

This is to be expected because the application is using sqlcommands to build
the tree in the recursive function.  Thus, the same sqlcommand is executed
once for each organization.

Obviously to get the most update version of the data you have build it from
the database.  But what other ways are there, that once built, one can reuse
it rather that rebuild the tree?

For example, the user selects OrgType from a dropdown (College, High School,
Middle School) which populates the tree control with organization of that
type, he then can select the Organization and populate the form with the
details of the selected organization.

When changing the OrgType, the control is completely rebuilt via the
recurisive function which goes to the database.  Instead, it would be nice to
persist the previously clicked OrgTypes in memory so that the tree control is
populated from memory if that OrgType is reselected.

Since this tree control is used in other places in the application, it might
be better to populate a dataset in memory so that all projects within the
application can reference and populate the tree control that way or,  better
yet, all instances of the application can use it.  For example, a user A
opens his application which causes the Tree to be built.  But user B opens
his application but the application has access to the already populated tree
because of User A’s work (This organization structure does not change often).

How can I do this?  Any VB or C# code examples would be nice?

WR