how to convert Database Hierarchical Data to XML using ASP.net 3.5 and LINQ

Question

hello guys!
i have a table with hierarchical structure. like this:

and table data shown here:

this strategy gives me the ability to have unbounded categories and sub-categories.

i use ASP.net 3.5 SP1 and LINQ and MSSQL Server2005. How to convert it to XML? I can Do it with Dataset Object and ".GetXML()" method. but how to implement it with LINQtoSQL or LINQtoXML??? or if there is another simpler way to perform that? what is your suggestion? the best way? I searched the web but found nothing for .net 3.5 featuers.

Question Update
thnks to Murph but Now I have a New Problem. I want to Make a Dunamic SiteMap file in my Project. You Know the SiteMap file in ASP.net looks like this:

<siteMapNode url="~/Category?cid=0" title="Home"  description="Home">

    <siteMapNode url="~/Category?cid=1" title="a"  description="" />

    <siteMapNode url="~/Category?cid=2" title="b"  description="" >

        <siteMapNode url="~/Category?cid=3" title="c"  description="" />

        <siteMapNode url="~/Category?cid=4" title="d"  description="" />

    </siteMapNode>
</siteMapNode>

Main Problem is that according to Mr.Murph's Code the sub categories will nested in element. but in SiteMap Case we have no such a elements and all of categories and sub categories are nested in elements. how can I change Mr.Murph code to shape this Schema?

Answer 1

Ok very quickly (and thoroughly untested).

To "roll your own" XML using Linq to SQL and Linq to XML (which are generic .NET not ASP.NET specific) is fairly simple, this should be sufficient given a few assumptions which I'll detail after (now modified slightly as suggested):

void Main()
{
    DataContext dc = new DataContext();

    menuXML = new XDocument();
    XElement root = new XElement("menuxml",
        from m in dc.Menus
        where m.ParentID == null
        select GetMenuXML(m));

    menuXML.Add(root);
    // You've now got an XML document that you can do with as you need
    // For test you can save...
    menuXML.Save("filename.xml");
}

private static XElement GetMenuXML(Menu menu) 
{ 
    return new XElement("category",  
        new XAttribute("MenuID", menu.MenuID), 
        new XAttribute("Text", menu.Text), 
        new XElement("Description", menu.Description), 
        new XElement("menus", menu.Menus.Select(m => GetMenuXML(m)))); 
}

Ok, from the top

• I'm assuming a Linq to SQL data context called DataContext in which you've got the table mapped to Menus and that the parent/child relation is ChildMenus at the parent end.
• We create a new document
• We create a new element that we'll use as the root that we'll call menuxml
• We then do a linq to SQL query to select all the menu entries that don't have a parent and call GetMenuXML to output the XML for a single menu record (i.e that record and its children) for those entries.
• GetMenuXML outputs a single menu entry split betwixt attributes and elements the only slightly different thing is that I've used a lamba expression instead of the verbose syntax for generating the child menus - but if I've got it right (remember no test!) thats doing something along the lines of

from m in menu.ChildMenus select GetMenuXML(m)

If it works (!) you should get XML something like

<menuxml>
    <menu MenuID="1" Text="Product">
        <Description>A list of products</Description>
        <menus>
            <menu MenuID="6" Text="Background">
                <Description>Product Background</Description>
                <menus>
                    <menu MenuID="18" Text="Internet Restriction">
                        <Description>Internet Restriction</Description>
                        <!-- children if any would be here -->
                    </menu>
                    <menu MenuID="19" Text="Speed Solution">
                        <Description>Speed Solutions</Description>
                    </menu>
                </menus>
            </menu>
            <menu MenuID="7" Text="Background">
                <Description>Product Details</Description>
            </menu>
        </menus>
    </menu>
    <!-- rest of the top level menus -->
</menuxml>

Answer 2

thank you dear Murph. I used your code and it worked well. but as you say, you didn't test your code so GetMenuXML() had some mistakes. here is the correction:

private static XElement GetMenuXML(Menu menu)
{
    return new XElement("category", 
        new XAttribute("MenuID", menu.MenuID),
        new XAttribute("Text", menu.Text),
        new XElement("Description", menu.Description),
        new XElement("menus", menu.Menus.Select(m => GetMenuXML(m))));
} 

Answer 3

Hey there, I used this code (with small modifications) in conjunction with the example referred to by mahdiahmadirad, to produce a custom control (ASP.NET). This builds correctly and the HTML looks ok but it doesn't generate any XML? It seems silly but it when debugging it just skips over the LINQ to SQL component as if there is no data in my database. (I assure you there is). Can anyone see what I'm doing wrong? (The value for root in my debug window is just the 'menuXML' node.

using System;
using System.Configuration;
using System.Data;
using System.Data.Linq;
using System.Data.SqlClient;
using System.Linq;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Xml.Linq;



namespace Virtual
{
    public class MainMenu : WebControl
    {
        public MainMenu()
        {


        }


        protected override void CreateChildControls()
        {
            //Define working objects
            Menu _mnu = new Menu();
            Virtual.linq.linqDataContext _dc = new Virtual.linq.linqDataContext();
            XDocument _menuXML = new XDocument();
            XmlDataSource _xmlDS = new XmlDataSource();

            //Define LINQ to SQL Query
            XElement root = new XElement("menuXML",
                            from m in _dc.menus
                            where m.parentid == null
                            select GetMenuXML(m));

            _menuXML.Add(root);

            _xmlDS.Data = _menuXML.Document.ToString();

            _mnu.DataSource = _xmlDS;

            RenderMenu(_mnu);


            this.Controls.Add(_mnu);
            this.Controls.Add(_xmlDS);

        }


        private static XElement GetMenuXML(Virtual.linq.menu menu)
        {
            return new XElement("category",
                new XAttribute("MenuID", menu.id),
                new XAttribute("Text", menu.text),
                new XElement("Description", menu.description),
                new XElement("menus", menu.childMenus.Select(m => GetMenuXML(m))));
        }


        private static void RenderMenu(Menu _mnu)
        {
            //Set Behaviour of Control
            _mnu.Orientation = Orientation.Horizontal;
            _mnu.StaticDisplayLevels = 1;
            _mnu.MaximumDynamicDisplayLevels = 2;
            _mnu.Enabled = true;

            //Set CSS Classes To Control Look and Feel
            _mnu.CssClass = "Menu";
            _mnu.StaticMenuStyle.CssClass = "Menu";
            _mnu.StaticMenuItemStyle.CssClass = "MenuItem";
            _mnu.StaticHoverStyle.CssClass = "MenuItemHover";
            _mnu.StaticSelectedStyle.CssClass = "MenuSelectedStyle";
            _mnu.DynamicMenuStyle.CssClass = "SubMenu";
            _mnu.DynamicMenuItemStyle.CssClass = "SubMenuItem";
            _mnu.DynamicHoverStyle.CssClass = "SubMenuHover";
            _mnu.DynamicSelectedStyle.CssClass = "SubMenuSelectedStyle";
        }


    }
}