Rhyous

February 17, 2010

How read use an ADO.NET DataSet to read XML files designed with nested attributes?

Filed under: C# (C-Sharp) — J. Abram barneck @ 2:32 pm

I am working on a project that is XML driven and I am using ADO.NET DataSet functionality to make reading the XML easier. However, I ran into a problem that really just a lack of knowledge on my part.

Problem
So I have DataSet created using an XML. The XML is using Nested attributes. And I just need to know how to loop properly through the DataSet Tables and their columns.

I have the following XML.

<?xml version="1.0" encoding="utf-8" ?>
<Plugin PluginName="TestName" GroupName="Operating System Settings" Type="Single">
  <Title>Plugin 1</Title>
  <StartTime>1:57:47 PM 2/16/2010</StartTime>
  <EndTime>1:58:03 PM 2/16/2010</EndTime>
  <Description>Runs the TestName process to determine something.</Description>
  <Section SectionName="Section1">
    <Field FieldName="Field Name">
      <Value Operand="EQ">Some Correct Setting1</Value>
      <ActionPlugin Name="" Type="Link" URL="">
        <Executable>SomeAction1.exe</Executable>
        <Parameters>Param1 Param2</Parameters>
      </ActionPlugin>
    </Field>
    <Field FieldName="Field2">
      <Value Operand="RG">900</Value>
      <WarningLevel>10%</WarningLevel>
      <ErrorLevel>20%</ErrorLevel>
      <ActionPlugin Name="ActionPlugin1" Type="Link" URL="http://www.somesite.tld/some/path/file.htm" />
    </Field>
  </Section>
  <Section SectionName="Section2">
    <Field FieldName="Field1">
      <Value Operand="EQ">Some Correct Setting2</Value>
      <ActionPlugin Name="" Type="Link" URL="">
        <Executable>SomeAction2.exe</Executable>
        <Parameters>Param1 Param2</Parameters>
      </ActionPlugin>
    </Field>
    <Field FieldName="Field2">
      <Value Operand="RG">900</Value>
      <WarningLevel>10%</WarningLevel>
      <ErrorLevel>20%</ErrorLevel>
      <ActionPlugin Name="ActionPlugin1" Type="Link" URL="http://www.somesite.tld/some/path/file.htm" />
    </Field>
  </Section>
  <Section SectionName="Section3">
    <Field FieldName="Field1">
      <Value Operand="EQ">Some Correct Setting3</Value>
      <ActionPlugin Name="" Type="Link" URL="">
        <Executable>SomeAction3ds.exe</Executable>
        <Parameters>Param1 Param2</Parameters>
      </ActionPlugin>
    </Field>
    <Field FieldName="Field2">
      <Value Operand="RG">900</Value>
      <WarningLevel>10%</WarningLevel>
      <ErrorLevel>20%</ErrorLevel>
      <ActionPlugin Name="ActionPlugin1" Type="Link" URL="http://www.somesite.tld/some/path/file.htm" />
    </Field>
  </Section>
</Plugin>

So the DataSet is created with these tables (this is copied from the debugger):

– List Count = 5 System.Collections.ArrayList
+ [0] {Plugin} object {System.Data.DataTable}
+ [1] {Section} object {System.Data.DataTable}
+ [2] {Field} object {System.Data.DataTable}
+ [3] {Value} object {System.Data.DataTable}
+ [4] {ActionPlugin} object {System.Data.DataTable}

Table [1] {Section} has 3 rows.
Table [2] {Field} has 6 rows.

So the data looks like this:

Sections Table
Row 1
Row 2
Row 3

Fields Table
Row 1
Row 2
Row 3
Row 4
Row 5
Row 6

But I need to read it as follows:

Sections Table
Row 1  
Fields Table
Row 1
Row 2
Row 2  
Fields Table
Row 3
Row 4
Row 3  
Fields Table
Row 5
Row 6

So I had the code below, but for each Section Row it would loop through all six field rows, which is not what I intend.

string mFullPathToXML = "C:\My.xml";
DataSet ds;
ds.ReadXml(mFullPathToXML);

foreach (DataRow SectionRow in ds.Tables["Section"].Rows)
{
    foreach(DataRow FieldRow in ds.Tables["Field"].Rows) 
    {
        // Looping through all rows, not just those that pertain to the section.
        // How to get only the two rows that belong to each Section row here?
    }
}

Solution
Well, I set out on a journey to figure this out. In a few search engines I use search phrases like:
DataSet XML Nested
DataSet XML Nested Relation
DataSet DataTable XML Nested Row
DataSet DataTable XML Nested Row

A lot of documentation on Microsoft’s site to XMLs and DataSets showed up, but nothing describing this problem/solution.

I am happy to say that with help from the MSDN Forums, the solution was found. Please read my post here:
http://social.msdn.microsoft.com/Forums/en-US/adodotnetdataset/thread/2d115ba6-49be-4a5c-bf92-054626109f50

So the solutions is to use the Section_Id assigned to each row in the Sections table inside the Field table’s Select() function as shown:

foreach (DataRow sectionRow in ds.Tables["Section"].Rows)
{
	string sectionId = sectionRow["Section_Id"].ToString();
	Console.WriteLine("Section: {0}", sectionRow["SectionName"]);
	foreach (DataRow fieldRow in ds.Tables["Field"].Select("Section_Id = " + sectionId))                
	{
		foreach (object item in fieldRow.ItemArray)
		{
			// Do something here
		}
	}
}

This solution works for me.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: