Table relations across datasets

[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.

 

[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

 

[amir massourian]

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

 

[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

 

[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.

 

[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

 

[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

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

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.