CODEWITHSUNDEEP
c#xmldatatable
Alex
Alex

Reputation: 5724

Defining nested relationships in datatables wiith dataset.readxml

I want to read my xml into a dataset using dataset.readXML(filename)

The idea is to get the 2 tables loaded, and the use LINQ to join them and dump the query result into a sql database.

I have the following xml

<Report>
    <Parameters>
        <Code>ABC</Code>
        <Expiries>
            <date>2000-01-01</date>
            <time>08:00:00</time>
            <reason>The guy liked cake</reason>
        </Expiries>
        <Expiries>
            <date>2002-01-01</date>
            <time>08:00:00</time>
            <reason>The guy still liked cake</reason>
        </Expiries>
    </Parameters>
    <Parameters>
        <Code>BCA</Code>
        <Expiries>
            <date>2000-01-01</date>
            <time>08:00:00</time>
            <reason>The guy liked cake</reason>
        </Expiries>
        <Expiries>
            <date>2002-01-01</date>
            <time>08:00:00</time>
            <reason>The guy still liked cake</reason>
        </Expiries>
    </Parameters>
</Report>

I define the datatable as so: (addcolumn is a convenience method to add columns)

public DataTable Parameters = new DataTable("Parameters")
    .addColumn<string>("Code")

 public DataTable Expiries = new DataTable("Parameters")
    .addColumn<string>("date")
    .addColumn<string>("time")
    .addColumn<string>("reason")

and I import it into the tables using the following:

DataSet tds = new DataSet()
tds.Tables.add(Parameters);
tds.Tables.add(Expiries);

tds.ReadXML(file.FullName)
//now a record on tds.tables["Parameters"] will have Code="ABC"

This all works brilliantly, but it throws away the relationship between my expiries and my parameters. If I do it without using the defined columns, it will create a relationship between the 2 tables automatically eg

DataSet tds = new DataSet()

tds.ReadXML(file.FullName)
//now a record on tds.tables["Parameters"] will have Code="ABC", Parameter_Id=0

How do I define this relationship in the datatable definition, so that it mimics what the auto generated schema has?

Upvotes: 4

Views: 2508

Answers (1)

brucwhi
brucwhi

Reputation: 161

Assuming Code is unique, you could the following:

        DataSet custom = new DataSet();
        DataTable Parameters = new DataTable("Parameters")
            .AddColumn<string>("Code");

        DataTable Expiries = new DataTable("Expiries")
            .AddColumn<string>("Code")
            .AddColumn<string>("date")
            .AddColumn<string>("time")
            .AddColumn<string>("reason");
        custom.Tables.Add(Parameters);
        custom.Tables.Add(Expiries);
        custom.Relations.Add(new DataRelation("ParameterExpiries", Parameters.Columns["Code"], Expiries.Columns["Code"]));
        custom.Relations["ParameterExpiries"].Nested = true;

This will give you an expiries table which has Code defined from the parent

If Code is not unique, then you can create another column in Parameters which is AutoIncrement=true (e.g "ID") and use it in the parent child relationship. Then add another column in Expiries "Code" which uses the expression "Parent.Code". 

        DataSet custom = new DataSet();
        DataTable Parameters = new DataTable("Parameters")
            .AddColumn<string>("Code")
            .AddColumn<int>("ID");
        Parameters.Columns["ID"].AutoIncrement = true;

        DataTable Expiries = new DataTable("Expiries")
            .AddColumn<int>("ID")
            .AddColumn<string>("Code")
            .AddColumn<string>("date")
            .AddColumn<string>("time")
            .AddColumn<string>("reason");
        custom.Tables.Add(Parameters);
        custom.Tables.Add(Expiries);
        custom.Relations.Add(new DataRelation("ParameterExpiries", Parameters.Columns["ID"], Expiries.Columns["ID"]));
        custom.Relations["ParameterExpiries"].Nested = true;
        Expiries.Columns["Code"].Expression = "Parent.Code";

For expressions see: http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression(v=vs.110).aspx

For datarelation see: http://msdn.microsoft.com/en-us/library/system.data.datarelation(v=vs.110).aspx

Upvotes: 2

Related Questions