Is there any way to select from database by relations?
Hi all , i'm new here, and relatively new to asp.net..
My question is, how can i make relations based queries from database.
I posted this on few other forums, so far no reply.
My main problem is that i need to select data from few tables using their relationships as defined in database.
Let's presume i've got 2 tables, tblUsers, and tblAddress
tblUsers fields: userId, userName
tblAddress: adrrId, userId, addressStr
it has 1:N relationship - (which fields it's obvious)
basicly, i want to select few rows from tblUsers :
"SELECT * FROM tblUsers WHERE userName='pirate'"
then i fill tblUsers in the dataset with the results.
I've got more then 1 user from tblUsers, and i allready have a pre-defined dataset with the proper relations defined.
the thing is, I want to pull the records from tblAddress in database only for the records i've got in dataSet.tblUsers by using the relations in the dataset because selecting all records from tblAddress seems like a waste of memory
Is there any reasonable, non complex way to do so?
Thanks in advance :)
[1204 byte] By [
yushi] at [2007-11-11 8:51:19]
# 3 Re: Is there any way to select from database by relations?
How did those values get into the dataTable in the first place?
And how many id values are there? There is nothing stopping you from building your SQL string at runtime, something like this:
"SELECT * FROM tblAddress WHERE tblAddress.UserID = " & valueToCheck1 & " or tblAddress.UserID = " & valueToCheck2 & " AND tblAddress.UserID = tblUsers.UserID"
You can use a loop through every item in the dataTable to build up a string containing every item, with " or tblAddress.UserID = " in between each. Then you combine that with the rest of the SQL string and run it once against the db.
Ugly, but it works. However, once you have it working, you REALLY should use a StringBuilder object instead of a string; string concatenation is orders of magnitude slower than using StringBuilder.Append.
A better approach might be to use a Parameter in the query which your code fills in at runtime... in general using Parameters is much less error-prone than building SQL strings at runtime; and it avoids having to make sure you got all the quotes in the right places and have the correct number of &'s!
As long as you have the values for the query SOMEWHERE, you can build the Query at runtime or use parameters.
There may be better ways to do this; anyone? Paul Clement? (Paul is my personal SQL Hero this month, for a SQL command he helped me with that inserts info from a .CSV file into a db at warp-speed. The full thread is here: http://forums.dev-archive.com/showthread.php?p=456356#post456356 )
-Andrew
