Link to home
Create AccountLog in
Avatar of LarsNess
LarsNessFlag for Norway

asked on

WriteXML to ignore relation columns?

Is there any way to get DataSet.WriteXML to ignore the columns used in a relation bewteen two DataTables in the dataset when writing the XML?

Ex. I have dataset DS and datatables DT1 and DT2. In DT1 I have columns A, B and C. In DT2 I have columns A, B and D. I fill DT1 with a select A,B,C from <somewhere> and DT2 with select A,B,D from <somewhere else>. Add a relation between DT1 and DT2 on columns A and B and set the "Nested" property to true.

Now the WriteXML method will give something like this:
<DT1>
  <A />
  <B />
  <C />
  <DT2>
    <A />
    <B />
    <D />
  </DT2>
</DT1>
..
..

I want the WriteXML to ignore the relation key columns on level 2, like this:
<DT1>
  <A />
  <B />
  <C />
  <DT2>
    <D />
  </DT2>
</DT1>
..
..

Is this possible "automatically" in some way - or do I have to parse myself if I want this?
Avatar of Bob Learned
Bob Learned
Flag of United States of America image

You don't have control over the WriteXml process that I know of.

Bob
In your DataTables, instead of having duplicate columns in both of your tables (columns A and B), both
storing the same data separately, use the Expression property of the corresponding columns in the child
table to reference the parent table column.  Then, before you write out the XML, remove the columns
from DT2; and when you read the XML, add the columns back in, setting the Expressoin property to
the corresponding column in the parent table.

Here's an example:

        // reads XML file, adds column referencing parent columns
        private void LoadXML()
        {
            DataSet ds = new DataSet();
            ds.ReadXml("c:\\temp\\data.xml");

            // add columns to DT2 and set Expression property of corresponding columns
            ds.Tables["DT2"].Columns.Add("A");
            ds.Tables["DT2"].Columns.Add("B");

            // set index to position columns
            ds.Tables["DT2"].Columns["A"].SetOrdinal(1);
            ds.Tables["DT2"].Columns["B"].SetOrdinal(2);

            // reference column in parent table
            ds.Tables["DT2"].Columns["A"].Expression = "Parent.A";
            ds.Tables["DT2"].Columns["B"].Expression = "Parent.B";

            ds.WriteXml("c:\\temp\\data2.xml");
        }


        // creates the DataSet, adds 2 tables, loads DataSet
        //  creates XML file
        private void CreateXMLFile()
        {
            DataSet ds = new DataSet();
            DataTable DT1 = new DataTable("DT1");
            DataTable DT2 = new DataTable("DT2");

            ds.Tables.Add(DT1);
            ds.Tables.Add(DT2);

            // add Primary ID column to DT1 - used as FK in DT2
            DT1.Columns.Add("ID");
            DT1.Columns["ID"].DataType = typeof(int);

            DT1.Columns.Add("A");
            DT1.Columns.Add("B");
            DT1.Columns.Add("C");

            // add FK from DT1
            DT2.Columns.Add("DT1_ID");
            DT2.Columns["DT1_ID"].DataType = typeof(int);

            DT2.Columns.Add("D");

            // create a relation between DT1 and DT2
            ds.Relations.Add("DT1_DT2", DT1.Columns["ID"], DT2.Columns["DT1_ID"]);

            // add rows
            for (int i = 0; i < 5; i++)
            {
                DT1.Rows.Add(i,
                        "parent A item " + i.ToString(),
                            "parent B item " + i.ToString(),
                            "parent C item " + i.ToString());

                DT2.Rows.Add(i, "child item " + i.ToString());
            }

            // write the data
            ds.WriteXml("c:\\temp\\data.xml", XmlWriteMode.WriteSchema );
        }


VBRocks
MS MVP


ASKER CERTIFIED SOLUTION
Avatar of LarsNess
LarsNess
Flag of Norway image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account