• 12
• 12
• 9
• 10
• 13

# [.net] What Collection to use...for given situation, if any

This topic is 3947 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

## Recommended Posts

Hello. I'm basically asking...what is a good way to do this... I have a database table called folders....that has these fields.
ID(int, Primary Key, seeded)      Title(varchar)      Parent_Folder_ID(int)

I have about 400 records in this table. If the Parent_Folder_ID field is null, that means that the folder is a top level folder, or root folder. An example would be.. RECORDS
ID                TITLE                   PARENT_FOLDER_ID
1                 Wisconsin               NULL
3                 Milwaukee               1
4                 Communities             2

So I have all these records...and I want to make them into a tree view..that would look like this.
-Wisconsin
Communities
Milwaukee

That shows the hierarchy of the folders. I can do this fine...but right now I must be doing it a terrible way because it takes about 3 minutes to excute all the database querys i'm doing. (I first grab the folder, then grab all child folders of it...) so i'm running like a million database queries. Is there an easy way to do this...? I thought maybe c# had some of those collections that would help me out in this case...(I've never really used them so didn't know which one would be best for this scenerio) Thanks, ArchG

##### Share on other sites
Perhaps I'm misunderstanding, but from the way you've presented the problem it sounds like your database structure and/or queries are the bottleneck, because they don't let you get the data out of the database in the fashion you need it efficiently. How you're storing the data once retrieved seems to be inconsequential, here.

In any case, I'd use a simple tree structure -- a node with a list of child nodes -- to store the data once recovered from the database. There's no collection for this, unless you directly store the data into the node items in a tree view control, but its simple to make one using List<T>.

##### Share on other sites
A collection won't help you here. Your bottleneck is the database. It sounds like you're making a new connection for each query. Reuse the connection for all queries in your system, only close the connection when the application exits. Also, combine your queries. Instead of selecting only a single row at a time, select all of the rows you will need at once. If there aren't too many rows, just select all of the rows and keep it in memory.

##### Share on other sites
There is a LinkedList class in the .NET Framework in the System.Collections.Generic namespace. Just what you need I think.

##### Share on other sites
I realize that the bottle neck is the database, but I think it's just the way i'm getting the data...
(I hate posting this...it's terrible...but the way i figured out how to do it.)
void LoadTree(){    string sSQL = "SELECT ID, Name FROM Folders WHERE Parent_Folder_ID IS NULL ORDER BY Name ASC";            SqlDataReader Reader = Command.ExecuteReader(sSQL);            if (Reader.HasRows)            {                while (Reader.Read())                {                    TreeNode Node = new TreeNode((string)Reader["Name"]);                    Node.Tag = (int)Reader["ID"];                    AddSubNodes((int)Reader["ID"], Node);                    treeView1.Nodes.Add(Node);                    this.Update();                }            }            Reader.Close();            Connection.Close();}private bool AddSubNodes(int ID, TreeNode Parent){            SqlDataReader Reader = Command.ExecuteReader("SELECT ID, Name FROM Folders WHERE Parent_Folder_ID = " + ID.ToString() + " ORDER BY Name ASC");            if (Reader.HasRows)            {                while (Reader.Read())                {                    TreeNode N = new TreeNode((string)Reader["Name"]);                    N.Tag = (int)Reader["ID"];                    AddSubNodes((int)Reader["ID"], N);                    Parent.Nodes.Add(N);                    this.Update();                }                Reader.Close();                Connection.Close();            }            else            {                Reader.Close();                Connection.Close();                return false;            }            return true;}

What I would like to do....is just do one query...like this
SELECT * FROM Folders

store it in memory...and then just organize it some how...
The question is ...how do I organize the data into the hierarchy? I'm sure I could think of some dumb idea like I did above...but I just thought that maybe there was some type of collection or something I could throw a folderObject(custom object) or something into and make my life easier my code better.

##### Share on other sites
I would do one query that was
SELECT * FROM Folders ORDER BY PARENT_FOLDER_ID

Then (I think) the root folders will all be first, so iterate through the table adding those until you find a PARENT_FOLDER_ID that's not null. I would store them (at least temporarily) in a Dictionary<int,MyFolder> where MyFolder is the structure that you use to hold all the information about a folder and a list of its children.

Once you find something with a PARENT_FOLDER_ID that's not null, iterate through them and read each folder's data and add it to the appropriate folder's child list. Also add it to the dictionary, since each child folder can have children.

I would keep a separate List<MyFolder> of just the root folders so that you can construct the tree graph the way you want.