Typed DataSets and Relations

[Ken Allen]

I am having some difficulties with determining how to leverage the use of
typed datasets with a specific schema. The problem lies with the definition
of a 'relationship', at least when defining the typed dataset via the IDE.

The IDE forces me to define a relationship from a parent to a child,
requiring the parent to be the one with the Primary Key and the child to
contain a column of the same type (not necessarily part of its primary key).
This works fine in many cases, such as Customer --> Orders, where the
Customer record is the parent and the Orders record contains a (possibly
non-primary key) column that contains the CustomerID.

In other cases this is not the actual case: consider the somewhat artificial
situation where we shall classify disk drives into 'levels' based on a
capacity range. So the [Levels] table will contain a primary key of LevelID
and the remainder of the fields will describe each specific level. The
[Drives] table will contain a primary key of DriveID and will also contain a
non-key field named LevelID; in the database there shall be a foreign key
defined between these tables based on the LevelID column. In the typed
dataset, I am forced to define the relationship with [Levels] as the parent,
but in fact the levels are merely a lookup mechanism to permit normalization
of the data. When I display the [Drives] entries, I want to show some
columns from the [Levels] table, either in addition to or in place of the
LevelID column value itself, but I do not wish to treat the [Drives] as
children of the [Levels], since [Levels] are not control objects, merely
lookup information.

I am likely missing something here, but since all of the source material I
have found, in books and online, refers to the Customer/Order paradigm and
not to the Drives/Levels scenario, I am uncertain how I can leverage the use
of typed datasets to avoid the use of a JOIN to retrieve the information
that I want.

Also, I can find no way to define calculated columns as part of a typed
dataset, at least from within the IDE. Is this possible? In the classic
Customer/Orders paadigm, I would want to define a calculated column in the
typed data table [Customers] to contain the number of [Orders] that are
'open' as well as the total value of those orders, but I can find no way to
define such a calculated column unless I place it in the database, which I
do not want. I have come across numerous examples of doing this with
programmatically created untyped datasets, but I can find no reference on
how to do this with a typed dataset, and expecially from within the IDE.

Finally, I am concerned with the amount of work that one must do to update a
typed dataset to reflect changes made to the actual schema. From an earlier
post it was pointed out that business rules should be added to a further
sub-class of the typed dataset, such that most changes to the typed dataset
would not invalidate the derived class (but would require that it be
recompiled), except where columns or relations were renamed or removed;
obviously new code has to be added to deal with new columns and
relationships. But is there no alternative aside from forcing one to
re-generate the XSD from scratch again, with all the manual steps to define
the relationships and calculated columns (if this can be done)?

-ken

 

[Ken Allen]

Argh!

I can define the Parent/Child relationship the way I want programmatically,
but not in a typed dataset from the IDE!

While I can define the relationship, I cannot get access to the members of
the child! In my case there is a 1:1 relationship between the parent
[Drives] and child [Levels] tables, but the only way to refer to the
contents of the child row(s) directly is to use numeric aggregate functions!
The parent can be referred to directly, however!

So this basically means that I must define my relationship "in reverse" to
what I intend, and then traverse only the children and permit a reference to
the parent...

I can do this when I create the dataset programmtically, but I still cannot
find a way to define a calculated column from the typed dataset designer in
the IDE.

Another point -- if I have a typed dataset with two (2) or more tables, can
I fully populate the dataset with a single Fill() call? Theonly way I have
been able to achieve this is to use a separate SqlDataAdapter.Fill() call
for each specific table.

-ken

I am having some difficulties with determining how to leverage the use of
typed datasets with a specific schema. The problem lies with the definition
of a 'relationship', at least when defining the typed dataset via the IDE.

The IDE forces me to define a relationship from a parent to a child,
requiring the parent to be the one with the Primary Key and the child to
contain a column of the same type (not necessarily part of its primary key).
This works fine in many cases, such as Customer --> Orders, where the
Customer record is the parent and the Orders record contains a (possibly
non-primary key) column that contains the CustomerID.

In other cases this is not the actual case: consider the somewhat artificial
situation where we shall classify disk drives into 'levels' based on a
capacity range. So the [Levels] table will contain a primary key of LevelID
and the remainder of the fields will describe each specific level. The
[Drives] table will contain a primary key of DriveID and will also contain a
non-key field named LevelID; in the database there shall be a foreign key
defined between these tables based on the LevelID column. In the typed
dataset, I am forced to define the relationship with [Levels] as the parent,
but in fact the levels are merely a lookup mechanism to permit normalization
of the data. When I display the [Drives] entries, I want to show some
columns from the [Levels] table, either in addition to or in place of the
LevelID column value itself, but I do not wish to treat the [Drives] as
children of the [Levels], since [Levels] are not control objects, merely
lookup information.

I am likely missing something here, but since all of the source material I
have found, in books and online, refers to the Customer/Order paradigm and
not to the Drives/Levels scenario, I am uncertain how I can leverage the use
of typed datasets to avoid the use of a JOIN to retrieve the information
that I want.

Also, I can find no way to define calculated columns as part of a typed
dataset, at least from within the IDE. Is this possible? In the classic
Customer/Orders paadigm, I would want to define a calculated column in the
typed data table [Customers] to contain the number of [Orders] that are
'open' as well as the total value of those orders, but I can find no way to
define such a calculated column unless I place it in the database, which I
do not want. I have come across numerous examples of doing this with
programmatically created untyped datasets, but I can find no reference on
how to do this with a typed dataset, and expecially from within the IDE.

Finally, I am concerned with the amount of work that one must do to update a
typed dataset to reflect changes made to the actual schema. From an earlier
post it was pointed out that business rules should be added to a further
sub-class of the typed dataset, such that most changes to the typed dataset
would not invalidate the derived class (but would require that it be
recompiled), except where columns or relations were renamed or removed;
obviously new code has to be added to deal with new columns and
relationships. But is there no alternative aside from forcing one to
re-generate the XSD from scratch again, with all the manual steps to define
the relationships and calculated columns (if this can be done)?

-ken

 

[Guest]

Hi Ken

I found your post looking for a solution of almost identical problem. I cannot help you with the main problem you wrote, but maybe I have something that will be usefull for the other questions.

1. Adding a calculated column to data set.
Open the data set in VC schema designer, then add an 'element' (field) to the table where you want to have the calculated field. When you look at this new field's properties window, you find an 'Expression' property - enter formula to calculate value of the field here - 'Quantity * Price' for example. In case of Customer/Orders scenario you must define a Customer2Orders relation - then write something like 'Count(Child.ProductID)' to see number of orders (this explanation comes from 'ADO.NET Profession Programming' by David Sceppa).

2. Filling multiple table with a single call.
I found somewhere an example where 3 table were loaded at once. The query in an adapter was a batch query: "select * from Customers; Select * from Orders; select * from [Order details]". The table mapping property of the adapter was set to map fields from 1st query (Table1) to appropriate table/fields of a data set, fields from 2nd query (Table2) to another data set's table/fields, and so on. MSSQL handles batch queries, but other data servers may not.

3. Lookup relation.
Somebody on the forum put this link - maybe it will also help you a little:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319076

This is, however, an obscure solution for such a kind of a problem. In C++Builder or Delphi making a lookup relationship is trivial.

Have you already got any answers for the core of your question (mean lookup relationship)?
And, you wrote about creating a sub-class DataSet - do you know how to create such data set, that the VC Designer will see it and let change its properties in design-time.

Piotr

Argh!

I can define the Parent/Child relationship the way I want programmatically,
but not in a typed dataset from the IDE!

While I can define the relationship, I cannot get access to the members of
the child! In my case there is a 1:1 relationship between the parent
[Drives] and child [Levels] tables, but the only way to refer to the
contents of the child row(s) directly is to use numeric aggregate functions!
The parent can be referred to directly, however!

So this basically means that I must define my relationship "in reverse" to
what I intend, and then traverse only the children and permit a reference to
the parent...

I can do this when I create the dataset programmtically, but I still cannot
find a way to define a calculated column from the typed dataset designer in
the IDE.

Another point -- if I have a typed dataset with two (2) or more tables, can
I fully populate the dataset with a single Fill() call? Theonly way I have
been able to achieve this is to use a separate SqlDataAdapter.Fill() call
for each specific table.

-ken

I am having some difficulties with determining how to leverage the use of
typed datasets with a specific schema. The problem lies with the definition
of a 'relationship', at least when defining the typed dataset via the IDE.

The IDE forces me to define a relationship from a parent to a child,
requiring the parent to be the one with the Primary Key and the child to
contain a column of the same type (not necessarily part of its primary key).
This works fine in many cases, such as Customer --> Orders, where the
Customer record is the parent and the Orders record contains a (possibly
non-primary key) column that contains the CustomerID.

In other cases this is not the actual case: consider the somewhat artificial
situation where we shall classify disk drives into 'levels' based on a
capacity range. So the [Levels] table will contain a primary key of LevelID
and the remainder of the fields will describe each specific level. The
[Drives] table will contain a primary key of DriveID and will also contain a
non-key field named LevelID; in the database there shall be a foreign key
defined between these tables based on the LevelID column. In the typed
dataset, I am forced to define the relationship with [Levels] as the parent,
but in fact the levels are merely a lookup mechanism to permit normalization
of the data. When I display the [Drives] entries, I want to show some
columns from the [Levels] table, either in addition to or in place of the
LevelID column value itself, but I do not wish to treat the [Drives] as
children of the [Levels], since [Levels] are not control objects, merely
lookup information.

I am likely missing something here, but since all of the source material I
have found, in books and online, refers to the Customer/Order paradigm and
not to the Drives/Levels scenario, I am uncertain how I can leverage the use
of typed datasets to avoid the use of a JOIN to retrieve the information
that I want.

Also, I can find no way to define calculated columns as part of a typed
dataset, at least from within the IDE. Is this possible? In the classic
Customer/Orders paadigm, I would want to define a calculated column in the
typed data table [Customers] to contain the number of [Orders] that are
'open' as well as the total value of those orders, but I can find no way to
define such a calculated column unless I place it in the database, which I
do not want. I have come across numerous examples of doing this with
programmatically created untyped datasets, but I can find no reference on
how to do this with a typed dataset, and expecially from within the IDE.

Finally, I am concerned with the amount of work that one must do to update a
typed dataset to reflect changes made to the actual schema. From an earlier
post it was pointed out that business rules should be added to a further
sub-class of the typed dataset, such that most changes to the typed dataset
would not invalidate the derived class (but would require that it be
recompiled), except where columns or relations were renamed or removed;
obviously new code has to be added to deal with new columns and
relationships. But is there no alternative aside from forcing one to
re-generate the XSD from scratch again, with all the manual steps to define
the relationships and calculated columns (if this can be done)?

-ken

 

[Ken Allen]

Comments embedded.

Hi Ken

I found your post looking for a solution of almost identical problem. I

cannot help you with the main problem you wrote, but maybe I have something
that will be usefull for the other questions.

1. Adding a calculated column to data set.
Open the data set in VC schema designer, then add an 'element' (field) to

the table where you want to have the calculated field. When you look at this
new field's properties window, you find an 'Expression' property - enter
formula to calculate value of the field here - 'Quantity * Price' for
example. In case of Customer/Orders scenario you must define a
Customer2Orders relation - then write something like
'Count(Child.ProductID)' to see number of orders (this explanation comes
from 'ADO.NET Profession Programming' by David Sceppa).
OK, so I finally managed to add the new column, and I did enter
"Parent.FieldName" as the expression for this new column, but the column
value is always NULL when I display the typed dataset in a grid.

2. Filling multiple table with a single call.
I found somewhere an example where 3 table were loaded at once. The query

in an adapter was a batch query: "select * from Customers; Select * from
Orders; select * from [Order details]". The table mapping property of the
adapter was set to map fields from 1st query (Table1) to appropriate
table/fields of a data set, fields from 2nd query (Table2) to another data
set's table/fields, and so on. MSSQL handles batch queries, but other data
servers may not.
Yes, I had forgotton about the table mapping information and the 'magic'
names of "Table", "Table1" and so on. I see how this is done now. Thanks.

3. Lookup relation.
Somebody on the forum put this link - maybe it will also help you a little:

http://support.microsoft.com/default.aspx?scid=kb;en-us;Q319076

This is, however, an obscure solution for such a kind of a problem. In

C++Builder or Delphi making a lookup relationship is trivial.
Yes, this is obscure, and not quite what I had in mind. If I do the same as
what I had attempted with the typed dataset above, add a column to the
Drives table that references Levels.Tag, then when the Drives table is
displayed in the grid the new column contains the field from the Levels
table as I wanted, but I still cannot get the typed dataset approach to do
this.

Have you already got any answers for the core of your question (mean lookup relationship)?
And, you wrote about creating a sub-class DataSet - do you know how to

create such data set, that the VC Designer will see it and let change its
properties in design-time.
What I so here is first create the typed dataset, and then define a new
class (in another source file, obviously) that uses the typed dataset as the
base class, and then I add the business rules as methods on this derived
class. The designer knows nothing about this derived class, aside from the
built-in intellisense and compilation. I can then make changes to either and
only require a recompilation, unless I change the columns.

Piotr

Argh!

I can define the Parent/Child relationship the way I want programmatically,
but not in a typed dataset from the IDE!

While I can define the relationship, I cannot get access to the members of
the child! In my case there is a 1:1 relationship between the parent
[Drives] and child [Levels] tables, but the only way to refer to the
contents of the child row(s) directly is to use numeric aggregate functions!
The parent can be referred to directly, however!

So this basically means that I must define my relationship "in reverse" to
what I intend, and then traverse only the children and permit a reference to
the parent...

I can do this when I create the dataset programmtically, but I still cannot
find a way to define a calculated column from the typed dataset designer in
the IDE.

Another point -- if I have a typed dataset with two (2) or more tables, can
I fully populate the dataset with a single Fill() call? Theonly way I have
been able to achieve this is to use a separate SqlDataAdapter.Fill() call
for each specific table.

-ken

I am having some difficulties with determining how to leverage the use of
typed datasets with a specific schema. The problem lies with the definition
of a 'relationship', at least when defining the typed dataset via the IDE.

The IDE forces me to define a relationship from a parent to a child,
requiring the parent to be the one with the Primary Key and the child to
contain a column of the same type (not necessarily part of its primary key).
This works fine in many cases, such as Customer --> Orders, where the
Customer record is the parent and the Orders record contains a (possibly
non-primary key) column that contains the CustomerID.

In other cases this is not the actual case: consider the somewhat artificial
situation where we shall classify disk drives into 'levels' based on a
capacity range. So the [Levels] table will contain a primary key of LevelID
and the remainder of the fields will describe each specific level. The
[Drives] table will contain a primary key of DriveID and will also

contain
a

non-key field named LevelID; in the database there shall be a foreign key
defined between these tables based on the LevelID column. In the typed
dataset, I am forced to define the relationship with [Levels] as the parent,
but in fact the levels are merely a lookup mechanism to permit normalization
of the data. When I display the [Drives] entries, I want to show some
columns from the [Levels] table, either in addition to or in place of the
LevelID column value itself, but I do not wish to treat the [Drives] as
children of the [Levels], since [Levels] are not control objects, merely
lookup information.

I am likely missing something here, but since all of the source material I
have found, in books and online, refers to the Customer/Order paradigm and
not to the Drives/Levels scenario, I am uncertain how I can leverage

the
use

of typed datasets to avoid the use of a JOIN to retrieve the information
that I want.

Also, I can find no way to define calculated columns as part of a typed
dataset, at least from within the IDE. Is this possible? In the classic
Customer/Orders paadigm, I would want to define a calculated column in the
typed data table [Customers] to contain the number of [Orders] that are
'open' as well as the total value of those orders, but I can find no

way
to

define such a calculated column unless I place it in the database, which I
do not want. I have come across numerous examples of doing this with
programmatically created untyped datasets, but I can find no reference on
how to do this with a typed dataset, and expecially from within the IDE.

Finally, I am concerned with the amount of work that one must do to

update
a

typed dataset to reflect changes made to the actual schema. From an earlier
post it was pointed out that business rules should be added to a further
sub-class of the typed dataset, such that most changes to the typed dataset
would not invalidate the derived class (but would require that it be
recompiled), except where columns or relations were renamed or removed;
obviously new code has to be added to deal with new columns and
relationships. But is there no alternative aside from forcing one to
re-generate the XSD from scratch again, with all the manual steps to define
the relationships and calculated columns (if this can be done)?

-ken