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

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
Thu Jan 27 16:04:30 CST 2005

David,

I recommend this article:

"Entity Aggregation"
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/archentityaggregation.asp

- Mike

pantichd wrote:
> 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.
>
>

pantichd
Fri Jan 28 10:19:12 CST 2005

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.


"Mike Ryan" <CANSPAMmryan5@gmail.com> wrote in message
news:r9-dnVwrheTz-GTcRVn-rA@comcast.com...
> David,
>
> I recommend this article:
>
> "Entity Aggregation"
>
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnpag/html/archentityaggregation.asp
>
> - Mike
>
> pantichd wrote:
> > 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.
> >
> >

almscl2
Mon Jan 31 00:03:26 CST 2005

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

pantichd wrote:
> 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.