Mapping a flat data table to a typed DataSet with complex schema

[Stephen Walch]

I am a schema "newbie" but I think I pretty much understand how schemas and
typed DataSets are supposed to work. Now I have a case where I am loading a
flat (denormalized) table into a dataset with a not-so-flat schema.

For example, lets say I was using the Employees table from the Northwind
database but I wanted to load a typed dataset in which the LastName and
FirstName fields were organized into a complex type called "EmpName". I
tried simply adding a complex type to my XSD file (creating using VS.NET's
Generate DataSet function):

<xs:complexType name="EmpName">
<xs:sequence>
<xs:element name="LastName" type="xs:string" minOccurs="0" />
<xs:element name="FirstName" type="xs:string" minOccurs="0" />
</xs:sequence>
</xs:complexType>

and I replaced the FirstName and LastName elements with a single Name
element

<xs:element name="Name" type="mstns:EmpName" />

A side effect of doing this is that my generated typed dataset how has an
additional table called "Name":

private void InitClass() {
....
this.tableName = new NameDataTable();
this.Tables.Add(this.tableName);
ForeignKeyConstraint fkc;
fkc = new ForeignKeyConstraint("Employees_Name", new DataColumn[] {
this.tableEmployees.EmployeeIDColumn}, new DataColumn[] {
this.tableName.EmployeeIDColumn});
this.tableName.Constraints.Add(fkc);
....
}

If this is the right way to model a nested ComplexType, fine. But when I
try to fill the dataset from my "flat" table, the Name table in the dataset
is not filled. I tried to find a tweak the TableMappings for my data
adapter, but no luck. It looks like that data adapter will only fill the
Name table if there is a Name table in the source database.

Can you suggest a way to accomplish what I want?

Thanks,

Steve

 

[Stephen Walch]

Note: I am having similar issues maping a column of type string array to a
typed dataset. I tried specifying maxoccurs="unbounded" in the XSD file
and, once again, the generated dataset ended up with an extra table but my
data adapter does not fill the table.

Thanks again.

Steve

 

[Kevin Yu [MSFT]]

Hi Stephen,

Based on my understanding, you want to create a Name column which is the
combination of FirstName and LastName. I think you can achieve this with
the Expression attribute. Please try to add this line in the XSD file after
LastName and FirstName.

<xs:element name="Name" type="xs:string"
msdata:Expression="FirstName+' '+LastName" />

This will add a column definition to the table and the column's value is
set to FirstName + LastName. When you fill the dataset, the column value
will be generated automatically. If you need to hide the FirstName and
LastName columns, you can try to use the following code after filling.

ds.Employees.FirstNameColumn.ColumnMapping = MappingType.Hidden;
ds.Employees.LastNameColumn.ColumnMapping = MappingType.Hidden;

If anything is unclear, please feel free to reply to the post.

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

 

[Stephen Walch]

This is very clear and may help in some cases, but I think I picked a bad
example. What I really want is

(1) a general way to map elements from my "denormalized" table to complex
types in the resulting schema, as in the example I gave:

<xs:element name="Name" type="mstns:EmpName" />

where EmpName is a complex type.

(2) a general way to map columns of type array (for example, string[]) to
multiple-occurance elements in the resulting schema:

<xs:element name="Category" type="xs:string" maxOccurs="unbounded" />

Thanks again.

-Steve

 

[Kevin Yu [MSFT]]

Hi Stephen,

I don't think there is a normalized way to achieve this. However, what you
assumed is right. The data adapter will only fill the Name table if there
is a Name table in the source database. So we have to add EmpName field
into the resultset. You can try to query like the following:

SELECT *, firstname+ lastname as EmpName from employees

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

 

[Stephen Walch]

How would you suggest working around the limitations in the other case I
cited: map columns of type string array (for example, string[]) to
multiple-occurance elements in the resulting schema:

<xs:element name="Category" type="xs:string" maxOccurs="unbounded" />

Currently, the dataset generated from this schema ends up with an extra
table (and ForeignKeyConstraint) for Category, but my data adapter does not
fill the table.

 

[Kevin Yu [MSFT]]

Hi Stephen,

I'm not quite sure about your question. Are you going to join the parent
table and child table into one? If so, you can design a schema with the
structure of the result table. When filling that table, you can use the
following SQL statement:

SELECT * FROM ChildTable LEFT JOIN ParentTable on ChildTable.Field =
ParentTable.Field

For more information about join tables, please refer to SQL Book Online.

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

 

[Stephen Walch]

No, I have a single table with one column that returns an array of strings
for each row.

 

[Kevin Yu [MSFT]]

Hi Stephen,

The data type for a DataColumn object cannot be string array. For a
complete list of supported types of a DataColumn, please refer to the
following link:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdatadatacolumnclassdatatypetopic.asp

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

 

[Stephen Walch]

I understand, but one of the columns in my data source certainly can be (and
is) a string array. So my question (again) is how to map this into a legal
schema/DataSet (with legal DataColumns, etc.). To recap what I have posted
thus far, when I use

<xs:element name="Category" type="xs:string" maxOccurs="unbounded" />

in my schema and generate a typed DataSet, I end up with a second table and
a ForeignKey constraint (which makes sense) but I can see no way to get the
data adapter to fill the second table.

 

[MSFT]

Hi Stephen

To fill a dataset will parent and children table, we need at least two
different dataadapter. DataAdapter is realted to the data source. I am
curious what your data source, a database? How can it use a string arry as
column?

Luke
Microsoft Online Support

Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)

 

[Stephen Walch]

The data source is Proposion N2N, which is an ADO.NET data provider for
accessing Lotus Notes databases. http://www.proposion.com/n2n. Since Notes
can have multi-valued string, date and number items, it is possible that the
value of reader.GetValue(0) will be an array.

I did discover that if I use the following in my schema:

<xs:element name="Category" type="xs:string"
msataType="System.String[]" />

then the typed dataSet that is generated includes a column of type String[]
and my dataAdapter does sucessfully fill the dataSet.

I guess this is as good as I am going to be able to do. Thanks.