Geek Answers Handbook

Creating XML from a hierarchical dataset in C #

I have a dataset that 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 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 hierarchical XML using a parent-child relationship from a dataset.

+7
source share
3 answers

I think you could use the following code snippet:

  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 }
+1
source

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

First, you declare a method (I searched by Name , but you can also do this 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 the parent element (I added the top line, otherwise the request will be more complex):

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

You now have an IEnumerable<XElement> to turn it into a string, you can do the following:

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

You can create a DataRelation in a dataset, for example

 DataRelation relation = new DataRelation("ParentChild", ds.Tables["UserInfo"].Columns["MenuID"], ds.Tables["UserInfo"].Columns["ParentID"], true); **relation.Nested = true;** ds.Relations.Add(relation); //

Attribution "Nested" is very important!

Makeyuan

0
source

More articles:


All Articles