Datatables and relations
CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Page 1 of 2 12 LastLast
Results 1 to 15 of 16

Thread: Datatables and relations

  1. #1
    Join Date
    Nov 2005
    Posts
    159

    Question 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

  2. #2
    Join Date
    Mar 2002
    Location
    NY, USA
    Posts
    12,097

    Re: Datatables and relations

    You will be much better of if you work with an explicit (valiatable) schema...
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009
    In theory, there is no difference between theory and paractice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  3. #3
    Join Date
    Mar 2008
    Location
    IRAN
    Posts
    811

    Re: Datatables and relations

    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: toraj_e@yahoo.com");
            }
        }
    }
    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).
    Attached Files Attached Files
    Last edited by toraj58; December 17th, 2008 at 11:46 AM.
    Please rate my post if it was helpful for you.
    C#, C++, PHP, ASP.NET
    SQL Server, MySQL
    DirectX
    MATH
    Touraj Ebrahimi
    [toraj_e] [at] [yahoo] [dot] [com]

  4. #4
    Join Date
    Nov 2005
    Posts
    159

    Re: Datatables and relations

    Quote Originally Posted by TheCPUWizard View Post
    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

  5. #5
    Join Date
    Mar 2002
    Location
    NY, USA
    Posts
    12,097

    Re: Datatables and relations

    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).
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009
    In theory, there is no difference between theory and paractice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  6. #6
    Join Date
    Nov 2005
    Posts
    159

    Re: Datatables and relations

    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

  7. #7
    Join Date
    Mar 2002
    Location
    NY, USA
    Posts
    12,097

    Re: Datatables and relations

    Quote Originally Posted by Jef Patat View Post
    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!!!!!
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009
    In theory, there is no difference between theory and paractice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  8. #8
    Join Date
    Mar 2008
    Location
    IRAN
    Posts
    811

    Re: Datatables and relations

    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.
    Attached Files Attached Files
    Last edited by toraj58; December 18th, 2008 at 10:48 AM.
    Please rate my post if it was helpful for you.
    C#, C++, PHP, ASP.NET
    SQL Server, MySQL
    DirectX
    MATH
    Touraj Ebrahimi
    [toraj_e] [at] [yahoo] [dot] [com]

  9. #9
    Join Date
    Mar 2002
    Location
    NY, USA
    Posts
    12,097

    Re: Datatables and relations

    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.
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009
    In theory, there is no difference between theory and paractice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  10. #10
    Join Date
    Mar 2008
    Location
    IRAN
    Posts
    811

    Re: Datatables and relations

    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.
    Please rate my post if it was helpful for you.
    C#, C++, PHP, ASP.NET
    SQL Server, MySQL
    DirectX
    MATH
    Touraj Ebrahimi
    [toraj_e] [at] [yahoo] [dot] [com]

  11. #11
    Join Date
    Nov 2005
    Posts
    159

    Re: Datatables and relations

    Quote Originally Posted by TheCPUWizard View Post
    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
    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

  12. #12
    Join Date
    Mar 2008
    Location
    IRAN
    Posts
    811

    Re: Datatables and relations

    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.
    Please rate my post if it was helpful for you.
    C#, C++, PHP, ASP.NET
    SQL Server, MySQL
    DirectX
    MATH
    Touraj Ebrahimi
    [toraj_e] [at] [yahoo] [dot] [com]

  13. #13
    Join Date
    Mar 2002
    Location
    NY, USA
    Posts
    12,097

    Re: Datatables and relations

    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.
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009
    In theory, there is no difference between theory and paractice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  14. #14
    Join Date
    Mar 2002
    Location
    NY, USA
    Posts
    12,097

    Re: Datatables and relations

    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...
    Attached Files Attached Files
    TheCPUWizard is a registered trademark, all rights reserved. (If this post was helpful, please RATE it!)
    2008, 2009
    In theory, there is no difference between theory and paractice; in practice there is.

    * Join the fight, refuse to respond to posts that contain code outside of [code] ... [/code] tags. See here for instructions
    * How NOT to post a question here
    * Of course you read this carefully before you posted
    * Need homework help? Read this first

  15. #15
    Join Date
    Nov 2005
    Posts
    159

    Re: Datatables and relations

    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

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Windows Mobile Development Center


Click Here to Expand Forum to Full Width

This is a CodeGuru survey question.


Featured


HTML5 Development Center