Displaying/Modifying Multiple Tables as One Table

[Stefan Siprell]

Hello everybody,
we are currently working on a Oracle 9i Project with the ADO.NET
Framework version 1.0. We have multiple tables in this database which
are linked together by a shared primary key. The relationship among
these tsbles is always 1:1, so each row in a table has a corresponding
row in all of the other tables.

We would like to display and edit columns from the different tables in
one single datagrid (without any navigation!!!). How can this be
achieved? Here are our experiences from different approaches:

* Virtual Tables or Views in the RDBMS
They are perfect for displaying, but do not allow and updates.

* ADO Dataview
Creating an ADO Dataview does not help any, because it only works for
one single table.

* Mixing Columns in Table (in the ADO Dataset)
This does not work either, as each column may be associated with one
table only.

* Appending Columns from Parent Tables to Master Table
One can add columns from linked tables in ADO using their
relationships. This allows us t display all the required columns but
does not allow us to edit all of them. One can edit the appended
columns, but not the master columns, as ADO claims, that this table
cannot be updated, as its structure has been modified.


Any other ideas?

Regards

Tadeas and Stefan

 

[Jon Skeet [C# MVP]]

Hello everybody,
we are currently working on a Oracle 9i Project with the ADO.NET
Framework version 1.0. We have multiple tables in this database which
are linked together by a shared primary key. The relationship among
these tsbles is always 1:1, so each row in a table has a corresponding
row in all of the other tables.

We would like to display and edit columns from the different tables in
one single datagrid (without any navigation!!!). How can this be
achieved? Here are our experiences from different approaches:

<snip>

What happens if you make a query which fetches columns from multiple
tables, and then carefully write your insert/update/delete behaviour to
make the appropriate changes to all tables? It will certainly require
care, but I don't see why it shouldn't work.

 

[Stefan Siprell]

Hi Jon,
well we now chose following solution:

We create a "virtual" table programtically. Then we created relations
between this virtual table and all of the real tables using the PK's.
After this we added references of the columns from the real tables to
the virtual table.

Now we can use this Datagrid to display all of the data from the
virtual table. To modifiy the data, special components are positioned
over the grid cells, which write the modifications directly to the
real tables columns.

This is quite a workaround, but ADO is still keeping everything in
sync automatically, and this solution is quite stable.

Thanks for your help

Regards

Stefan