Back to General and Gameplay Programming

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

General and Gameplay Programming Programming

Started by ArchG June 01, 2007 12:15 PM

4 comments, last by vermilion_wizard 16 years, 10 months ago

ArchG

327

Author

June 01, 2007 12:15 PM

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
2                 Madison                 1
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
  -Madison
      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

jpetrie

13,220

June 01, 2007 12:20 PM

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>.

capn_midnight

1,708

June 01, 2007 12:26 PM

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.

[Formerly "capn_midnight". See some of my projects. Find me on twitter tumblr G+ Github.]

Machaira

1,035

June 01, 2007 12:34 PM

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

Former Microsoft XNA and Xbox MVP | Check out my blog for random ramblings on game development

ArchG

327

Author

June 01, 2007 12:38 PM

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.

vermilion_wizard

568

June 01, 2007 04:34 PM

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.

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

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

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

This topic is closed to new replies.

Popular Topics

Recommended Tutorials

Reticulating splines