Create XML from a hierarchical Dataset in C#

Question

I have a Dataset which I created using Recursion in SQL,

Parent        UserId   Child     Reporting_To_UserId   Depth        id
Aditya         13     Abhishek     4                   0            13
Abhishek       4      Saurabh      6                   1            16
Abhishek       4      Mohinder     8                   1            17
Mohinder       8      Mohammad     14                  2            18
Saurabh        6      Rahul        1                   2            11
Saurabh        6      Amitesh      5                   2            12

Now I want to generate a XML which should look like this:-

 <Person name="Aditya" User_Id="13">

    <Person name="Abhishek" User_Id="4">

           <Person name="Mohinder" User_id="8">
               <Person name="Mohammad" User_id="14"/>
           </Person>         

           <Person name="Saurabh" User_Id="6">
              <Person name="Rahul" User_Id="1"/>
              <Person name="Amitesh" User_Id="5"/>
           </Person>

     </Person>

 </Person>

I want to create a Hierarchical XML using the Parent and Child relation from a Dataset.

Answer 1

I think you could utilize following piece of code:

 protected void Page_Load(object sender, EventArgs e)
    {
        DataSet ds = new DataSet();
        string connStr = @"Data Source=MY-PC\SQLExpress;Initial Catalog=DataDB;User Id=ME;Password=YourPassword;Trusted_Connection=True;";
        using (SqlConnection conn = new SqlConnection(connStr))
        {
            string sql = "Select MenuID, Text,Description, ParentID from UserInfo";
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            da.Fill(ds);
            da.Dispose();
        }
        ds.DataSetName = "UserInfos"; //You can start directly from here as you have the dataset just mantain Parent and Child ID Proper
        ds.Tables[0].TableName = "UserInfo";
        DataRelation relation = new DataRelation("ParentChild",
         ds.Tables["UserInfo"].Columns["MenuID"],
         ds.Tables["UserInfo"].Columns["ParentID"], true);

        relation.Nested = true;
        ds.Relations.Add(relation);  //XmlDataSource1 is any source of xml you can have this in file also
        XmlDataSource1.Data = ds.GetXml(); //Here Dataset will automatically generate XML for u based on relations added

    }

Answer 2

I think it can be done with recursive LINQ, but I still need to figure out how to write it right, so here is a solution with recursive method:

First, you declare method (I've done search by Name, but you can also do it with Id):

public static IEnumerable<XElement> BuildXML(string Parent, DataTable dt)
{
    string filter = string.Format("[Parent] = '{0}'", Parent);
    return from x in dt.Select(filter)
            select new XElement("Person",
                        new XAttribute("Name", x["Child"]),
                        new XAttribute("User_Id", x["Reporting_To_UserId"]),
                        BuildXML(x["Child"].ToString(), dt)
                    );
}

Then, you call it with your parent element (I've added a top row, otherwise query will be more complicated):

var dt = new DataTable();
dt.Columns.AddRange(new[] {
    new DataColumn("Parent"),
    new DataColumn("UserId"),
    new DataColumn("Child"),
    new DataColumn("Reporting_To_UserId"),
    new DataColumn("Depth"),
    new DataColumn("id")
});
dt.Rows.Add(new object[] { "", 0, "Aditya", 13, 0, 12 });
dt.Rows.Add(new object[] {"Aditya", 13, "Abhishek", 4, 0, 13});
dt.Rows.Add(new object[] { "Abhishek", 4, "Saurabh", 6, 1, 16 });
dt.Rows.Add(new object[] { "Abhishek", 13, "Mohinder", 8, 1, 17 });
dt.Rows.Add(new object[] { "Mohinder", 8, "Mohammad", 14, 2, 18 });
dt.Rows.Add(new object[] { "Saurabh", 6, "Rahul", 1, 2, 11 });
dt.Rows.Add(new object[] { "Saurabh", 6, "Amitesh", 5, 2, 12 });

var result = BuildXML("", dt);

Now you have IEnumerable<XElement>, to turn it into string, you can do follows:

var xml = result.
            Select(e => e.ToString()).
            Aggregate((current, next) => current + next);