Looking for ideas on how to handle a specific data situation.

[pantichd]

Hello,

I'm looking for options in handling a certain data situation. Data is stored
in Oracle.

There is a database that contains tables with common information. For
example, it contains a table called USER_COMMON. This table has a key field
(USER_KEY) and some other fields containing data (login, e-mail, etc) that
all applications will need about a user.

Then a specific application has its own database and one of the tables there
is called USER. This table also has a USER_KEY column with the same value as
the column in the USER_COMMON table.

I've implemented a layered approach to the data handling. One project has
all the data adapter objects, another project has the typed datasets created
from the data adapter objects and a third project has the business layer
objects which use the data adapter objects and return dataset objects to the
GUI layer.

This approach has worked great for me when I"m just going against one
database. However, now I want to have one object to represent the
combination of the USER_COMMON and USER tables.

My first thought was "I'll just create a view on Oracle". Then I could
create a data adapter from the view and so on. That fell apart because
Oracle doesn't allow you to update data thru a view.

Next I thought I could put both tables in one typed dataset and do a
DataRelation to join them. Again, I can't figure out how to do updates using
this approach.

Then I saw an article
(http://support.microsoft.com/default.aspx?scid=kb;en-us;325688) on creating
a dataset JOIN helper class but that didn't seem to have a way to update the
backend data either. I noticed this article is quite old so there may be
something better out now.

I may have missed something but my eyes are glazed over now and I'm looking
for some fresh ideas.

Any help/tips would be appreciated.

Thanks!

David.

 

[Mike Ryan]

David,

I recommend this article:

"Entity Aggregation"
http://msdn.microsoft.com/library/d...ry/en-us/dnpag/html/archentityaggregation.asp

- Mike

 

[pantichd]

Mike,

Thanks for your reply. I read the article but I hate to admit I'm just not
smart enough to figure out how to IMPLEMENT this myself. I was hoping for
some examples of someone who had actually implemented something like this.
Do know of any?

Thanks again.

David.

 

[almscl2]

Hello,

- As far as I remember, you are able to update a view in Oracle as long
as you update not more than one table participating in the view. I
think this functionality was added in after Oracle 8i.

- An elegant solution is to break up the dataset which is the result of
a join query back in your data acces layer to its primary tables and
then update the dataset.

Thanks,
-Al
WireFactor Software Ltd.
http://www.wirefactor.com