none
Load DropDownList with all categories under parent category , using LINQ RRS feed

  • Question

  • Hi ,
    I have a drop down list , I have to load the drop down list with all the menu names under the parent menu

    the table is like this

    menuid, parentmenuid, menuname

    1, null, superParent
    11, 1, firstparent
    12, 1, secondParent
    13, 1, thirdparent
    111, 11, parent111
    121, 12, parent121

    the LINQ metal generated  object will be for this table called optionsMenu

    example

    public OptionsMenu()
            {
                this._OptionsLanguages = default(EntityRef<OptionsLanguages>);
                this._ParentMenu = default(EntityRef<OptionsMenu>);
                this._Menu = new EntitySet<OptionsMenu>(new Action<OptionsMenu>(this.attach_Menu), new Action<OptionsMenu>(this.detach_Menu));
                this._VideoMenu = new EntitySet<VideoMenu>(new Action<VideoMenu>(this.attach_VideoMenu), new Action<VideoMenu>(this.detach_VideoMenu));
                this._TrackLog = new EntitySet<TrackLog>(new Action<TrackLog>(this.attach_TrackLog), new Action<TrackLog>(this.detach_TrackLog));
                OnCreated();
            }

            public int MenuID
            {
                get
                {
                    return this._MenuID;
                }
                set
                {
                    if ((this._MenuID != value))
                    {
                        this.OnMenuIDChanging(value);
                        this.SendPropertyChanging();
                        this._MenuID = value;
                        this.SendPropertyChanged("MenuID");
                        this.OnMenuIDChanged();
                    }
                }
            }


    The most important this is that the parent can have endless number of childs horizontally and vertically , I need to display in the drop down alll the menu names under the parent menu,

    I achieved this via writing T-  SQL Query , But my goal is to get this using LINQ Objects and C#

    Please help me write the appropriate C# methods in my busines service with appropriate LINQ Queries,

    I am posting how I did it with SQL


    In my page load :
    if(!IsPostBacK){
    DataTable dt = new DataTable();
                dt = OptionsMenuService.Current.GetAllCategories();

                this.ddlParentMenu.DataSource = dt;
                this.ddlParentMenu.DataTextField = "MenuName";
                this.ddlParentMenu.DataValueField = "MenuID";
                this.ddlParentMenu.DataBind();
    }

    In my business services

    public DataTable GetAllVODCategories()
            {
               
                DataTable returnTable = new DataTable();
                DataColumn idColumn = new DataColumn();
                idColumn.DataType = System.Type.GetType("System.Int32");
                idColumn.ColumnName = "MenuID";

                DataColumn parentIDColumn = new DataColumn();
                parentIDColumn.DataType = System.Type.GetType("System.Int32");
                parentIDColumn.ColumnName = "ParentMenuID";

                DataColumn menuNameColumn = new DataColumn();
                menuNameColumn.DataType = System.Type.GetType("System.String");
                menuNameColumn.ColumnName = "MenuName";

               

                SqlConnection objConn = new SqlConnection(@"server=localhost;database=SampleDb;Trusted_Connection=true");

                string queryText = "WITH CTE AS(SELECT MenuID, ParentMenuID,MenuName FROM optionsMenu WHERE ParentMenuID = 3 UNION ALL SELECT c.MenuID, c.ParentMenuID,c.MenuName FROM optionsMenu c JOIN CTE p ON c.ParentMenuID = p.MenuID)SELECT * FROM CTE";
                SqlCommand objCommand = new SqlCommand(queryText, objConn);

                SqlDataAdapter da = new SqlDataAdapter(objCommand);

               

                da.Fill(returnTable);

                return returnTable;
            }


    When I write my C# Methods for my business services, This is a template how I do(The following is just a template , has nothing to do with my question)
    public Dictionary<int, string> GetCategoryforVideoCategory()
            {
                SampleDB db = new SampleDB();
                Dictionary<int, string> returnDict = new Dictionary<int, string>();

                int menuID = this.GetMenuIDByMenuName("Video on Demand");

                var query = from q in db.OptionsMenu
                            where q.ParentMenuID == menuID
                            select q;
               

                foreach (var a in query)
                {
                    returnDict.Add(a.MenuID, a.MenuName);
                    var query2 = from j in db.OptionsMenu
                                 where j.ParentMenuID == a.MenuID
                                 select j;
                    foreach (var b in query2)
                    {
                        returnDict.Add(b.MenuID, b.MenuName);

                    }

                   
                }

                return returnDict;
              
            }

    ctrlnick
    Sunday, March 29, 2009 6:52 AM

Answers

  • Hi there, this seems like a re-post of the following:

    http://social.msdn.microsoft.com/Forums/en-US/linqtosql/thread/5b0a677d-7474-447b-8cde-2ae716286af4/

    In any case, here was my response - did it help?

    Hi there. I didn't have the chance to test the code below but I believe you need to use recursion for what you're trying to do. I am assuming your DropDownList is based on the Windows.Forms.MenuItem class. In which case, the code would be something like so:

    public void LoadDropDownList()
    {
     LoadDropDownListWorker(mainMenuItem, null);
    }

    private void LoadDropDownListWorker(MenuItem menu, int? parentId)
    {

     var parentItem = from item in db.OptionsMenu
        where item.menuid == parentId
        select item;

     MenuItem parent = new MenuItem();
     parent.Text = item.menuname;
     menu.MenuItems.Add(parent);


     var subItems = from item in db.OptionsMenu
          where item.ParentMenuID == parentId
          select item; // You might want to order by menuname

     if (subItems.Count() == 0)
      return; // no children

     foreach(OptionsMenu subItem in subItems)
     {
      LoadDropDownListWorker(parent, subItem.menuid); 
     } 
     
    }

    Wednesday, April 1, 2009 8:31 PM