copyPasteGhost
asked on
Building Dynamic Treeview 3 levels. C#
I have this sturcture in SQL SERVER 2005.
Table Name- Categories.
CatID LangID EngID OwnerID SubOwnerID categoryName Show OrderBy PopRating
1 ENG 1 0 NULL Automobile False 1 null
2 ENG 2 0 NULL Beauty False 2 null
3 ENG NULL 2 NULL Spa False 1 null
4 ENG NULL NULL 3 Facials False 1 null
I want the tree to look like ->
- Automobile
- Beauty
-Spa
-Facials
The problem is that I want the tree view to be built from a dataset or datareader...or whatever that is fast...there will be about 300 categories. and I don't want to have 300 database calls to build the table...
I thought about a stored procedure...but I don't really know how I would go about to implement that....Also I thought about maybe a 3 level nested loop....but that would involved making a database call every time...(Which is obviously not a good idea) I also thought about the nested loop but instead of many database calls...I can make one database call. get all the categories...then store it in a dataset or datareader ( I think datareader would be faster, not sure if I can have realtionships in a datareader though....) and then read it and populate my Tree view..
This is what I have so far... Stored procedure under it....which is really not that advanced...
Any help would be great..
Many thanks.
Ghost
Table Name- Categories.
CatID LangID EngID OwnerID SubOwnerID categoryName Show OrderBy PopRating
1 ENG 1 0 NULL Automobile False 1 null
2 ENG 2 0 NULL Beauty False 2 null
3 ENG NULL 2 NULL Spa False 1 null
4 ENG NULL NULL 3 Facials False 1 null
I want the tree to look like ->
- Automobile
- Beauty
-Spa
-Facials
The problem is that I want the tree view to be built from a dataset or datareader...or whatever that is fast...there will be about 300 categories. and I don't want to have 300 database calls to build the table...
I thought about a stored procedure...but I don't really know how I would go about to implement that....Also I thought about maybe a 3 level nested loop....but that would involved making a database call every time...(Which is obviously not a good idea) I also thought about the nested loop but instead of many database calls...I can make one database call. get all the categories...then store it in a dataset or datareader ( I think datareader would be faster, not sure if I can have realtionships in a datareader though....) and then read it and populate my Tree view..
This is what I have so far... Stored procedure under it....which is really not that advanced...
Any help would be great..
Many thanks.
Ghost
protected void BuildCatPreviewTree() {
SqlDataReader reader = dbobj.runStoredProc("usp_GetCategories", new string[,] { { "@langID", Session["lang"].ToString() } });
if(reader.HasRows){
while (reader.Read()) {
TreeNode grandParentNode = new TreeNode(reader["CategoryName"].ToString(), reader["CategoryID"].ToString());
}
}
reader.Close();
dbobj.closeDB();
/* foreach (string[] element in alCats) {
//for (int i = 0; i < element.Length; i++) {
TreeNode grandParentNode = new TreeNode(element[0]);
tvPreview.Nodes.Add(grandParentNode);
for (int i = 1; i < element.Length; i++) {
TreeNode parentNode = new TreeNode(element[i]);
grandParentNode.ChildNodes.Add(parentNode);
for (int i = 1; i < element.Length; i++) {
}
}
// }
}
*/
}
And the stored procedure....
PROCEDURE [dbo].[usp_GetCategories]
@langID char(3)
AS
SELECT * FROM categories where LanguageID=@langID;
ASKER
that sounds really good... So the structure would be??
Right now:
CatID LangID EngID OwnerID SubOwnerID categoryName Show OrderBy PopRating
1 ENG 1 0 NULL Automobile False 1 null
2 ENG 2 0 NULL Beauty False 2 null
3 ENG NULL 2 NULL Spa False 1 null
4 ENG NULL NULL 3 Facials False 1 null
Proposed:
CatID LangID EngID ParentCategoryId categoryName Show OrderBy PopRating
1 ENG 1 ??? Automobile False 1 null
2 ENG 2 ??? Beauty False 2 null
3 ENG NULL ??? Spa False 1 null
4 ENG NULL ??? Facials False 1 null
I will only ever need 3 levels..
Main category
sub category
sub sub category
>>3) In your code
>>Add a Stack to track tree nodes and go through the result set one line by one line to build the tree. (Push a >>node to Stack when level goes smaller, Pop when it goes bigger)
Do you have an example on how to I can do this? I'm not very familiar with pop and push...or even stack for that matter????
Also I need it to be very fast as it is for a web application. that's not going to be a problem is it?
thanks,
Ghost
Right now:
CatID LangID EngID OwnerID SubOwnerID categoryName Show OrderBy PopRating
1 ENG 1 0 NULL Automobile False 1 null
2 ENG 2 0 NULL Beauty False 2 null
3 ENG NULL 2 NULL Spa False 1 null
4 ENG NULL NULL 3 Facials False 1 null
Proposed:
CatID LangID EngID ParentCategoryId categoryName Show OrderBy PopRating
1 ENG 1 ??? Automobile False 1 null
2 ENG 2 ??? Beauty False 2 null
3 ENG NULL ??? Spa False 1 null
4 ENG NULL ??? Facials False 1 null
I will only ever need 3 levels..
Main category
sub category
sub sub category
>>3) In your code
>>Add a Stack to track tree nodes and go through the result set one line by one line to build the tree. (Push a >>node to Stack when level goes smaller, Pop when it goes bigger)
Do you have an example on how to I can do this? I'm not very familiar with pop and push...or even stack for that matter????
Also I need it to be very fast as it is for a web application. that's not going to be a problem is it?
thanks,
Ghost
You can put
0
0
2
3
in parent id for category 1 to 4
OK, I have a page with only a treeview called TreeView1, the following code will populate the treeview
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Dictionary<string, int> values = new Dictionary<string, int>();
values.Add("Automobile", 3);
values.Add("Beauty", 3);
values.Add("Spa", 2);
values.Add("Facials", 1);
Stack<TreeNodeCollection> s = new Stack<TreeNodeCollection>( );
s.Push(TreeView1.Nodes);
TreeNode lastNode = null;
TreeNode currentNode;
int currentLevel = 3;
foreach (KeyValuePair<string, int> value in values)
{
currentNode = new TreeNode(value.Key);
if (currentLevel > value.Value) //Move down
{
s.Push(lastNode.ChildNodes );
}
else if (currentLevel < value.Value)//Move up
{
s.Pop();
}
s.Peek().Add(currentNode);
currentLevel = value.Value;
lastNode = currentNode;
}
}
}
0
0
2
3
in parent id for category 1 to 4
OK, I have a page with only a treeview called TreeView1, the following code will populate the treeview
protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
Dictionary<string, int> values = new Dictionary<string, int>();
values.Add("Automobile", 3);
values.Add("Beauty", 3);
values.Add("Spa", 2);
values.Add("Facials", 1);
Stack<TreeNodeCollection> s = new Stack<TreeNodeCollection>(
s.Push(TreeView1.Nodes);
TreeNode lastNode = null;
TreeNode currentNode;
int currentLevel = 3;
foreach (KeyValuePair<string, int> value in values)
{
currentNode = new TreeNode(value.Key);
if (currentLevel > value.Value) //Move down
{
s.Push(lastNode.ChildNodes
}
else if (currentLevel < value.Value)//Move up
{
s.Pop();
}
s.Peek().Add(currentNode);
currentLevel = value.Value;
lastNode = currentNode;
}
}
}
ASKER
your example worked..now I need to link it to my table in the database...another thing you might not of noticed..but the table is language independant...if I would want the category names in french for instance...would I just add... where langaugeID='Fre' at the end of your select?
Also wondering what is the purpose of the ParentCategoryId field.
and what does it's contents mean? since I'm going to be filling the database from a form I need to know how to properly insert the data.
thanks.
Also wondering what is the purpose of the ParentCategoryId field.
and what does it's contents mean? since I'm going to be filling the database from a form I need to know how to properly insert the data.
thanks.
The query I put here is just for example.
You have to modify it according to your needs.
It does not select all columns and the orderby column does not need to be selected.
ParentCategoryId is the CatId of the row which is the parent of current row.
Use one parent idcolumn just to simplify the query. Your table structure could also be used as long as you can come up a query which returns the result in the right order.
You have to modify it according to your needs.
It does not select all columns and the orderby column does not need to be selected.
ParentCategoryId is the CatId of the row which is the parent of current row.
Use one parent idcolumn just to simplify the query. Your table structure could also be used as long as you can come up a query which returns the result in the right order.
ASKER
ok I'm going to try it and post back my results.
Thanks for your continued help!
Thanks for your continued help!
ASKER
What would the sql statement be if instead of ordering by the orderby field I just wanted to order them alphabetically?
Thanks
Thanks
ASKER
This is what I have so far...
it's giving me an index out of bounds on the "ParentCategoyID" field...
it's giving me an index out of bounds on the "ParentCategoyID" field...
Dictionary<string, int> values = new Dictionary<string, int>();
SqlDataReader reader = beeDBobj.runStoredProc("usp_GetCategories", new string[,] { { "@langID", Session["lang"].ToString() } });
while (reader.Read()) {
values.Add(reader["CategoryName"].ToString(), Int32.Parse(reader["ParentCategoyID"].ToString()));
}
Stack<TreeNodeCollection> s = new Stack<TreeNodeCollection>();
s.Push(CatTree.Nodes);
TreeNode lastNode = null;
TreeNode currentNode;
int currentLevel = 3;
foreach (KeyValuePair<string, int> value in values) {
currentNode = new TreeNode(value.Key);
if (currentLevel > value.Value) //Move down
{
s.Push(lastNode.ChildNodes);
}
else if (currentLevel < value.Value)//Move up
{
s.Pop();
}
s.Peek().Add(currentNode);
currentLevel = value.Value;
lastNode = currentNode;
}
this is the procedure...
ALTER PROCEDURE [dbo].[usp_GetCategories]
@langID char(3)
AS
with result(CategoryID,CategoryName,level,OrderBy)
as
(
select CategoryID,CategoryName,3,row_number() over (order by CategoryID) * Power(10,3 * 2)
from Categories
where ParentCategoyID = 0
union all
select c.CategoryID,c.CategoryName,r.level - 1,r.OrderBy + row_number() over (order by c.CategoryID) * Power(10,(level - 1) * 2)
from Categories c inner join result r on c.ParentCategoyID = r.CategoryID where LanguageID=@langID
)
select * from result order by OrderBy
THIS IS THE TABLE:
CategoryID LanguageID EnglishID ParentCategoyID CategoryName Show OrderBy PopRating
2 ENG 1 0 Automobile False NULL NULL
I would also like to know how to take out the Orderby and use the select to order it alphbetically.
THANKS
1) You don't need the generic dictionary in the code. You can loop through the data reader.
2) Unless you set the order statically in the table, you have to have the orderby column to get the right result.
The following might work for you:
ALTER PROCEDURE [dbo].[usp_GetCategories]
@langID char(3)
AS
with result(CategoryID,Category Name,level ,OrderBy)
as
(
select CategoryID,CategoryName,3, row_number () over (order by CategoryName ) * Power(10,3 * 2)
from Categories
where ParentCategoyID = 0 and LanguageID=@langID
union all
select c.CategoryID,c.CategoryNam e,r.level - 1,r.OrderBy + row_number() over (order by c.CategoryName) * Power(10,(level - 1) * 2)
from Categories c inner join result r on c.ParentCategoyID = r.CategoryID where LanguageID=@langID
)
select CategoryName,level from result order by OrderBy
2) Unless you set the order statically in the table, you have to have the orderby column to get the right result.
The following might work for you:
ALTER PROCEDURE [dbo].[usp_GetCategories]
@langID char(3)
AS
with result(CategoryID,Category
as
(
select CategoryID,CategoryName,3,
from Categories
where ParentCategoyID = 0 and LanguageID=@langID
union all
select c.CategoryID,c.CategoryNam
from Categories c inner join result r on c.ParentCategoyID = r.CategoryID where LanguageID=@langID
)
select CategoryName,level from result order by OrderBy
ASKER
>1....how can I loop through the data reader...
>2....Is it not possible to just order it alphabetically?
I changed it alittle trying to get it to work...
I now get this error...
Exception Details: System.NullReferenceExcept ion: Object reference not set to an instance of an object.
Source Error:
Line 71: if (currentLevel > value.Value) //Move down
Line 72: {
Line 73: s.Push(lastNode.ChildNodes );
Line 74: }
Line 75: else if (currentLevel < value.Value)//Move up
Source File: d:\MyDomain\MyDomainV1.5\S earch.aspx .cs Line: 73
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.]
Search.BuildCategoryTree() in d:\MyDomain\MyDomainV1.5\S earch.aspx .cs:73
Search.Page_Load(Object sender, EventArgs e) in d:\MyDomain\MyDomainV1.5\S earch.aspx .cs:38
System.Web.Util.CalliHelpe r.EventArg FunctionCa ller(IntPt r fp, Object o, Object t, EventArgs e) +31
System.Web.Util.CalliEvent HandlerDel egateProxy .Callback( Object sender, EventArgs e) +68
System.Web.UI.Control.OnLo ad(EventAr gs e) +88
System.Web.UI.Control.Load Recursive( ) +74
System.Web.UI.Page.Process RequestMai n(Boolean includeStagesBeforeAsyncPo int, Boolean includeStagesAfterAsyncPoi nt) +3022
Any Ideas?
>2....Is it not possible to just order it alphabetically?
I changed it alittle trying to get it to work...
I now get this error...
Exception Details: System.NullReferenceExcept
Source Error:
Line 71: if (currentLevel > value.Value) //Move down
Line 72: {
Line 73: s.Push(lastNode.ChildNodes
Line 74: }
Line 75: else if (currentLevel < value.Value)//Move up
Source File: d:\MyDomain\MyDomainV1.5\S
Stack Trace:
[NullReferenceException: Object reference not set to an instance of an object.]
Search.BuildCategoryTree()
Search.Page_Load(Object sender, EventArgs e) in d:\MyDomain\MyDomainV1.5\S
System.Web.Util.CalliHelpe
System.Web.Util.CalliEvent
System.Web.UI.Control.OnLo
System.Web.UI.Control.Load
System.Web.UI.Page.Process
Any Ideas?
ALTER PROCEDURE [dbo].[usp_GetCategories]
@langID char(3)
AS
with result(CategoryID,CategoryName,level,OrderBy,ParentCategoyID)
as
(
select CategoryID,CategoryName,3,row_number() over (order by CategoryName ) * Power(10,3 * 2),ParentCategoyID
from Categories
where ParentCategoyID = 0 and LanguageID=@langID
union all
select c.CategoryID,c.CategoryName,r.level - 1,r.OrderBy + row_number() over (order by c.CategoryName) * Power(10,(level - 1) * 2),r.ParentCategoyID
from Categories c inner join result r on c.ParentCategoyID = r.CategoryID where LanguageID=@langID
)
select CategoryName,level,CategoryID,ParentCategoyID from result order by OrderBy
this is the C#...
Dictionary<string, int> values = new Dictionary<string, int>();
SqlDataReader reader = beeDBobj.runStoredProc("usp_GetCategories", new string[,] { { "@langID", Session["lang"].ToString() } });
while (reader.Read()) {
values.Add(reader["CategoryName"].ToString(), Int32.Parse(reader["ParentCategoyID"].ToString()));
}
Stack<TreeNodeCollection> s = new Stack<TreeNodeCollection>();
s.Push(CatTree.Nodes);
TreeNode lastNode = null;
TreeNode currentNode;
int currentLevel = 3;
foreach (KeyValuePair<string, int> value in values) {
currentNode = new TreeNode(value.Key);
if (currentLevel > value.Value) //Move down
{
s.Push(lastNode.ChildNodes);
}
else if (currentLevel < value.Value)//Move up
{
s.Pop();
}
s.Peek().Add(currentNode);
currentLevel = value.Value;
lastNode = currentNode;
}
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
it works!
thanks SO much!
thanks SO much!
ASKER
I'm running into another problem...
here is the database.. and the code for the method is under it..
I'm getting an error..
An item with the same key has already been added.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: An item with the same key has already been added.
Source Error:
Line 250: SqlDataReader reader = beeDBobj.runStoredProc("us p_GetCateg ories", new string[,] { { "@langID", Session["lang"].ToString() } });
Line 251: while (reader.Read()) {
Line 252: values.Add(reader["Categor yName"].To String(), Int32.Parse(reader["level" ].ToString ()));
Line 253: }
Line 254:
Source File: d:\hosting\member\MyDomain \site1\Man ageCategor ies.aspx.c s Line: 252
Stack Trace:
[ArgumentException: An item with the same key has already been added.]
System.ThrowHelper.ThrowAr gumentExce ption(Exce ptionResou rce resource) +48
System.Collections.Generic .Dictionar y`2.Insert (TKey key, TValue value, Boolean add) +2653240
System.Collections.Generic .Dictionar y`2.Add(TK ey key, TValue value) +11
ManageCategories.BuildCatP reviewTree () in d:\hosting\member\MyDomain \site1\Man ageCategor ies.aspx.c s:252
ManageCategories.Page_PreR ender(Obje ct sender, EventArgs e) in d:\hosting\member\MyDomain \site1\Man ageCategor ies.aspx.c s:79
System.Web.Util.CalliHelpe r.EventArg FunctionCa ller(IntPt r fp, Object o, Object t, EventArgs e) +15
System.Web.Util.CalliEvent HandlerDel egateProxy .Callback( Object sender, EventArgs e) +33
System.EventHandler.Invoke (Object sender, EventArgs e) +0
System.Web.UI.Control.OnPr eRender(Ev entArgs e) +2117788
System.Web.UI.Control.PreR enderRecur siveIntern al() +86
System.Web.UI.Page.Process RequestMai n(Boolean includeStagesBeforeAsyncPo int, Boolean includeStagesAfterAsyncPoi nt) +2041
Any ideas would be awesome!
Thanks!
here is the database.. and the code for the method is under it..
I'm getting an error..
An item with the same key has already been added.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: An item with the same key has already been added.
Source Error:
Line 250: SqlDataReader reader = beeDBobj.runStoredProc("us
Line 251: while (reader.Read()) {
Line 252: values.Add(reader["Categor
Line 253: }
Line 254:
Source File: d:\hosting\member\MyDomain
Stack Trace:
[ArgumentException: An item with the same key has already been added.]
System.ThrowHelper.ThrowAr
System.Collections.Generic
System.Collections.Generic
ManageCategories.BuildCatP
ManageCategories.Page_PreR
System.Web.Util.CalliHelpe
System.Web.Util.CalliEvent
System.EventHandler.Invoke
System.Web.UI.Control.OnPr
System.Web.UI.Control.PreR
System.Web.UI.Page.Process
Any ideas would be awesome!
Thanks!
14 ENG 1 0 Automotive False 1 NULL
15 ENG 2 14 Auto Accessories False 1 NULL
16 ENG 3 0 Apparel & Accessories False 2 NULL
17 ENG 4 0 Entertainment & Media False 3 NULL
19 ENG 5 0 Business & Professional Services False 4 NULL
22 ENG 6 0 Education False 5 NULL
23 ENG 7 0 Computers, Communications & Electronics False 6 NULL
24 ENG 8 0 Family & Community False 7 NULL
25 ENG 9 0 Finance & Legal False 8 NULL
26 ENG 10 0 Food & Beverages False 9 NULL
27 ENG 11 0 Health & Medicine False 10 NULL
28 ENG 12 0 Home & Garden False 11 NULL
29 ENG 13 0 Personal Care False 12 NULL
30 ENG 14 0 Real-Estate & Insurance False 13 NULL
31 ENG 15 0 Shopping & Specialty Stores False 14 NULL
32 ENG 16 0 Sports & Recreation False 15 NULL
33 ENG 17 0 Travel & Lodging False 16 NULL
34 ENG 18 0 Construction & Renovation False 17 NULL
35 ENG 19 0 Agriculture, Fishing & Forestry False 18 NULL
36 ENG 20 0 Industrial supplies & Services False 19 NULL
37 ENG 21 0 Transportation False 20 NULL
38 ENG 22 0 Public Utilities & Environment False 21 NULL
59 ENG 23 14 New & Used Dealers False 2 NULL
60 ENG 24 14 Motorcycles False 3 NULL
61 ENG 25 14 Motorized Vehicles False 4 NULL
62 ENG 26 14 Parts & Supplies False 5 NULL
63 ENG 27 14 Repairs Body Work False 6 NULL
64 ENG 28 14 Repairs - Mechanical False 7 NULL
66 ENG 29 14 Roadside Assistance False 8 NULL
67 ENG 30 14 Trailers False 9 NULL
68 ENG 31 14 Related Services False 10 NULL
70 ENG 32 14 Detailing & Aesthetics False 11 NULL
71 ENG 33 16 Alterations & Services False 1 NULL
72 ENG 34 16 Accessories False 2 NULL
73 ENG 35 16 Clothing - General False 3 NULL
74 ENG 36 16 Footwear False 4 NULL
75 ENG 37 16 Outerwear False 5 NULL
76 ENG 38 16 Uniforms & Work Clothing False 6 NULL
77 ENG 39 17 Amusement Centres False 1 NULL
78 ENG 40 17 Artists & Entertainers False 2 NULL
79 ENG 41 17 Auditoriums & Halls False 3 NULL
80 ENG 42 17 Casinos & Gambling False 4 NULL
82 ENG 43 17 Circus, Festivals & Outdoor Events False 5 NULL
83 ENG 44 17 Games, Hobbies & Parties False 6 NULL
84 ENG 45 17 Music False 7 NULL
85 ENG 46 17 Radio False 8 NULL
86 ENG 47 17 Theatre False 9 NULL
87 ENG 48 17 Video, Movies & Television False 10 NULL
88 ENG 49 17 Night Life False 11 NULL
89 ENG 50 17 Adult Entertainment False 12 NULL
90 ENG 51 17 Agents, Agencies & Organizations False 13 NULL
91 ENG 52 17 Tickets & Seats False 14 NULL
92 ENG 53 17 News & Information False 15 NULL
93 ENG 54 19 Administrative & Specialty Services False 1 NULL
94 ENG 55 19 Advertising False 2 NULL
95 ENG 56 19 Associations & Organizations False 3 NULL
96 ENG 57 19 Communication & Audio-Visual False 4 NULL
97 ENG 58 19 Consultants False 5 NULL
98 ENG 59 19 Document Services False 6 NULL
99 ENG 60 19 Employment & Career Resources False 7 NULL
100 ENG 61 19 Furniture & Office/Industrial Machines False 8 NULL
101 ENG 62 19 Import & Export Services False 9 NULL
102 ENG 63 19 Marketing & Sales False 10 NULL
103 ENG 64 19 Office Supplies - General False 11 NULL
104 ENG 65 19 Printing, Publishing & Copying False 12 NULL
105 ENG 66 19 Shipping, Packaging & Postal Services False 13 NULL
106 ENG 67 19 Trade Shows, Expositions & Conventions False 14 NULL
107 ENG 68 19 Engineers - Consulting False 15 NULL
108 ENG 69 22 Academic False 1 NULL
110 ENG 70 22 Libraries False 2 NULL
111 ENG 71 22 Services & Supplies False 3 NULL
112 ENG 72 22 Specialized Schools False 4 NULL
113 ENG 73 23 Communications Services & Equipment False 1 NULL
114 ENG 74 23 Computer Equipment False 2 NULL
115 ENG 75 23 Computer Services False 3 NULL
116 ENG 76 23 Electronics - Services & Equipment False 4 NULL
117 ENG 77 23 Information Systems False 5 NULL
118 ENG 78 23 Internet False 6 NULL
119 ENG 79 24 Children & Child Care False 1 NULL
120 ENG 80 24 Pet Services False 2 NULL
121 ENG 81 24 Pet Supplies False 3 NULL
122 ENG 82 24 Community Services & Organizations False 4 NULL
123 ENG 83 24 Government agencies and services False 5 NULL
124 ENG 84 25 Accounting Services False 1 NULL
125 ENG 85 25 Banks & Financial Services False 2 NULL
126 ENG 86 25 Financial industry - Supplies False 3 NULL
127 ENG 87 25 Investment services False 4 NULL
128 ENG 88 25 Loans & Credit Services False 5 NULL
129 ENG 89 25 Retirement & Financial Planning False 6 NULL
130 ENG 90 25 Taxes False 7 NULL
132 ENG 91 25 Legal False 8 NULL
133 ENG 92 26 Bars, Pubs & Cafes False 1 NULL
134 ENG 93 26 Beverages False 2 NULL
135 ENG 94 26 Desserts & Sweets False 3 NULL
136 ENG 95 26 Facilities & Services False 4 NULL
137 ENG 96 26 Foods - General False 5 NULL
139 ENG 97 26 Restaurants False 6 NULL
140 ENG 98 27 Alternative Health Care False 1 NULL
143 ENG 99 27 Health Associations & Organizations False 2 NULL
144 ENG 100 27 Dental Care False 3 NULL
146 ENG 101 27 Diet & Nutrition False 4 NULL
147 ENG 102 27 Eye Care False 5 NULL
148 ENG 103 27 Fitness & Exercise False 6 NULL
149 ENG 104 27 Hospitals & Clinics False 7 NULL
150 ENG 105 27 Long Term Care False 8 NULL
151 ENG 106 27 Medical Products, Equipment & Services False 9 NULL
152 ENG 107 27 Physicians & Surgeons False 10 NULL
154 ENG 108 27 Therapy & Counseling False 11 NULL
155 ENG 109 28 Accessories - General False 1 NULL
156 ENG 110 28 Appliances False 2 NULL
157 ENG 111 28 Cabinets, Racks & Organizers False 3 NULL
158 ENG 112 28 Cleaning Supplies & Services False 4 NULL
159 ENG 113 28 Cooling & Ventilating False 5 NULL
160 ENG 114 28 Doors False 6 NULL
161 ENG 115 28 Furniture False 7 NULL
162 ENG 116 28 Garden & Lawn False 8 NULL
163 ENG 117 28 General Supplies & Services False 9 NULL
164 ENG 118 28 Heating False 10 NULL
166 ENG 119 28 Housewares False 11 NULL
167 ENG 120 28 Interior Decoration False 12 NULL
168 ENG 121 28 Kitchen & Bath Supplies & Services False 13 NULL
169 ENG 122 28 Lighting False 14 NULL
170 ENG 123 28 Moving & Storage False 15 NULL
171 ENG 124 28 Outdoor Accessories & Equipment False 16 NULL
172 ENG 125 28 Outdoor Services False 17 NULL
173 ENG 126 28 Painting & Floor & Wall Coverings False 18 NULL
174 ENG 127 28 Safety & Security False 19 NULL
175 ENG 128 28 Windows False 20 NULL
177 ENG 129 28 Pest Control False 21 NULL
178 ENG 130 28 Bedroom & Linens False 22 NULL
179 ENG 131 29 Barbers, Beauty Salons & Spas False 1 NULL
180 ENG 132 29 Body Art & Piercing False 2 NULL
181 ENG 133 29 Cosmetics False 3 NULL
182 ENG 134 29 Skin & scalp services & products False 4 NULL
183 ENG 135 29 Supplies False 5 NULL
184 ENG 136 30 Building Management False 1 NULL
185 ENG 137 30 Real Estate False 2 NULL
186 ENG 138 30 Insurance False 3 NULL
187 ENG 139 31 Antiques False 1 NULL
188 ENG 140 31 Apparel & Accessories False 2 NULL
189 ENG 141 31 Arts Supplies & Services False 3 NULL
191 ENG 142 31 Books False 4 NULL
192 ENG 143 31 Florists False 5 NULL
193 ENG 144 31 Games & Toys False 6 NULL
194 ENG 145 31 Gifts & Novelty False 7 NULL
195 ENG 146 31 Party Supplies & Services False 8 NULL
196 ENG 147 31 Shopping Services & Supplies False 9 NULL
197 ENG 148 31 Stores - General False 10 NULL
198 ENG 149 31 Tobacco Products False 11 NULL
199 ENG 150 31 Discount, Used & Rental Goods False 12 NULL
200 ENG 151 32 Associations & Clubs False 1 NULL
here is the method to populate the tree view...
Dictionary<string, int> values = new Dictionary<string, int>();
SqlDataReader reader = beeDBobj.runStoredProc("usp_GetCategories", new string[,] { { "@langID", Session["lang"].ToString() } });
DataSet ds = Common.convertDataReaderToDataSet(reader);
while (reader.Read()) {
values.Add(reader["CategoryName"].ToString(), Int32.Parse(reader["level"].ToString()));
}
Stack<TreeNodeCollection> s = new Stack<TreeNodeCollection>();
s.Push(CatTree.Nodes);
TreeNode lastNode = null;
TreeNode currentNode;
int currentLevel = 3;
foreach (KeyValuePair<string, int> value in values) {
currentNode = new TreeNode(value.Key);
if (currentLevel > value.Value) //Move down
{
s.Push(lastNode.ChildNodes);
}
else if (currentLevel < value.Value)//Move up
{
s.Pop();
}
s.Peek().Add(currentNode);
currentLevel = value.Value;
lastNode = currentNode;
}
}
ASKER
Never mind.
Thanks again!
Thanks again!
1) Drop OwnerId and SubOwnerId , Create a column called ParentCategoryId, Now the table could support more than 3 level of hierarchy.
2) Here is a sample query to retrieve the data in tree view order
--------------------------
with result(catid,categoryname,
as
(
select catid,categoryname,3,row_n
from categories
where parentid = 0
union all
select c.catid,c.categoryname,r.l
from categories c inner join result r on c.parentid = r.catid
)
select * from result order by orderby
--------------------------
The query is hard coded to support 3 level in
select catid,categoryname,3,row_n
The query is hard coded to support every category can only have 99 immediate subcategories.
Power(10,(level - 1) * 2) (if you can the 2 to 3 then it could support 999)
The max for t-sql is Power(10,9), so it's not support many levels. If you want to support more levels, use a varchar type for ordering.
Now you have the result something like
1 Automobile 3 1000000
2 Beauty 3 2000000
3 Spa 2 2010000
4 Facials 1 2010100
3) In your code
Add a Stack to track tree nodes and go through the result set one line by one line to build the tree. (Push a node to Stack when level goes smaller, Pop when it goes bigger)
Hope it's enough information for you