Database Driven Menu using LINQ and stored Procedures

[barriegrant1]

hey, im currently creating an e-commerce store, im quite new to ASP.NET and C#. im trying to create a menu that is generated based on the database tables. i have created a stored procedure, which works correctly:

ALTER PROCEDURE dbo.getProductsForCat
	
AS
	SELECT * FROM products_cat WHERE product_cat_id IN 
( SELECT product_cat_id FROM products ) 


SELECT p.product_id, p.product_name,p.product_cat_id FROM products p 
	RETURN

im not too sure how to bind the results to some kind of html output. im unsure if the code below is in the right direction?

using (api_prodDataContext api = new api_prodDataContext())
            {
                var results = from u in api.getProductsForCat()

                select u;

                foreach (var details in results)
             {
                   //some code?
             }

any help with this issue would be much much appreciated.:icon_cool:

[kvprajapati]

Have a look at codeproject article, hope it helps you.

[barriegrant1]

hey thanks, that was a good place to start. I wanted to use LINQ and Stored procedures. i have developed the menu using the following code

private void PopulateMenu2()
        {
           

            using (api_prodDataContext api = new api_prodDataContext())
            {
                TreeView tree = new TreeView();
               
                var result_cat = from c in api.getProductCategory()
                                 select c;


                foreach (var parentItem in result_cat)
                {

                    if (parentItem.order_num == 0) //hides category items whos order_num is 0;
                    {
                        parentItem.product_cat_desc = "";
                        
                    }

                    else
                    {
                          var categoryItem = new TreeNode();
                          categoryItem.Text = parentItem.product_cat_desc;
                          categoryItem.PopulateOnDemand = true;

                          categoryItem.CollapseAll();
                          categoryItem.SelectAction = TreeNodeSelectAction.None;

                            
                          var result_sub_cat = from s in api.getProductSubCat()
                                               where s.product_cat_id == parentItem.product_cat_id 
                                               select s;

                          foreach (var childItem in result_sub_cat)
                            {
                                    var childrenItem = new TreeNode();
                                    childrenItem.Text = childItem.description;
                                    categoryItem.ChildNodes.Add(childrenItem);
                                    categoryItem.SelectAction = TreeNodeSelectAction.SelectExpand;
                               
                            }

                            TreeView1.CollapseAll();
                            TreeView1.Nodes.Add(categoryItem);
                      }


                    TreeView1.DataBind();

                }
                   
            }
        }

this currently works fine, it generates the category and then the subcategory. I now need to link each subcategory (childitem) to a products page, but it will need to load items based on the 'result_sub_cat_id' when the user clicks the subcategory in the menu.