Datatables and relations

Hi all,

I'm trying to read an xml file that looks like this.

Code:

---

<?xml version="1.0" encoding="utf-16"?>
<TestApp xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="TestApp">
  <Application>
    <Name>146</Name>
    <Version>90</Version>
  </Application>
  <User>
    <Name>Test</Name>
    <RequiredLanguages>
      <Language>English</Language>
      <Language>French</Language>
    </RequiredLanguages>
  </User>
  <User>
    <Name>Test2</Name>
    <RequiredLanguages>
      <Language>Dutch</Language>
      <Language>English</Language>
    </RequiredLanguages>
  </User>
</TestApp>

---

When using DataSet.ReadXml I see some extra columns being added with names ending with _Id. I could write some custom code to play with these _Id's but I figure there must be a better way. Googling learned me a lot but not what I need in my case.

How can I, for example, easilly get the reguired languages for the second user out of the dataset? Please provide a brief code example. Thank you,

Jef

You will be much better of if you work with an explicit (valiatable) schema...

i have developed a XML Viewer Program that can give you some clue.
the core of my application is base on XmlDocument and XmlNode classes.
the code is:

Code:

---

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Xml;

namespace xmlTest
{
    public partial class Form1 : Form
    {
        private int counter = 0;
        private XmlNode theNode;
        private XmlDocument doc;

        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            doc = new XmlDocument();
            doc.Load("../../xmltest.xml");
            theNode = doc.FirstChild;
            displayNode();
           
        }

        private void displayNode()
        {
            //handle all the straight text properties
            lblCurName.Text = theNode.Name;
            lblCurValue.Text = theNode.Value;
            lblInnerXml.Text = theNode.InnerXml;
            lblInnerText.Text = theNode.InnerText;
            lblInnerText.BackColor = this.BackColor;
            lblInnerXml.BackColor = BackColor;

            //handle the parent button
            btnParent.Text = theNode.ParentNode.Name;

            if (theNode.ParentNode.NodeType == XmlNodeType.Element)
                btnParent.Enabled = true;
            else btnParent.Enabled = false;

            // handle next sibling button
            if (theNode.NextSibling == null) btnNext.Enabled = false;
            else btnNext.Enabled = true;

            // handle Previous sibling button
            if (theNode.PreviousSibling == null) btnPrev.Enabled = false;
            else btnPrev.Enabled = true;

            //populate the list box with children
            XmlNode childNode;
            lstChildren.Items.Clear();
            if (theNode.HasChildNodes)
            {
                childNode = theNode.FirstChild;
                while (childNode != null)
                {
                    lstChildren.Items.Add(childNode.Name
                    + " − " + childNode.InnerText);
                    childNode = childNode.NextSibling;
                } // end while
            } // end if statement
        }

        private void btnNext_Click(object sender, EventArgs e)
        {
            theNode = theNode.NextSibling;
            lblInnerXml.ScrollBars = ScrollBars.Both;
            lblInnerText.ScrollBars = ScrollBars.Both;
            displayNode();
        }

        private void btnParent_Click(object sender, EventArgs e)
        {
            theNode = theNode.ParentNode;
            displayNode();
        }

        private void btnRoot_Click(object sender, EventArgs e)
        {
            theNode = doc.FirstChild;
            displayNode();
        }

        private void lstChildren_DoubleClick(object sender, EventArgs e)
        {
            if (lstChildren.Items.Count > 0)
            {
                theNode = theNode.ChildNodes[lstChildren.SelectedIndex];
                displayNode();
            }
            else MessageBox.Show("There is no item in the list box");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            if (opener.ShowDialog() != DialogResult.Cancel)
            {
                doc.Load(opener.FileName);
                theNode = doc.FirstChild;
                displayNode();
            }//endif
               
        }

        private void btnprev_Click(object sender, EventArgs e)
        {
            theNode = theNode.PreviousSibling;
            displayNode();
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            this.Opacity += 0.1;
            lblInnerText.Visible = false;
          counter = counter^1;
            if (counter == 0)
                this.Text = "-";
            else this.Text = "--";

            if (this.Opacity == 1)
            {
                if (lbltoraj.Top > 478)
                {
                    lbltoraj.Top -= 2;
                    lblyahoo.Top -= 2;
                }
                                         
                    this.Text = "XML Viewer - Version 1.0";
            }
                   
          //  label7.Text = Convert.ToString(counter); //
          if (lbltoraj.Top == 478)
          {
            timer1.Enabled = false;
            lblInnerText.Visible = true;
          }
        }

        private void Form1_DoubleClick(object sender, EventArgs e)
        {
            MessageBox.Show("Contact: [email protected]");
        }
    }
}

---

sorry for bad GUI; i wrote it for test.
i have attached the source code; you can compile/ debug it with Visual Studio 2005 (or higher; but i have not tested with higher version).

Quote:

---

Originally Posted by TheCPUWizard

You will be much better of if you work with an explicit (valiatable) schema...

---

I agree. The problem is that I am not an expert in this and that, when I try to write a schema I get stuck for sure. So what I tried is first reading the xml and the generating the schema by using the method WriteSchema to then experiment and modify the schema.

This works quite well for the two dimensional cases. i.e. dataset with datatables. But now I would like to experiment with a third dimension which brings in these relations that I do not master.

I hope someone can help me with a simple example.

@toraj58
I see how you can display the inner text but how for example would you add or change a language?
BTW, I like the fading in of the application ;-)

thanks for the help so far!

Jef

1) Clread the complex XML file with ALL of the data that you eventualy want.
2) Load THIS File into the DataSEt
3) Write the dataset (which alreadfy has all of the relations since it is loaded from a "complex" XML).

If I read the example xml from my first post I don't understand how I can get a list/column/row/whatever of all required languages for user Test2. I don't understand how to interprete the extra columns with the _Id suffix that are automatically created.

How can I for example check the languages for user Test2 add a language for user Test2?

Thank you

Quote:

---

Originally Posted by Jef Patat

If I read the example xml from my first post I don't understand how I can get a list/column/row/whatever of all required languages for user Test2. I don't understand how to interprete the extra columns with the _Id suffix that are automatically created.

How can I for example check the languages for user Test2 add a language for user Test2?

Thank you

---

Please perform the three steps, and post the resulting XSD file!!!!! :rolleyes:

Quote:

---

Originally Posted by Jef Patat

@toraj58
I see how you can display the inner text but how for example would you add or change a language?
BTW, I like the fading in of the application ;-)

---

for adding, changing issue i have wriiten another XML test Application that work with a contact XML file
i revised it somehow to apply to your problem.

and i added DataSet and DataGrid Class to it.
the main methods and properties for manipulating XML are:


doc.Save(); // where doc is: XmlDocument doc
doc.Load();
ds.ReadXml(); // where ds is: DataSet ds
dg.DataSource = ds; // where dg is a DataGrid Control
dg.DataMember = ds.Tables["person"].ToString();
theNode = person.Clone(); // where theNode is: XmlNode theNode
theNode = person.Clone();
contacts.AppendChild(theNode); // where theNode is: XmlNode contacts

and as before: InnerText property;


for adding a new record i used a trick that i should explain you:
here is the code in the ADD Button Click Event:

Code:

---

        private void button1_Click(object sender, EventArgs e)
        {
            //duplicate the person node
            XmlNode contacts;
            XmlNode person;
            XmlNode root;
            root = doc.FirstChild;
            contacts = root.NextSibling;
            person = contacts.FirstChild;
            theNode = person.Clone();
            //copy node values from text boxes
            theNode["name"].InnerText = txtName.Text;
            theNode["address"].InnerText = txtAddress.Text;
            theNode["phone"].InnerText = txtPhone.Text;
            //add the new node to contacts
            contacts.AppendChild(theNode);
            doc.Save(fileName);
        }

---

i used NextSibling then FirstChild to point to first contact record then i cloned it finally i updated the clone from the data entered by the user in the
text boxes and i used AppendChild() method to appened the new person info to contact node and then saved to the XML file.

the coplete code is this:

Code:

---

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.IO;

namespace xmlContact
{
    public partial class Form1 : Form
    {
        private XmlDocument doc;
        private XmlNode theNode;
        private string fileName = "practice.xml";
        DataSet ds;

        public Form1()
        {
            InitializeComponent();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            doc = new XmlDocument();
            XmlElement contacts;
            XmlElement person;

            if (!File.Exists(fileName))
            {
                //initialize
                //create root node
                theNode = doc.CreateXmlDeclaration("1.0", "UTF-8", "yes");
                doc.AppendChild(theNode);
                //create contacts node
                contacts = doc.CreateElement("contacts");
                doc.AppendChild(contacts);
                //create first address
                person = doc.CreateElement("person");
                contacts.AppendChild(person);
                //create address elements
                theNode = doc.CreateElement("name");
                theNode.InnerText = "Roger Dodger";
                person.AppendChild(theNode);
                theNode = doc.CreateElement("address");
                theNode.InnerText = "123 W 4th St.";
                person.AppendChild(theNode);
                theNode = doc.CreateElement("phone");
                theNode.InnerText = "123−4567";
                person.AppendChild(theNode);
            }
        }

        private void button3_Click(object sender, EventArgs e)
        {
            //save the current document
            if (!File.Exists(fileName))
            {
                doc.Save(fileName);
            }
            //display with whitespace
            doc.PreserveWhitespace = true;
            doc.Load(fileName);
            txtOutput.Text = doc.OuterXml;
            //reload without whitespace
            doc.PreserveWhitespace = false;
            doc.Load(fileName);
            ds = new DataSet("res");
            ds.ReadXml(fileName);
            dg.DataSource = ds;
            //dg.SetDataBinding(ds, "res");
            dg.DataMember = ds.Tables["person"].ToString();
          // or >>  dg.DataMember = ds.Tables[0].ToString();
          //dg.SetDataBinding(ds, "res");
        }

        private void button1_Click(object sender, EventArgs e)
        {
            //duplicate the person node
            XmlNode contacts;
            XmlNode person;
            XmlNode root;
            root = doc.FirstChild;
            contacts = root.NextSibling;
            person = contacts.FirstChild;
            theNode = person.Clone();
            //copy node values from text boxes
            theNode["name"].InnerText = txtName.Text;
            theNode["address"].InnerText = txtAddress.Text;
            theNode["phone"].InnerText = txtPhone.Text;
            //add the new node to contacts
            contacts.AppendChild(theNode);
            doc.Save(fileName);
        }

        private void button4_Click(object sender, EventArgs e)
        {
            ds.AcceptChanges();
            ds.WriteXml(fileName);
            doc.Load(fileName);
        }
    }
}

---

i have also attached the source code for you that you can compile and change it. it is free because i wrote it for test and may contain bug and lack of functionality.
BTW it can show you most of the thing you asked for it.

Note: when you ran the program press this button: "Display XML" and after each operation please press it also to see the result in the DataGrid and TextBox area (that shows XML source)

Hope that it help you.

Toraj58,

Once again your information is completely off topic. The question is WHY does the ADO version introduce additional columns for enforcing the relationships between the tables. You Post (again) has NOTHING to do with this.

I am attempting to get Jef Patat to see the information so that he has an understanding as to WHY this is needed based upon the SCHEMA had is infered when reading XML into datasets.

he asked me this question:

Jef Patat: I see how you can display the inner text but how for example would you add or change a language?

and i answered him with what exactly he asked me and it is realted to his question and is not off-topic at all!

in this topic he mentioned another question and my ansewer is to it.

Quote:

---

Originally Posted by TheCPUWizard

Toraj58,

Once again your information is completely off topic. The question is WHY does the ADO version introduce additional columns for enforcing the relationships between the tables. You Post (again) has NOTHING to do with this.

I am attempting to get Jef Patat to see the information so that he has an understanding as to WHY this is needed based upon the SCHEMA had is infered when reading XML into datasets.

---

I have to agree with you. I'm afraid that Toraj58's help is not getting me any further. I don't know if your's will but it looks like it's in the right direction :rolleyes:
My understanding of ADO.NET is indeed too limited and that seems to be where I get stuck.

Anyway, if I understood you correctly this is what you were asking:

Code:

---

<?xml version="1.0" standalone="yes"?>
<xs:schema id="TestApp" targetNamespace="TestApp" xmlns:mstns="TestApp" xmlns="TestApp" xmlns:xs="http://www.w3.org/2001/XMLSchema" xmlns:msdata="urn:schemas-microsoft-com:xml-msdata" attributeFormDefault="qualified" elementFormDefault="qualified">
  <xs:element name="TestApp" msdata:IsDataSet="true" msdata:Locale="en-US">
    <xs:complexType>
      <xs:choice minOccurs="0" maxOccurs="unbounded">
        <xs:element name="Application">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Name" type="xs:string" minOccurs="0" />
              <xs:element name="Version" type="xs:string" minOccurs="0" />
            </xs:sequence>
          </xs:complexType>
        </xs:element>
        <xs:element name="User">
          <xs:complexType>
            <xs:sequence>
              <xs:element name="Name" type="xs:string" minOccurs="0" />
              <xs:element name="RequiredLanguages" minOccurs="0" maxOccurs="unbounded">
                <xs:complexType>
                  <xs:sequence>
                    <xs:element name="Language" nillable="true" minOccurs="0" maxOccurs="unbounded">
                      <xs:complexType>
                        <xs:simpleContent msdata:ColumnName="Language_Text" msdata:Ordinal="0">
                          <xs:extension base="xs:string">
                          </xs:extension>
                        </xs:simpleContent>
                      </xs:complexType>
                    </xs:element>
                  </xs:sequence>
                </xs:complexType>
              </xs:element>
            </xs:sequence>
          </xs:complexType>
        </xs:element>
      </xs:choice>
    </xs:complexType>
  </xs:element>
</xs:schema>

---

Thanks in advance,

Jef

yes, TheCPUWizard help in the right direction; but taking into consideration that you are new to XML i tried to help you with a sample that i have written already and let TheCPUWizard also give you the right direction.
if it did not help you; you can ignore it at this moment.

OK...Give me a few minutes to whip something up [I am on a telephone conference right now so it might be a little bit]

That schema should give me everything I need.

Ok, the attached program shows how things work with ADO when dealing with XML.

The "original" series, it what you posted. The relationships are inherent based on nesting within the XML. This does NOT map to relations by a column name as expected in ADO.Net, so the act of reading created the additional _Id columns for building the relationshipd.

The "optimized" is created by manually building up a dataset with two tables (User and Languages) and populating them. We then write both the schema and the XML.

You can not see the difference. In the optinmized the two are NOT listed in a hierarchy, but are separed structures, wtih the contraint explicit in the XML.

Look it over..I have to step out for a few hours...

Based on your example I played around a bit more. I disliked the fact that the elements got spread all over the xml (It is my intention to later on edit it manually) I did so research and found that I can disable this by using the "Nested" property of the datarelation as shown in this code:

Code:

---

                        DataSet ds3 = new DataSet();
                        DataTable dt1 = new DataTable("User");
                        DataColumn parentName = new DataColumn("Name");
                        dt1.Columns.Add(parentName);
                        ds3.Tables.Add(dt1);
                        DataTable dt2 = new DataTable("Languages");
                        DataColumn childName = new DataColumn("Name");
                        dt2.Columns.Add(childName);
                        dt2.Columns.Add(new DataColumn("Language"));
                        ds3.Tables.Add(dt2);
                        ds3.Relations.Add("relation", parentName, childName);
                        ds3.Relations["relation"].Nested = true;
                        ds3.WriteXmlSchema("OptimizedSchema.xsd");

                        DataSet ds4 = new DataSet();
                        using (StreamReader s1 = new StreamReader("OptimizedSchema.xsd"))
                        {
                                ds4.ReadXmlSchema(s1);
                        }

                        DataRow dr;
                        dr = ds4.Tables["User"].NewRow(); dr["Name"] = "Tom"; ds4.Tables["User"].Rows.Add(dr);
                        dr = ds4.Tables["User"].NewRow(); dr["Name"] = "****"; ds4.Tables["User"].Rows.Add(dr);
                        dr = ds4.Tables["User"].NewRow(); dr["Name"] = "Harry"; ds4.Tables["User"].Rows.Add(dr);

                        dr = ds4.Tables["Languages"].NewRow(); dr["Name"] = "Tom"; dr["Language"] = "Spanish"; ds4.Tables["Languages"].Rows.Add(dr);
                        dr = ds4.Tables["Languages"].NewRow(); dr["Name"] = "Tom"; dr["Language"] = "Chinese"; ds4.Tables["Languages"].Rows.Add(dr);
                        dr = ds4.Tables["Languages"].NewRow(); dr["Name"] = "****"; dr["Language"] = "English"; ds4.Tables["Languages"].Rows.Add(dr);

                        ds4.WriteXml("Optimized.xml");

---

This gives me xml like this:

Code:

---

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <User>
    <Name>Tom</Name>
    <Languages>
      <Name>Tom</Name>
      <Language>Spanish</Language>
    </Languages>
    <Languages>
      <Name>Tom</Name>
      <Language>Chinese</Language>
    </Languages>
  </User>
  <User>
    <Name>****</Name>
    <Languages>
      <Name>****</Name>
      <Language>English</Language>
    </Languages>
  </User>
  <User>
    <Name>Harry</Name>
  </User>
</NewDataSet>

---

This looks ok and at least I now understand what is happening (at least I think).

But why does the user Tom have two child elements 'Languages'. Why is it not like this:

Code:

---

<?xml version="1.0" standalone="yes"?>
<NewDataSet>
  <User>
    <Name>Tom</Name>
    <Languages>
      <Language>Spanish</Language>
      <Language>Chinese</Language>
    </Languages>
  </User>
  <User>
    <Name>****</Name>
    <Languages>
      <Language>English</Language>
    </Languages>
  </User>
  <User>
    <Name>Harry</Name>
  </User>
</NewDataSet>

---

thank you,

Jef

Quote:

---

Originally Posted by Jef Patat

This looks ok and at least I now understand what is happening (at least I think).

But why does the user Tom have two child elements 'Languages'. Why is it not like this:

---

Because the DataRelation idiom is between ROWS. So it places an entire row definition as the child relement(s) [(n this case two of them], and each child ROW has TWO Elements.