Table relations across datasets

1 Jan 2005 8:53 PM

Babu M

Hi all,

May be this is an illogical question. But I will try anyway. Is there
a way to create a relationship between two tables across datasets
without violating the constraints? in other words, the primary table
(with a foreign key) is in dataset1 and it's lookup table is in
dataset2.

I will explain why I need this setup. I have a Web application. I
want to store all the lookup tables at the Application level and the
business logic dataset at the Session level. The Session level dataset
tables have foreign keys that need to map to these lookup tables. I
don't want to populate these lookup tables at session level since it
could choke the system resources if the number of sessions are huge.
Any idea anybody?

Babu.

2 Jan 2005 2:24 AM

WJ

I have never done it, but now a day, almost everything you ask for is
possible ! As I understand, an Ado.Net DataSet can host more than one
tables, in this one DataSet, you can establish relations between tables
within this single DataSet. I do not see how it can be done across DataSets.
However, you may want to cache lookup tables in separate web forms once
created, say for a period of 30 hours or 30 days then refresh them. This
way, you can pass a key from the calling page to the cached page, the cached
page will then uses the input key and retrieve your lookup data
appropriately.

John Webb

Show quoteHide quote

"Babu M" <babupalladi***@yahoo.com> wrote in message
news:1104612801.861929.219500@c13g2000cwb.googlegroups.com...
> Hi all,
>
> May be this is an illogical question. But I will try anyway. Is there
> a way to create a relationship between two tables across datasets
> without violating the constraints? in other words, the primary table
> (with a foreign key) is in dataset1 and it's lookup table is in
> dataset2.
>
> I will explain why I need this setup. I have a Web application. I
> want to store all the lookup tables at the Application level and the
> business logic dataset at the Session level. The Session level dataset
> tables have foreign keys that need to map to these lookup tables. I
> don't want to populate these lookup tables at session level since it
> could choke the system resources if the number of sessions are huge.
> Any idea anybody?
>
> Babu.
>

2 Jan 2005 5:20 AM

amir massourian

No, you can not.
You only can create a DataRelation between two DataTables in the same
DataSet.

--
This posting is provided "AS IS", with no warranties, and confers no rights.
Please do not send email directly to this alias. This alias is for newsgroup
purposes only.

Show quoteHide quote

"Babu M" <babupalladi***@yahoo.com> wrote in message
news:1104612801.861929.219500@c13g2000cwb.googlegroups.com...
> Hi all,
>
> May be this is an illogical question. But I will try anyway. Is there
> a way to create a relationship between two tables across datasets
> without violating the constraints? in other words, the primary table
> (with a foreign key) is in dataset1 and it's lookup table is in
> dataset2.
>
> I will explain why I need this setup. I have a Web application. I
> want to store all the lookup tables at the Application level and the
> business logic dataset at the Session level. The Session level dataset
> tables have foreign keys that need to map to these lookup tables. I
> don't want to populate these lookup tables at session level since it
> could choke the system resources if the number of sessions are huge.
> Any idea anybody?
>
> Babu.
>

2 Jan 2005 10:58 AM

Sahil Malik

Babu,

Yes it is possible to do. Though it is not straightforward. Let me explain.

There are two possible approaches to this problem.

Approach #a) Row Importing ---

Create a new datatable and populate it as per below --
DataTable dt = ds1.Tables[0].Clone();
foreach (DataRow dr in ds1.Tables[0].Rows)
{
dt.ImportRow(dr);
}

Add dt to ds2, and setup a relationship.

Approach #b) Table shuffling --
DataTable dt = ds1.Tables[0] ;
ds1.Tables.Remove(0) ;
ds2.Tables.Add(dt) ;
// setup relation in ds2 now.
... do ur work ..
... when ur done ..
ds2.Tables.Remove(ds2) ; // you get the idea
ds1.Tables.Add(dt)

Pros and Cons of either approach --

Approach #a) ---
#1) Expensive for large tables.
#2) Good for repeated approaches
#3) Works great in multithreaded environments (as yours - well application
level/asp.net .. same concept).

Approach #b) ---
#1) Not expensive at all.
#2) Must use mutexes to prevent data corruption, or even proper logical
access.
#3) If you use mutexes, you'd get blocking situations.

... To tell you the truth, point #2 and #3 might be incorrect - I believe
anything you stick in Application object gets serialized and cloned
everytime you request it back. I am fairly sure the ASP.NET cache does that.
You might have to write up a little test to verify #2 and #3, or you could
instead use ASP.NET cache. But if the object is being serialized and
deserialized to effectively clone it, either in Application object or Cache,
then it isn't much worse than Approach #a, except in approach #a you're
paying the penalty twice.

If I had to implement this, I'd use Approach #b, and instead of mutexes, I'd
create a static class with a private variable that holds the lookup
datatable, and wrap that in a public property and stick that into
Application instead. That way, you don't have to deal with complicated
thread synchronization issues (the framework takes care of it).

HTH,

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

Show quoteHide quote

"Babu M" <babupalladi***@yahoo.com> wrote in message
news:1104612801.861929.219500@c13g2000cwb.googlegroups.com...
> Hi all,
>
> May be this is an illogical question. But I will try anyway. Is there
> a way to create a relationship between two tables across datasets
> without violating the constraints? in other words, the primary table
> (with a foreign key) is in dataset1 and it's lookup table is in
> dataset2.
>
> I will explain why I need this setup. I have a Web application. I
> want to store all the lookup tables at the Application level and the
> business logic dataset at the Session level. The Session level dataset
> tables have foreign keys that need to map to these lookup tables. I
> don't want to populate these lookup tables at session level since it
> could choke the system resources if the number of sessions are huge.
> Any idea anybody?
>
> Babu.
>

2 Jan 2005 4:38 PM

Babu Mannaravalappil

Thanks Sahil,

These are very good suggestions. Either way, I guess, I am going to
loose some ADO.NET builtin functionalities for strongly typed datasets.
Like, accessing a parent row directly from within the child table.
Example:

LookUp table: dataSet1.Orders linked to dataset1.OrderDetails table via
a Foreign key in Order Details. Normally, I could get to the Orders row
with dataset1.OrderDetails.OrdersRow. I guess, this approach is
impossible when Orders is in dataset1 and OrderDetails is in dataset2.
Also, now, that OrderDetails cannot have a relationship with Orders, I
have to manually lookup the parent row in orders, further, I have to
manually watchout for orphan rows getting created in the OrderDetails
table because there are no constraint rules there child foreign key.

Babu.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

2 Jan 2005 5:47 PM

Sahil Malik

Yup !! Agree on the below !!

Try another thing, "Dataset.Merge", that'd give you a merged dataset without
mangling the existing dataset.

- Sahil Malik
http://dotnetjunkies.com/weblog/sahilmalik

Show quoteHide quote

"Babu Mannaravalappil" <babu***@optonline.net> wrote in message
news:ezkj3lO8EHA.1296@TK2MSFTNGP10.phx.gbl...
> Thanks Sahil,
>
> These are very good suggestions. Either way, I guess, I am going to
> loose some ADO.NET builtin functionalities for strongly typed datasets.
> Like, accessing a parent row directly from within the child table.
> Example:
>
> LookUp table: dataSet1.Orders linked to dataset1.OrderDetails table via
> a Foreign key in Order Details. Normally, I could get to the Orders row
> with dataset1.OrderDetails.OrdersRow. I guess, this approach is
> impossible when Orders is in dataset1 and OrderDetails is in dataset2.
> Also, now, that OrderDetails cannot have a relationship with Orders, I
> have to manually lookup the parent row in orders, further, I have to
> manually watchout for orphan rows getting created in the OrderDetails
> table because there are no constraint rules there child foreign key.
>
> Babu.
>
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

3 Jan 2005 10:03 PM

Chris Taylor

Hi,

DataTable dt = ds1.Tables[0].Copy() can be used to make a complete copy of a
DataTable.

In ASP.NET the Application is a NameObjectCollectionBase which aggregates an
instance of Hashtable,
as far as I can see there is no serialization/deserialization of object
stored in Application ro Cache. You might be thinking of out or process
Session state which is serialized.

However the poster would definately require a mutex for option #b to ensure
that another ASP.NET request does not steal the DataTable from his dataset
while it is still being used.

--
Chris Taylor
http://dotnetjunkies.com/weblog/chris.taylor

Show quoteHide quote

"Sahil Malik" <contactmethrumyblog@nospam.com> wrote in message
news:OqxG8nL8EHA.2196@TK2MSFTNGP11.phx.gbl...
> Babu,
>
> Yes it is possible to do. Though it is not straightforward. Let me
explain.
>
> There are two possible approaches to this problem.
>
> Approach #a) Row Importing ---
>
> Create a new datatable and populate it as per below --
> DataTable dt = ds1.Tables[0].Clone();
> foreach (DataRow dr in ds1.Tables[0].Rows)
> {
> dt.ImportRow(dr);
> }
>
> Add dt to ds2, and setup a relationship.
>
> Approach #b) Table shuffling --
> DataTable dt = ds1.Tables[0] ;
> ds1.Tables.Remove(0) ;
> ds2.Tables.Add(dt) ;
> // setup relation in ds2 now.
> .. do ur work ..
> .. when ur done ..
> ds2.Tables.Remove(ds2) ; // you get the idea
> ds1.Tables.Add(dt)
>
>
> Pros and Cons of either approach --
>
> Approach #a) ---
> #1) Expensive for large tables.
> #2) Good for repeated approaches
> #3) Works great in multithreaded environments (as yours - well application
> level/asp.net .. same concept).
>
> Approach #b) ---
> #1) Not expensive at all.
> #2) Must use mutexes to prevent data corruption, or even proper logical
> access.
> #3) If you use mutexes, you'd get blocking situations.
>
> .. To tell you the truth, point #2 and #3 might be incorrect - I believe
> anything you stick in Application object gets serialized and cloned
> everytime you request it back. I am fairly sure the ASP.NET cache does
that.
> You might have to write up a little test to verify #2 and #3, or you could
> instead use ASP.NET cache. But if the object is being serialized and
> deserialized to effectively clone it, either in Application object or
Cache,
> then it isn't much worse than Approach #a, except in approach #a you're
> paying the penalty twice.
>
> If I had to implement this, I'd use Approach #b, and instead of mutexes,
I'd
> create a static class with a private variable that holds the lookup
> datatable, and wrap that in a public property and stick that into
> Application instead. That way, you don't have to deal with complicated
> thread synchronization issues (the framework takes care of it).
>
> HTH,
>
> - Sahil Malik
> http://dotnetjunkies.com/weblog/sahilmalik
>
>
>
>
>
> "Babu M" <babupalladi***@yahoo.com> wrote in message
> news:1104612801.861929.219500@c13g2000cwb.googlegroups.com...
> > Hi all,
> >
> > May be this is an illogical question. But I will try anyway. Is there
> > a way to create a relationship between two tables across datasets
> > without violating the constraints? in other words, the primary table
> > (with a foreign key) is in dataset1 and it's lookup table is in
> > dataset2.
> >
> > I will explain why I need this setup. I have a Web application. I
> > want to store all the lookup tables at the Application level and the
> > business logic dataset at the Session level. The Session level dataset
> > tables have foreign keys that need to map to these lookup tables. I
> > don't want to populate these lookup tables at session level since it
> > could choke the system resources if the number of sessions are huge.
> > Any idea anybody?
> >
> > Babu.
> >
>
>

Thread options