Building Dynamic Treeview 3 levels. C#

You can try this:

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,level,orderby)
as
(
    select catid,categoryname,3,row_number() over (order by catid) * Power(10,3 * 2)
    from categories
    where parentid = 0
      union all
    select c.catid,c.categoryname,r.level - 1,r.orderby + row_number() over (order by c.catid) * Power(10,(level - 1) * 2)
    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_number() over (order by catid) * Power(10,3 * 2) (you can change the number if you want)
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

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

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

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.

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.

ok I'm going to try it and post back my results.

Thanks for your continued help!

What would the sql statement be if instead of ordering by the orderby field I just wanted to order them alphabetically?

Thanks

This is what I have so far...

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

Select allOpen in new window

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

>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.NullReferenceException: 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\Search.aspx.cs    Line: 73

Stack Trace:


[NullReferenceException: Object reference not set to an instance of an object.]
   Search.BuildCategoryTree() in d:\MyDomain\MyDomainV1.5\Search.aspx.cs:73
   Search.Page_Load(Object sender, EventArgs e) in d:\MyDomain\MyDomainV1.5\Search.aspx.cs:38
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +31
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +68
   System.Web.UI.Control.OnLoad(EventArgs e) +88
   System.Web.UI.Control.LoadRecursive() +74
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3022
 
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;
        }

Select allOpen in new window

it works!


thanks SO much!

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("usp_GetCategories", new string[,] { { "@langID", Session["lang"].ToString() } });
Line 251:        while (reader.Read()) {
Line 252:            values.Add(reader["CategoryName"].ToString(), Int32.Parse(reader["level"].ToString()));
Line 253:        }
Line 254:
 

Source File: d:\hosting\member\MyDomain\site1\ManageCategories.aspx.cs    Line: 252

Stack Trace:


[ArgumentException: An item with the same key has already been added.]
   System.ThrowHelper.ThrowArgumentException(ExceptionResource resource) +48
   System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add) +2653240
   System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value) +11
   ManageCategories.BuildCatPreviewTree() in d:\hosting\member\MyDomain\site1\ManageCategories.aspx.cs:252
   ManageCategories.Page_PreRender(Object sender, EventArgs e) in d:\hosting\member\MyDomain\site1\ManageCategories.aspx.cs:79
   System.Web.Util.CalliHelper.EventArgFunctionCaller(IntPtr fp, Object o, Object t, EventArgs e) +15
   System.Web.Util.CalliEventHandlerDelegateProxy.Callback(Object sender, EventArgs e) +33
   System.EventHandler.Invoke(Object sender, EventArgs e) +0
   System.Web.UI.Control.OnPreRender(EventArgs e) +2117788
   System.Web.UI.Control.PreRenderRecursiveInternal() +86
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +2041

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

Select allOpen in new window

Never mind.

Thanks again!