Sign in to follow this  
ArchG

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

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

Share this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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 this post


Link to post
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.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this