C# ADO.NET & XML

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,
I have aproblem with nesting of tables in an schema generated dataset,

What I am trying to do is nest delivery item data in a delivery table, then
out put the data to a xml file. When I do this, what should be nested xml
elements I end up getting the opposite.

An example of the code I'm using is:
DataRow newSale = this.dataset.Tables["sale"].NewRow ();

DataRow newItem = this.dataset.Relations["sale_to_item"].ChildTable.NewRow ();
..
..Add data to the appropiate columns
..
this.dataset.Tables["sale"].Rows.Add ( newSale );

this.datset.Relations["sale_to_item"].ChildTable.Rows.Add ( newItem );

this.dataset.AcceptChanges ();

Below is a sample of the XML Schemas I'm using and the code

<?xml version="1.0" ?>
<xs:schema id="PDA_PROFSALE"
targetNamespace="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns:mstns="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="PDA_PROFSALE" msdata:IsDataSet="true"
msdata:Locale="en-AU" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="sale" msdata:CaseSensitive="false" minOccurs="1"
maxOccurs="1000000" form="qualified">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" minOccurs="0"
msdata:AutoIncrement="true" msdata:AutoIncrementSeed="0"
msdata:AutoIncrementStep="1" msdata:ReadOnly="true" />
<xs:element name="co_last_name" type="xs:string" minOccurs="0" />
<xs:element name="first_name" type="xs:string" minOccurs="0" />
<xs:element name="details_date" type="xs:string" minOccurs="0" />
<xs:element name="non_GST_amount" type="xs:string" minOccurs="0" />
<xs:element name="salesperson" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
<xs:element name="salesperson_last_name" type="xs:string"
minOccurs="0" />
<xs:element name="salesperson_first_name" type="xs:string"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="item" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
<xs:element name="item_quantity" type="xs:string" minOccurs="0" />
<xs:element name="item_size" type="xs:string" minOccurs="0" />
<xs:element name="item_type" type="xs:string" minOccurs="0" />
<xs:element name="item_charge" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="other">
<xs:complexType>
<xs:sequence>
<xs:element name="last_name" type="xs:string" minOccurs="0" />
<xs:element name="given_name" type="xs:string" minOccurs="0" />
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:key name="PDA_PROFSALEKey1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:sale" />
<xs:field xpath="mstns:ID" />
</xs:key>
<xs:keyref name="sale_to_salesperson" refer="mstns:PDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:salesperson" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:keyref name="sale_to_item" refer="mstns:PDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:item" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:keyref name="sale_to_other" refer="mstns:PDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:other" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:key name="PDA_PROFSALEKey2">
<xs:selector xpath=".//mstns:salesperson" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
<xs:key name="PDA_PROFSALEKey3">
<xs:selector xpath=".//mstns:item" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
<xs:key name="PDA_PROFSALEKey4">
<xs:selector xpath=".//mstns:other" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
</xs:element>
</xs:schema>
 
First of all, this schema has no "delivery" table (please see below). You do
have a lot of relations, though, some of them duplicated.

For example, "item" is connected to "sale" by two relations:



Nested: 'sale_item': item('ID' ) <- sale('ID' )
Related: 'sale_to_item': item('sale_ID' ) <- sale('ID' )



Seems kind of pointless to me.



Note 'nested' and 'related' are exactly the same from functionality point of
view.

The difference is in XML serialization only. Nested relations would result
in larger XML and slower parsing.



For example, table 'Parent' has a primary key 'ID' and table 'Child' had
foreign key "ParentID', columns are mapped to attributes to save space and
improve performance.

Nested xml:



<DataSet>

<Parent ID="10>

<Child ParentID="10 />

</Parent>

</DataSet>



Related XML:



<DataSet>

<Parent ID="10 />

<Child ParentID="10 />

</DataSet>



--------------------------- DataSet ----------------------
DataSet: 'PDA_PROFSALE'
----------------------- Tables -----------------------
Table@0: 'sale'
------------------- Columns ----------------------
Element column@0: 'ID' of 'Int32' Unique Autoincrement
Element column@1: 'co_last_name' of 'String' Nullable
Element column@2: 'first_name' of 'String' Nullable
Element column@3: 'details_date' of 'String' Nullable
Element column@4: 'non_GST_amount' of 'String' Nullable
----------------- Child Relations ----------------
Nested: 'sale_salesperson': salesperson('ID' ) -> sale('ID' )
Nested: 'sale_item': item('ID' ) -> sale('ID' )
Nested: 'sale_other': other('ID' ) -> sale('ID' )
Related: 'sale_to_salesperson': salesperson('sale_ID' ) ->
sale('ID' )
Related: 'sale_to_item': item('sale_ID' ) -> sale('ID' )
Related: 'sale_to_other': other('sale_ID' ) -> sale('ID' )
----------------- Parent Relations ---------------
--------------------- Rows -----------------------
0 rows in this table
--------------------------------------------------
Table@1: 'salesperson'
------------------- Columns ----------------------
Element column@0: 'sale_ID' of 'Int32' Unique
Element column@1: 'salesperson_last_name' of 'String'
Nullable
Element column@2: 'salesperson_first_name' of 'String'
Nullable
Hidden column@3: 'ID' of 'Int32' Nullable
----------------- Child Relations ----------------
----------------- Parent Relations ---------------
Nested: 'sale_salesperson': salesperson('ID' ) <- sale('ID' )
Related: 'sale_to_salesperson': salesperson('sale_ID' ) <-
sale('ID' )
--------------------- Rows -----------------------
0 rows in this table
--------------------------------------------------
Table@2: 'item'
------------------- Columns ----------------------
Element column@0: 'sale_ID' of 'Int32' Unique
Element column@1: 'item_quantity' of 'String' Nullable
Element column@2: 'item_size' of 'String' Nullable
Element column@3: 'item_type' of 'String' Nullable
Element column@4: 'item_charge' of 'String' Nullable
Hidden column@5: 'ID' of 'Int32' Nullable
----------------- Child Relations ----------------
----------------- Parent Relations ---------------
Nested: 'sale_item': item('ID' ) <- sale('ID' )
Related: 'sale_to_item': item('sale_ID' ) <- sale('ID' )
--------------------- Rows -----------------------
0 rows in this table
--------------------------------------------------
Table@3: 'other'
------------------- Columns ----------------------
Element column@0: 'last_name' of 'String' Nullable
Element column@1: 'given_name' of 'String' Nullable
Element column@2: 'sale_ID' of 'Int32' Unique
Hidden column@3: 'ID' of 'Int32' Nullable
----------------- Child Relations ----------------
----------------- Parent Relations ---------------
Nested: 'sale_other': other('ID' ) <- sale('ID' )
Related: 'sale_to_other': other('sale_ID' ) <- sale('ID' )
--------------------- Rows -----------------------
0 rows in this table
--------------------------------------------------


Best regards,

Ilya

This posting is provided "AS IS" with no warranties, and confers no rights.

*** Want to find answers instantly? Here's how... ***

1. Go to
http://groups-beta.google.com/group/microsoft.public.dotnet.framework.compactframework?hl=en
2. Type your question in the text box near "Search this group" button.
3. Hit "Search this group" button.
4. Read answer(s).

Aus Bean said:
Hi,
I have aproblem with nesting of tables in an schema generated dataset,

What I am trying to do is nest delivery item data in a delivery table,
then
out put the data to a xml file. When I do this, what should be nested xml
elements I end up getting the opposite.

An example of the code I'm using is:
DataRow newSale = this.dataset.Tables["sale"].NewRow ();

DataRow newItem = this.dataset.Relations["sale_to_item"].ChildTable.NewRow
();
.
.Add data to the appropiate columns
.
this.dataset.Tables["sale"].Rows.Add ( newSale );

this.datset.Relations["sale_to_item"].ChildTable.Rows.Add ( newItem );

this.dataset.AcceptChanges ();

Below is a sample of the XML Schemas I'm using and the code

<?xml version="1.0" ?>
<xs:schema id="PDA_PROFSALE"
targetNamespace="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns:mstns="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns="http://tempuri.org/PDA_PROFSALE.xsd"
xmlns:xs="http://www.w3.org/2001/XMLSchema"
xmlns:msdata="urn:schemas-microsoft-com:xml-msdata"
attributeFormDefault="qualified" elementFormDefault="qualified">
<xs:element name="PDA_PROFSALE" msdata:IsDataSet="true"
msdata:Locale="en-AU" msdata:EnforceConstraints="False">
<xs:complexType>
<xs:choice maxOccurs="unbounded">
<xs:element name="sale" msdata:CaseSensitive="false" minOccurs="1"
maxOccurs="1000000" form="qualified">
<xs:complexType>
<xs:sequence>
<xs:element name="ID" type="xs:int" minOccurs="0"
msdata:AutoIncrement="true" msdata:AutoIncrementSeed="0"
msdata:AutoIncrementStep="1" msdata:ReadOnly="true" />
<xs:element name="co_last_name" type="xs:string" minOccurs="0" />
<xs:element name="first_name" type="xs:string" minOccurs="0" />
<xs:element name="details_date" type="xs:string" minOccurs="0" />
<xs:element name="non_GST_amount" type="xs:string" minOccurs="0" />
<xs:element name="salesperson" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
<xs:element name="salesperson_last_name" type="xs:string"
minOccurs="0" />
<xs:element name="salesperson_first_name" type="xs:string"
minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="item" minOccurs="0" maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
<xs:element name="item_quantity" type="xs:string" minOccurs="0" />
<xs:element name="item_size" type="xs:string" minOccurs="0" />
<xs:element name="item_type" type="xs:string" minOccurs="0" />
<xs:element name="item_charge" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="other">
<xs:complexType>
<xs:sequence>
<xs:element name="last_name" type="xs:string" minOccurs="0" />
<xs:element name="given_name" type="xs:string" minOccurs="0" />
<xs:element name="sale_ID" type="xs:int" minOccurs="0" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:choice>
</xs:complexType>
<xs:key name="PDA_PROFSALEKey1" msdata:PrimaryKey="true">
<xs:selector xpath=".//mstns:sale" />
<xs:field xpath="mstns:ID" />
</xs:key>
<xs:keyref name="sale_to_salesperson" refer="mstns:PDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:salesperson" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:keyref name="sale_to_item" refer="mstns:PDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:item" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:keyref name="sale_to_other" refer="mstns:PDA_PROFSALEKey1">
<xs:selector xpath=".//mstns:other" />
<xs:field xpath="mstns:sale_ID" />
</xs:keyref>
<xs:key name="PDA_PROFSALEKey2">
<xs:selector xpath=".//mstns:salesperson" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
<xs:key name="PDA_PROFSALEKey3">
<xs:selector xpath=".//mstns:item" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
<xs:key name="PDA_PROFSALEKey4">
<xs:selector xpath=".//mstns:other" />
<xs:field xpath="mstns:sale_ID" />
</xs:key>
</xs:element>
</xs:schema>
Click to expand...
 
Back
Top