Dealing with Data

So I have a situation with a set of data that I need some
help with. Not sure if this is the right forum but maybe you
can help.
 
So, I have a table of Recipes. Each recipe has a set of
Nutritional values, this list in maintained in a XREF table.
Each recipe also has a set of "Lookup" values,
this list is also maintained in a XREF table.
 
I want to do only one SQL query when a user hits a page, and
get either 1 or many recipes, and for each these recipes get
a list of the associated nutritional values, and a list of
lookup values.
 
I was thinking, to have a stored procedure that returns  3
datasets: the recipe info as one dataset, the nutritionals
as another dataset, lookups as another dataset, and then
drop the nutritionals and lookups into a hashtable.
 
Is there a better way to do this efficiently?



Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com

Why not return the three sets of data as separate tables in
one dataset? Then
you can establish relations within the tables in the
dataset.

 

-----Original Message-----
From: Deepesh Banerji [mailto:dbanerjiwaterfrontmedia.com] 
Sent: Thursday, August 03, 2006 6:52 PM
To: aspnet-databasesaspadvice.com
Subject: [aspnet-databases] Dealing with Data

So I have a situation with a set of data that I need some
help with. Not sure if
this is the right forum but maybe you can help.
 
So, I have a table of Recipes. Each recipe has a set of
Nutritional values, this
list in maintained in a XREF table. Each recipe also has a
set of "Lookup"
values, this list is also maintained in a XREF table.
 
I want to do only one SQL query when a user hits a page, and
get either 1 or
many recipes, and for each these recipes get a list of the
associated
nutritional values, and a list of lookup values.
 
I was thinking, to have a stored procedure that returns  3
datasets: the recipe
info as one dataset, the nutritionals as another dataset,
lookups as another
dataset, and then drop the nutritionals and lookups into a
hashtable.
 
Is there a better way to do this efficiently?



Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com


Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com

Deepesh, this is a pretty efficient way of getting the data
since the round
trip to the server is probably the slowest part of the
transaction.   So
getting it all at once into multiple data sets or data
readers makes sense.

The only advice I would have would be to stay away from
dataset if you are
concerned about performance on the client.   A datareader is
much faster and
efficient.

Bill


-----Original Message-----
From: bounce-aspnet-databases-58376aspadvice.com
[mailto:bounce-aspnet-databases-58376aspadvice.com] On Behalf Of
Deepesh
Banerji
Sent: Thursday, August 03, 2006 6:52 PM
To: aspnet-databasesaspadvice.com
Subject: [aspnet-databases] Dealing with Data

So I have a situation with a set of data that I need some
help with. Not
sure if this is the right forum but maybe you can help.
 
So, I have a table of Recipes. Each recipe has a set of
Nutritional values,
this list in maintained in a XREF table. Each recipe also
has a set of
"Lookup" values, this list is also maintained in
a XREF table.
 
I want to do only one SQL query when a user hits a page, and
get either 1 or
many recipes, and for each these recipes get a list of the
associated
nutritional values, and a list of lookup values.
 
I was thinking, to have a stored procedure that returns  3
datasets: the
recipe info as one dataset, the nutritionals as another
dataset, lookups as
another dataset, and then drop the nutritionals and lookups
into a
hashtable.
 
Is there a better way to do this efficiently?



Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com


Need SQL Advice? http://sqladvice.com
Need RegEx Advice? http://regexadvice.com
Need XML Advice? http://xmladvice.com